Microsoft SQL Server
JSON на сервере Sql

Синтаксис

• JSON_VALUE (выражение, путь) - извлекает скалярное значение из строки JSON.
• JSON_QUERY (выражение [, путь]) - извлекает объект или массив из строки JSON.
• OPENJSON (jsonExpression [, path]) - функция table-value, которая анализирует текст JSON и возвращает объекты и свойства в JSON в виде строк и столбцов.
• ISJSON (выражение) - проверяет, содержит ли строка действительный JSON.
• JSON_MODIFY (выражение, путь, newValue) - обновляет значение свойства в строке JSON и возвращает обновленную строку JSON.

параметры

замечания

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

Форматировать результаты запроса как JSON с FOR JSON

Данные входных таблиц (таблица «Люди»)

запрос

SELECT Id, Name, Age
FROM People
FOR JSON PATH

Результат

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

Текст анализа JSON

Функции JSON_VALUE и JSON_QUERY анализируют текст JSON и возвращают скалярные значения или объекты / массивы на пути в тексте 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

Результат

Присоединяйте родительские и дочерние объекты JSON, используя CROSS APPLY OPENJSON

Присоединяйте родительские объекты со своими дочерними объектами, например, мы хотим, чтобы реляционная таблица каждого человека и их хобби

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

запрос

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

В качестве альтернативы этот запрос можно записать с помощью предложения 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'
)

Результат

Индекс по свойствам JSON с использованием вычисленных столбцов

При хранении JSON-документов в SQL Server нам необходимо иметь возможность эффективно фильтровать и сортировать результаты запроса по свойствам документов 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

Учитывая приведенную выше таблицу. Если мы хотим найти строку с именем = 'Adam', мы выполним следующий запрос.

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

Однако для этого требуется, чтобы SQL-сервер выполнил полную таблицу, которая на большой таблице неэффективна.

Чтобы ускорить это, мы хотели бы добавить индекс, однако мы не можем напрямую ссылаться на свойства в документе JSON. Решение состоит в том, чтобы добавить вычисленный столбец в пути JSON $.Name , а затем добавить индекс в вычисленный столбец.

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

CREATE INDEX idx_name
ON JsonTable(vName)

Теперь, когда мы выполняем один и тот же запрос, вместо полного сканирования таблицы SQL-сервер использует индекс для поиска в некластеризованном индексе и поиск строк, удовлетворяющих заданным условиям.

Примечание. Чтобы SQL-сервер использовал индекс, вы должны создать вычисленный столбец с тем же выражением, которое вы планируете использовать в своих запросах, - в этом примере JSON_VALUE(jsonInfo, '$.Name') , однако вы также можете использовать имя вычисленного столбца vName

Отформатируйте одну строку таблицы как единый объект JSON, используя FOR JSON

Параметр WITHOUT_ARRAY_WRAPPER в предложении FOR JSON удаляет скобки массива с выхода JSON. Это полезно, если вы возвращаете одну строку в запросе.

Примечание. Эта опция выдаст недопустимый вывод JSON, если возвращается более одной строки.

Данные входных таблиц (таблица «Люди»)

запрос

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

Результат

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

Разбирать текст JSON с использованием функции OPENJSON

Функция OPENJSON анализирует текст JSON и возвращает несколько выходов. Значения, которые должны быть возвращены, указываются с использованием путей, определенных в предложении WITH. Если для некоторого столбца не указан путь, имя столбца используется как путь. Эта функция возвращает возвращаемые значения в типы SQL, определенные в предложении WITH. Опция AS JSON должна указываться в определении столбца, если какой-либо объект / массив должен быть возвращен.

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

Результат