Microsoft SQL Server
JSON in SQL Server

Syntaxis

• JSON_VALUE (expressie, pad) - extraheer een scalaire waarde uit een JSON-tekenreeks.
• JSON_QUERY (expressie [, pad]) - Extraheert een object of een array uit een JSON-string.
• OPENJSON (jsonExpression [, pad]) - tabelwaardefunctie die JSON-tekst parseert en objecten en eigenschappen in JSON retourneert als rijen en kolommen.
• ISJSON (expressie) - Test of een string geldige JSON bevat.
• JSON_MODIFY (expressie, pad, newValue) - Werkt de waarde van een eigenschap in een JSON-string bij en retourneert de bijgewerkte JSON-string.

parameters

Opmerkingen

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

Queryresultaten opmaken als JSON met FOR JSON

Invoertabelgegevens (tabel Personen)

vraag

SELECT Id, Name, Age
FROM People
FOR JSON PATH

Resultaat

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

JSON-tekst ontleden

JSON_VALUE en JSON_QUERY functies parseren JSON-tekst en retourneren scalaire waarden of objecten / arrays op het pad in JSON-tekst.

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

Resultaat

Word lid van bovenliggende en onderliggende JSON-entiteiten met CROSS APPLY OPENJSON

Voeg ouderobjecten samen met hun onderliggende entiteiten, we willen bijvoorbeeld een relationele tabel van elke persoon en zijn hobby's

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"}
        ]
    }
 ]'

vraag

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

Als alternatief kan deze query worden geschreven met behulp van de clausule 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'
)

Resultaat

Indexeer op JSON-eigenschappen met behulp van berekende kolommen

Bij het opslaan van JSON-documenten in SQL Server moeten we queryresultaten op eigenschappen van de JSON-documenten efficiënt kunnen filteren en sorteren.

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

Gegeven de bovenstaande tabel Als we de rij met de naam = 'Adam' willen vinden, zouden we de volgende zoekopdracht uitvoeren.

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

Dit vereist echter dat de SQL-server een volledige tabel uitvoert die op een grote tafel niet efficiënt is.

Om dit te versnellen, willen we een index toevoegen, maar we kunnen niet rechtstreeks naar eigenschappen in het JSON-document verwijzen. De oplossing is om een berekende kolom toe te voegen aan het JSON-pad $.Name en vervolgens een index toe te voegen aan de berekende kolom.

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

CREATE INDEX idx_name
ON JsonTable(vName)

Wanneer we nu dezelfde query uitvoeren, gebruikt SQL Server in plaats van een volledige tabelscan een index om in de niet-geclusterde index te zoeken en de rijen te vinden die aan de opgegeven voorwaarden voldoen.

Opmerking: Om de SQL-server de index te laten gebruiken, moet u de berekende kolom maken met dezelfde expressie die u van plan bent te gebruiken in uw query's - in dit voorbeeld JSON_VALUE(jsonInfo, '$.Name') , maar u kunt ook de naam gebruiken van berekende kolom vName

Maak een tabelrij op als een enkel JSON-object met FOR JSON

De optie WITHOUT_ARRAY_WRAPPER in de clausule FOR JSON verwijdert arrayhaken uit de JSON-uitvoer. Dit is handig als u een enkele rij in de query retourneert.

Opmerking: deze optie levert ongeldige JSON-uitvoer op als er meer dan één rij wordt geretourneerd.

Invoertabelgegevens (tabel Personen)

vraag

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

Resultaat

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

JSON-tekst ontleden met de OPENJSON-functie

De functie OPENJSON parseert JSON-tekst en retourneert meerdere uitgangen. Waarden die moeten worden geretourneerd, worden opgegeven met behulp van de paden die zijn gedefinieerd in de clausule WITH. Als voor sommige kolommen geen pad is opgegeven, wordt de kolomnaam als pad gebruikt. Deze functie werpt geretourneerde waarden naar de SQL-typen die zijn gedefinieerd in de clausule WITH. AS JSON-optie moet worden opgegeven in de kolomdefinitie als een object / array moet worden geretourneerd.

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]')

Resultaat