Apprendre à retourner du JSON avec SQL Server

Depuis SQL Server 2016, Microsoft a introduit le support du format JSON pour répondre aux besoins de certaines applications spécifiques, pour lesquelles le format ligne/colonne/valeur est trop limitant.

SQL Server propose donc des fonctions intégrées pour manipuler et générer du JSON.

Voici comment retourner un objet (ou un tableau) JSON, directement depuis une requête SQL :

Utilisation de FOR JSON

La clause FOR JSON permet de formater les résultats d'une requête SQL en JSON.

Elle dispose de deux modes : AUTO et PATH.

FOR JSON AUTO

Ce mode génère un JSON automatiquement basé sur la structure de la requête SQL, en utilisant les relations implicites entre les tables.

Exemple pour une requête comme celle-ci :

SELECT id, nom, age
FROM Employes
FOR JSON AUTO;

La sortie sera :

[
  { "id": 1, "nom": "Dupont", "age": 30 },
  { "id": 2, "nom": "Martin", "age": 25 }
]

FOR JSON PATH

Ce mode donne plus de flexibilité en permettant de définir des chemins personnalisés et de structurer manuellement la hiérarchie du JSON.

SELECT 
	id AS "employe.id", 
	nom AS "employe.nom", 
	age AS "employe.age"
FROM Employes
FOR JSON PATH, ROOT('employes');

Ce qui produira une structure JSON imbriquée :

{
  "employes": [
    { "employe": { "id": 1, "nom": "Dupont", "age": 30 }},
    { "employe": { "id": 2, "nom": "Martin", "age": 25 }}
  ]
}

Conclusion

Comme avec la plupart des SGBD SQL, il est possible de retourner des objets et tableaux JSON directement depuis la requête, et le tout très simplement grâce à des commandes natives !

En l’occurance, FOR JSON pour SQL Server !