Microsoft SQL Server
JSON dans Sql Server

Syntaxe

• JSON_VALUE (expression, path) - extrait une valeur scalaire d'une chaîne JSON.
• JSON_QUERY (expression [, chemin]) - Extrait un objet ou un tableau d'une chaîne JSON.
• OPENJSON (jsonExpression [, chemin]) - fonction table-valeur qui analyse le texte JSON et renvoie les objets et propriétés dans JSON sous forme de lignes et de colonnes.
• ISJSON (expression) - Vérifie si une chaîne contient un JSON valide.
• JSON_MODIFY (expression, path, newValue) - Met à jour la valeur d'une propriété dans une chaîne JSON et renvoie la chaîne JSON mise à jour.

Paramètres

Remarques

ALTER DATABASE <Database-Name-Here> SET COMPATIBILITY_LEVEL = 130

Formater les résultats de la requête en tant que JSON avec FOR JSON

Données de la table d'entrée (table People)

Question

SELECT Id, Name, Age
FROM People
FOR JSON PATH

Résultat

[
    {"Id":1,"Name":"John","Age":23},
    {"Id":2,"Name":"Jane","Age":31}
]

Parse JSON text

Les fonctions JSON_VALUE et JSON_QUERY analysent le texte JSON et renvoient des valeurs scalaires ou des objets / tableaux sur le chemin du texte JSON.

DECLARE @json NVARCHAR(100) = '{"id": 1, "user":{"name":"John"}, "skills":["C#","SQL"]}'

SELECT
    JSON_VALUE(@json, '$.id') AS Id,
    JSON_VALUE(@json, '$.user.name') AS Name,
    JSON_QUERY(@json, '$.user') AS UserObject,
    JSON_QUERY(@json, '$.skills') AS Skills,
    JSON_VALUE(@json, '$.skills[0]') AS Skill0

Résultat

Joindre des entités JSON parent et enfant à l'aide de CROSS APPLY OPENJSON

Joindre des objets parents avec leurs entités enfants, par exemple, nous voulons une table relationnelle de chaque personne et de ses loisirs

DECLARE @json nvarchar(1000) =
N'[
    {
        "id":1,
        "user":{"name":"John"},
        "hobbies":[
            {"name": "Reading"},
            {"name": "Surfing"}
        ]
    },
    {
        "id":2,
        "user":{"name":"Jane"},
        "hobbies":[
            {"name": "Programming"},
            {"name": "Running"}
        ]
    }
 ]'

Question

SELECT 
    JSON_VALUE(person.value, '$.id') as Id,
    JSON_VALUE(person.value, '$.user.name') as PersonName,
    JSON_VALUE(hobbies.value, '$.name') as Hobby
FROM OPENJSON (@json) as person
    CROSS APPLY OPENJSON(person.value, '$.hobbies') as hobbies

Cette requête peut également être écrite à l'aide de la clause WITH.

SELECT 
    Id, person.PersonName, Hobby
FROM OPENJSON (@json)
WITH(
    Id int '$.id',
    PersonName nvarchar(100) '$.user.name',
    Hobbies nvarchar(max) '$.hobbies' AS JSON
) as person
CROSS APPLY OPENJSON(Hobbies)
WITH(
    Hobby nvarchar(100) '$.name'
)

Résultat

Index sur les propriétés JSON en utilisant des colonnes calculées

Lors du stockage de documents JSON dans SQL Server, nous devons pouvoir filtrer et trier efficacement les résultats des requêtes sur les propriétés des documents JSON.

CREATE TABLE JsonTable
(
    id int identity primary key,
    jsonInfo nvarchar(max),
    CONSTRAINT [Content should be formatted as JSON]
    CHECK (ISJSON(jsonInfo)>0)
)

INSERT INTO JsonTable
VALUES(N'{"Name":"John","Age":23}'),
(N'{"Name":"Jane","Age":31}'),
(N'{"Name":"Bob","Age":37}'),
(N'{"Name":"Adam","Age":65}')
GO

Étant donné le tableau ci-dessus Si nous voulons trouver la ligne avec le nom = 'Adam', nous exécuterons la requête suivante.

SELECT * 
FROM JsonTable Where 
JSON_VALUE(jsonInfo, '$.Name') = 'Adam'

Cependant, cela nécessitera que SQL Server exécute une table complète qui, sur une grande table, n'est pas efficace.

Pour accélérer cela, nous aimerions ajouter un index, mais nous ne pouvons pas référencer directement les propriétés dans le document JSON. La solution consiste à ajouter une colonne calculée sur le chemin JSON $.Name , puis à ajouter un index sur la colonne calculée.

ALTER TABLE JsonTable
ADD vName as JSON_VALUE(jsonInfo, '$.Name')

CREATE INDEX idx_name
ON JsonTable(vName)

Maintenant, lorsque nous exécutons la même requête, au lieu d'une analyse de table complète, SQL Server utilise un index pour rechercher dans l'index non clusterisé et rechercher les lignes qui répondent aux conditions spécifiées.

Remarque: Pour que SQL Server utilise l'index, vous devez créer la colonne calculée avec la même expression que celle que vous prévoyez d'utiliser dans vos requêtes - dans cet exemple, JSON_VALUE(jsonInfo, '$.Name') . Cependant, vous pouvez également utiliser le nom de la colonne calculée vName

Mettre en forme une ligne de tableau en tant qu'objet JSON unique à l'aide de FOR JSON

L' option WITHOUT_ARRAY_WRAPPER de la clause FOR JSON supprime les crochets de tableau de la sortie JSON. Ceci est utile si vous retournez une seule ligne dans la requête.

Remarque: cette option produira une sortie JSON non valide si plusieurs lignes sont renvoyées.

Données de la table d'entrée (table People)

Question

SELECT Id, Name, Age
FROM People
WHERE Id = 1
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Résultat

{"Id":1,"Name":"John","Age":23}

Analyser le texte JSON en utilisant la fonction OPENJSON

La fonction OPENJSON analyse le texte JSON et renvoie plusieurs sorties. Les valeurs à renvoyer sont spécifiées à l'aide des chemins définis dans la clause WITH. Si aucun chemin n'est spécifié pour une colonne, le nom de la colonne est utilisé comme chemin. Cette fonction renvoie les valeurs aux types SQL définis dans la clause WITH. L'option AS JSON doit être spécifiée dans la définition de la colonne si un objet / tableau doit être renvoyé.

DECLARE @json NVARCHAR(100) = '{"id": 1, "user":{"name":"John"}, "skills":["C#","SQL"]}'

SELECT * 
FROM OPENJSON (@json)
    WITH(Id int '$.id',
        Name nvarchar(100) '$.user.name',
        UserObject nvarchar(max) '$.user' AS JSON,
        Skills nvarchar(max) '$.skills' AS JSON,
        Skill0 nvarchar(20) '$.skills[0]')

Résultat