Microsoft SQL Server
JSON i Sql Server

Syntax

• JSON_VALUE (expression, path) - extrahera ett skalärt värde från en JSON-sträng.
• JSON_QUERY (uttryck [, sökväg]) - Extraherar ett objekt eller en matris från en JSON-sträng.
• OPENJSON (jsonExpression [, path]) - tabellvärde-funktion som analyserar JSON-text och returnerar objekt och egenskaper i JSON som rader och kolumner.
• ISJSON (uttryck) - Testar om en sträng innehåller giltig JSON.
• JSON_MODIFY (expression, path, newValue) - Uppdaterar värdet på en egenskap i en JSON-sträng och returnerar den uppdaterade JSON-strängen.

parametrar

Anmärkningar

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

Formatera frågeställningar som JSON med FOR JSON

Inmatningstabeldata (Persontabell)

Fråga

SELECT Id, Name, Age
FROM People
FOR JSON PATH

Resultat

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

Analysera JSON-text

JSON_VALUE och JSON_QUERY- funktioner analyserar JSON-text och returnerar skalvärden eller objekt / arrayer på sökvägen i JSON-text.

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

Resultat

Gå med i JSON-föräldrar och barn med CROSS APPLY OPENJSON

Gå med föräldraobjekt med sina barnenheter, till exempel vill vi ha en relationstabell för varje person och deras hobbyer

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

Fråga

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

Alternativt kan denna fråga skrivas med MED-klausulen.

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

Resultat

Indexera över JSON-egenskaper med hjälp av beräknade kolumner

När vi lagrar JSON-dokument i SQL Server måste vi kunna filtrera och sortera frågresultat på JSON-dokumentens egenskaper.

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

Givet tabellen ovan Om vi vill hitta raden med namnet = 'Adam', skulle vi utföra följande fråga.

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

Detta kommer emellertid att kräva att SQL-servern utför en fullständig tabell som på ett stort bord inte är effektiv.

För att påskynda detta vill vi lägga till ett index, men vi kan inte direkt hänvisa till egenskaper i JSON-dokumentet. Lösningen är att lägga till en beräknad kolumn på JSON-sökvägen $.Name och sedan lägga till ett index i den beräknade kolumnen.

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

CREATE INDEX idx_name
ON JsonTable(vName)

När vi kör samma fråga använder SQL-server istället för en fullständig tabellscanning ett index för att söka efter det icke-klusterade indexet och hitta raderna som uppfyller de angivna villkoren.

Obs: För att SQL-server ska använda indexet måste du skapa den beräknade kolumnen med samma uttryck som du planerar att använda i dina frågor - i det här exemplet JSON_VALUE(jsonInfo, '$.Name') , men du kan också använda namnet av beräknad kolumn vName

Formatera en tabellrad som ett enda JSON-objekt med hjälp av FOR JSON

WITHOUT_ARRAY_WRAPPER- alternativet i FOR JSON- avsnitt tar bort matriser från JSON-utgången. Detta är användbart om du returnerar en rad i frågan.

Obs! Det här alternativet kommer att producera ogiltig JSON-utgång om mer än en rad returneras.

Inmatningstabeldata (Persontabell)

Fråga

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

Resultat

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

Analysera JSON-text med OPENJSON-funktionen

OPENJSON- funktionen analyserar JSON-text och returnerar flera utgångar. Värden som ska returneras specificeras med hjälp av de sökvägar som definieras i MED-klausulen. Om en sökväg inte anges för någon kolumn används kolumnnamnet som en sökväg. Denna funktion avger returnerade värden till de SQL-typer som definieras i MED-klausulen. AS JSON-alternativet måste anges i kolumndefinitionen om något objekt / array ska returneras.

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

Resultat