Mettre les résultats de requête au format JSON avec FOR JSON

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (pool SQL serverless uniquement)Point de terminaison analytique SQL dans Microsoft FabricWarehouse dans Microsoft FabricSQL Database dans Microsoft Fabric

Mettez les résultats de requête au format JSON ou exportez des données depuis SQL Server au format JSON en ajoutant la clause FOR JSON à une instruction SELECT. Utilisez la clause FOR JSON pour simplifier les applications clientes en déléguant la mise en forme de la sortie JSON produite par l’application à SQL Server.

Note

Azure Data Studio est l’éditeur de requête recommandé pour les requêtes JSON, car il met en forme automatiquement les résultats JSON, comme illustré dans cet article. SQL Server Management Studio affiche une chaîne non mise en forme.

Dans Fabric Data Warehouse, FOR JSON doit être le dernier opérateur de la requête, et n’est donc pas autorisé dans les sous-requêtes.

Mettre en forme les résultats des requêtes

Quand vous utilisez la clause FOR JSON, vous pouvez spécifier de manière explicite la structure de la sortie JSON ou laisser la structure de l’instruction SELECT déterminer la sortie.

• Pour maintenir un contrôle total sur le format de la sortie JSON, utilisez FOR JSON PATH. Vous pouvez créer des objets wrapper et imbriquer des propriétés complexes.

• Pour mettre automatiquement en forme la sortie JSON en fonction de la structure de l’instruction SELECT, utilisez FOR JSON AUTO.

Voici un exemple d’instruction SELECT avec la clause FOR JSON et sa sortie.

POUR LE CHEMIN JSON

Contrôler la sortie avec FOR JSON PATH

En mode PATH, vous pouvez utiliser la syntaxe à point (par exemple, Item.Price) pour mettre en forme la sortie imbriquée.

Voici un exemple de requête qui utilise le mode PATH avec la clause FOR JSON. L’exemple suivant utilise également l’option ROOT pour spécifier un élément racine nommé.

Plus d’informations sur FOR JSON PATH

Pour plus d’informations et d’exemples, consultez Mettre en forme la sortie JSON imbriquée avec le mode PATH.

Pour plus d’informations sur la syntaxe et l’utilisation, consultez Clause SELECT - FOR (Transact-SQL).

FOR JSON AUTO

L’instruction SELECT contrôle la sortie avec FOR JSON AUTO

Dans le mode AUTO, la structure de l’instruction SELECT détermine le format de la sortie JSON.

Par défaut, les valeurs NULL ne sont pas incluses dans la sortie. Vous pouvez utiliser INCLUDE_NULL_VALUES pour changer ce comportement.

Voici un exemple de requête qui utilise le mode AUTO avec la clause FOR JSON.

SELECT name, surname
FROM emp
FOR JSON AUTO;

Et voici le code JSON retourné.

[{
    "name": "John"
}, {
    "name": "Jane",
    "surname": "Doe"
}]

Exemple avec JOIN et NULL

L’exemple SELECT...FOR JSON AUTO suivant inclut un affichage de à quoi ressemblent les résultats JSON lorsqu'il y a une relation 1 : plusieurs entre les données des tables jointes.

L’absence de la valeur null dans le JSON retourné est également illustrée. Toutefois, vous pouvez remplacer ce comportement par défaut en utilisant le mot clé INCLUDE_NULL_VALUES sur la clause FOR.

DROP TABLE IF EXISTS #tabStudent;
DROP TABLE IF EXISTS #tabClass;
GO

CREATE TABLE #tabClass (
    ClassGuid UNIQUEIDENTIFIER NOT NULL DEFAULT newid(),
    ClassName NVARCHAR(32) NOT NULL
);

CREATE TABLE #tabStudent (
    StudentGuid UNIQUEIDENTIFIER NOT NULL DEFAULT newid(),
    StudentName NVARCHAR(32) NOT NULL,
    ClassGuid UNIQUEIDENTIFIER NULL -- Foreign key.
);
GO

INSERT INTO #tabClass (ClassGuid, ClassName)
VALUES
    ('DE807673-ECFC-4850-930D-A86F921DE438', 'Algebra Math'),
    ('C55C6819-E744-4797-AC56-FF8A729A7F5C', 'Calculus Math'),
    ('98509D36-A2C8-4A65-A310-E744F5621C83', 'Art Painting');

INSERT INTO #tabStudent (StudentName, ClassGuid)
VALUES
    ('Alice Apple', 'DE807673-ECFC-4850-930D-A86F921DE438'),
    ('Alice Apple', 'C55C6819-E744-4797-AC56-FF8A729A7F5C'),
    ('Betty Boot', 'C55C6819-E744-4797-AC56-FF8A729A7F5C'),
    ('Betty Boot', '98509D36-A2C8-4A65-A310-E744F5621C83'),
    ('Carla Cap', null);
GO

SELECT c.ClassName,
    s.StudentName
FROM #tabClass AS c
RIGHT JOIN #tabStudent AS s ON s.ClassGuid = c.ClassGuid
ORDER BY c.ClassName,
    s.StudentName
