Microsoft SQL Server
JSON w serwerze Sql

Składnia

• JSON_VALUE (wyrażenie, ścieżka) - wyodrębnij wartość skalarną z łańcucha JSON.
• JSON_QUERY (wyrażenie [, ścieżka]) - Wyodrębnia obiekt lub tablicę z ciągu JSON.
• OPENJSON (jsonExpression [, ścieżka]) - funkcja tabeli-wartości, która analizuje tekst JSON i zwraca obiekty i właściwości w JSON jako wiersze i kolumny.
• ISJSON (wyrażenie) - Sprawdza, czy łańcuch zawiera prawidłowy JSON.
• JSON_MODIFY (wyrażenie, ścieżka, nowaWartość) - aktualizuje wartość właściwości w ciągu JSON i zwraca zaktualizowany ciąg JSON.

Parametry

Uwagi

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

Sformatuj wyniki zapytania jako JSON za pomocą FOR JSON

Dane tabeli wejściowej (tabela osób)

Pytanie

SELECT Id, Name, Age
FROM People
FOR JSON PATH

Wynik

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

Analizuj tekst JSON

Funkcje JSON_VALUE i JSON_QUERY analizują tekst JSON i zwracają wartości skalarne lub obiekty / tablice na ścieżce w tekście 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

Wynik

Dołącz do nadrzędnych i podrzędnych jednostek JSON za pomocą aplikacji CROSS APPLY OPENJSON

Łącz obiekty nadrzędne z ich bytami potomnymi, na przykład chcemy relacyjnej tabeli każdej osoby i jej hobby

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

Pytanie

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

Alternatywnie to zapytanie można napisać przy użyciu klauzuli 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'
)

Wynik

Indeksuj właściwości JSON za pomocą kolumn obliczeniowych

Podczas przechowywania dokumentów JSON w SQL Server, musimy mieć możliwość skutecznego filtrowania i sortowania wyników zapytań dotyczących właściwości dokumentów 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

Biorąc pod uwagę powyższą tabelę Jeśli chcemy znaleźć wiersz o nazwie = „Adam”, wykonalibyśmy następujące zapytanie.

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

Wymaga to jednak od serwera SQL wykonania pełnej tabeli, co w przypadku dużej tabeli nie jest skuteczne.

Aby to przyspieszyć, chcielibyśmy dodać indeks, jednak nie możemy bezpośrednio odwoływać się do właściwości w dokumencie JSON. Rozwiązaniem jest dodanie kolumny obliczanej do ścieżki JSON $.Name , a następnie dodanie indeksu do kolumny obliczanej.

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

CREATE INDEX idx_name
ON JsonTable(vName)

Teraz, gdy wykonujemy to samo zapytanie, zamiast pełnego skanowania tabeli, SQL Server używa indeksu do wyszukiwania w indeksie nieklastrowanym i znajdowania wierszy spełniających określone warunki.

Uwaga: Aby serwer SQL mógł korzystać z indeksu, musisz utworzyć kolumnę obliczeniową z tym samym wyrażeniem, którego planujesz użyć w zapytaniach - w tym przykładzie JSON_VALUE(jsonInfo, '$.Name') , jednak możesz także użyć nazwy obliczonej kolumny vName

Sformatuj jeden wiersz tabeli jako pojedynczy obiekt JSON przy użyciu FOR JSON

Opcja WITHOUT_ARRAY_WRAPPER w klauzuli FOR JSON usunie nawiasy tablicowe z danych wyjściowych JSON. Jest to przydatne, jeśli zwracasz pojedynczy wiersz w zapytaniu.

Uwaga: ta opcja spowoduje wygenerowanie niepoprawnego wyjścia JSON, jeśli zostanie zwrócony więcej niż jeden wiersz.

Dane tabeli wejściowej (tabela osób)

Pytanie

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

Wynik

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

Analizuj tekst JSON za pomocą funkcji OPENJSON

Funkcja OPENJSON analizuje tekst JSON i zwraca wiele wyników. Wartości, które powinny zostać zwrócone, są określone przy użyciu ścieżek zdefiniowanych w klauzuli WITH. Jeśli dla niektórych kolumn nie podano ścieżki, nazwa kolumny jest używana jako ścieżka. Ta funkcja rzutuje zwrócone wartości na typy SQL zdefiniowane w klauzuli WITH. Opcja AS JSON musi być określona w definicji kolumny, jeśli należy zwrócić jakiś obiekt / tablicę.

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

Wynik