FOR JSON AUTO
    -- To include NULL values in the output, uncomment the following line:
    --, INCLUDE_NULL_VALUES
    ;
GO

DROP TABLE IF EXISTS #tabStudent;
DROP TABLE IF EXISTS #tabClass;
GO

Et voici le code JSON généré par l’instruction SELECT précédente.

JSON_F52E2B61-18A1-11d1-B105-00805F49916B

[
   {"s":[{"StudentName":"Carla Cap"}]},
   {"ClassName":"Algebra Math","s":[{"StudentName":"Alice Apple"}]},
   {"ClassName":"Art Painting","s":[{"StudentName":"Betty Boot"}]},
   {"ClassName":"Calculus Math","s":[{"StudentName":"Alice Apple"},{"StudentName":"Betty Boot"}]}
]

Plus d’informations sur FOR JSON AUTO

Pour obtenir des informations et des exemples plus détaillés, consultez Mettre automatiquement en forme la sortie JSON avec le mode AUTO.

Pour plus d’informations sur la syntaxe et l’utilisation, consultez Clause SELECT - FOR (Transact-SQL).

Contrôler d’autres options de sortie JSON

Contrôlez la sortie de la clause FOR JSON en utilisant les options supplémentaires suivantes.

• ROOT

  Pour ajouter un élément de niveau supérieur unique à la sortie JSON, spécifiez l’option ROOT. Si vous ne spécifiez pas cette option, la sortie JSON n’a aucun élément racine. Pour plus d’informations, consultez Ajouter un nœud racine à la sortie JSON avec l’option ROOT.

• INCLUDE_NULL_VALUES

  Pour inclure des valeurs null dans la sortie JSON, spécifiez l’option INCLUDE_NULL_VALUES. Si vous ne spécifiez cette option, la sortie n’inclut pas les propriétés JSON pour les valeurs NULL dans les résultats de la requête. Pour plus d’informations, consultez Inclure des valeurs Null dans l’option JSON - INCLUDE_NULL_VALUES.

• WITHOUT_ARRAY_WRAPPER

  Pour supprimer les crochets qui entourent par défaut la sortie JSON de la clause FOR JSON, spécifiez l’option WITHOUT_ARRAY_WRAPPER. Utilisez cette option pour générer un seul objet JSON en tant que sortie d’un résultat de ligne unique. Si vous ne spécifiez pas cette option, la sortie JSON est mise en forme en tant que tableau, ce qui signifie qu’elle est placée entre crochets. Pour plus d’informations, consultez Supprimer les crochets de la sortie JSON avec l’option WITHOUT_ARRAY_WRAPPER.

Sortie de la clause FOR JSON

La sortie de la clause FOR JSON a les caractéristiques suivantes :

1. Le jeu de résultats contient une seule colonne.

   • Un jeu de résultats de petite taille peut ne contenir qu’une seule ligne.
   • Un grand jeu de résultats fractionne la longue chaîne JSON sur plusieurs lignes.

     • Par défaut, SQL Server Management Studio (SSMS) concatène les résultats en une seule ligne quand la valeur du paramètre de sortie est Résultats dans des grilles. La barre d’état SSMS affiche le nombre de lignes réel.

     • D’autres applications clientes peuvent nécessiter du code qui recombine des résultats longs en une seule chaîne JSON valide en concaténant le contenu de plusieurs lignes. Pour obtenir un exemple de ce code dans une application C#, consultez Utiliser la sortie de FOR JSON dans une application cliente C#.

       

2. Les résultats sont présentés sous la forme de tableau d’objets JSON.

   • Le nombre d’éléments du tableau JSON est égal au nombre de lignes dans les résultats de l’instruction SELECT (avant l’application de la clause FOR JSON).

   • Chaque ligne des résultats de l’instruction SELECT (avant l’application de la clause FOR JSON) devient un objet JSON distinct dans le tableau.

   • Chaque colonne des résultats de l’instruction SELECT (avant l’application de la clause FOR JSON) devient une propriété de l’objet JSON.

3. Le nom et la valeur des colonnes sont échappés selon la syntaxe JSON. Pour plus d’informations, consultez Comment FOR JSON place dans une séquence d’échappement les caractères spéciaux et les caractères de contrôle.

Example

Voici un exemple qui montre comment la clause FOR JSON met en forme la sortie JSON.

Résultats de la requête

A	B	C	D
10	11	12	X
20	21	22	Y
30	31	32	Z

Sortie JSON

[{
    "A": 10,
    "B": 11,
    "C": 12,
    "D": "X"
}, {
    "A": 20,
    "B": 21,
    "C": 22,
    "D": "Y"
}, {
    "A": 30,
    "B": 31,
    "C": 32,
    "D": "Z"
}]

Contenu connexe

• Comment FOR JSON convertit les types de données SQL Server en types de données JSON
• Comment FOR JSON place dans une séquence d’échappement les caractères spéciaux et les caractères de contrôle
• Vidéo : JSON en tant que pont entre les mondes relationnel et NoSQL
• Clause SELECT - FOR (Transact-SQL)
• Utiliser la sortie FOR JSON dans le moteur de base de données SQL et dans les applications clientes