Проверка, запрос и изменение данных JSON со встроенными функциями (SQL Server)

  • Статья

Область применения: SQL Server 2016 (13.x) и более поздних версий База данных SQL Azure Управляемый экземпляр SQL Azure

Встроенная поддержка JSON включает следующие встроенные функции, описанные в этой статье.

  • ISJSON проверяет наличие допустимых данных JSON в строке.
  • JSON_VALUE извлекает скалярное значение из строки JSON.
  • JSON_QUERY извлекает объект или массив из строки JSON.
  • JSON_MODIFY обновляет значение свойства в строке JSON и возвращает обновленную строку JSON.

В этой статье требуется AdventureWorks2022 пример базы данных, которую можно скачать на домашней странице примеров и проектов сообщества Microsoft SQL Server.

Текст JSON для примеров на этой странице

В примерах на этой странице используется текст JSON, аналогичный содержимому в следующем примере:

{
    "id": "DesaiFamily",
    "parents": [
        { "familyName": "Desai", "givenName": "Prashanth" },
        { "familyName": "Miller", "givenName": "Helen" }
    ],
    "children": [
        {
            "familyName": "Desai",
            "givenName": "Jesse",
            "gender": "female",
            "grade": 1,
            "pets": [
                { "givenName": "Goofy" },
                { "givenName": "Shadow" }
            ]
        },
        {
            "familyName": "Desai",
            "givenName": "Lisa",
            "gender": "female",
            "grade": 8
        }
    ],
    "address": {
        "state": "NY",
        "county": "Manhattan",
        "city": "NY"
    },
    "creationDate": 1431620462,
    "isRegistered": false
}

Этот документ JSON, содержащий вложенные сложные элементы, хранится в следующем образце таблицы:

CREATE TABLE Families (
    id INT identity CONSTRAINT PK_JSON_ID PRIMARY KEY,
    doc NVARCHAR(MAX)
);

Проверка строки JSON с помощью функции ISJSON

Функция ISJSON проверяет, содержит ли строка допустимый JSON.

В приведенном ниже примере возвращаются строки, в которых столбец JSON содержит допустимый текст JSON. Без явного ограничения JSON можно ввести любой текст в столбце NVARCHAR:

SELECT *
FROM Families
WHERE ISJSON(doc) > 0;

Дополнительные сведения см. в разделе ISJSON (Transact-SQL).

Для извлечения значения из строки JSON используется функция JSON_VALUE

Функция JSON_VALUE извлекает скалярное значение из строки JSON. Следующий запрос возвращает документы, в которых id поле JSON соответствует значениюDesaiFamily, упорядоченным по полям city JSON:state

SELECT JSON_VALUE(f.doc, '$.id') AS Name,
    JSON_VALUE(f.doc, '$.address.city') AS City,
    JSON_VALUE(f.doc, '$.address.county') AS County
FROM Families f
WHERE JSON_VALUE(f.doc, '$.id') = N'DesaiFamily'
ORDER BY JSON_VALUE(f.doc, '$.address.city') DESC,
    JSON_VALUE(f.doc, '$.address.state') ASC

Результаты этого запроса показаны в приведенной ниже таблице.

Имя. Город Округ
DesaiFamily NY Manhattan

Дополнительные сведения см. в JSON_VALUE (Transact-SQL).

Для извлечения объекта или массива из строки JSON используется функция JSON_QUERY

Функция JSON_QUERY извлекает объект или массив из строки JSON. В следующем примере показано, как можно вернуть фрагмент JSON в результатах запроса.

SELECT JSON_QUERY(f.doc, '$.address') AS Address,
    JSON_QUERY(f.doc, '$.parents') AS Parents,
    JSON_QUERY(f.doc, '$.parents[0]') AS Parent0
FROM Families f
WHERE JSON_VALUE(f.doc, '$.id') = N'DesaiFamily';

Результаты этого запроса показаны в приведенной ниже таблице.

Адрес Родители Parent0
{ "state": "NY", "county": "Manhattan", "city": "NY" } [ { "familyName": "Desai", "givenName": "Prashanth" }, { "familyName": "Miller", "givenName": "Helen" } ] { "familyName": "Desai", "givenName": "Prashanth" }

Дополнительные сведения см. в разделе JSON_QUERY (Transact-SQL).

Анализ вложенных коллекций JSON

OPENJSON функция позволяет преобразовать вложенный объект JSON в набор строк, а затем присоединить его к родительскому элементу. Например, можно вернуть все документы о семьях и присоединить их к объектам children, которые хранятся в виде внутреннего массива JSON:

SELECT JSON_VALUE(f.doc, '$.id') AS Name,
    JSON_VALUE(f.doc, '$.address.city') AS City,
    c.givenName,
    c.grade
FROM Families f
CROSS APPLY OPENJSON(f.doc, '$.children') WITH (
    grade INT,
    givenName NVARCHAR(100)
) c

Результаты этого запроса показаны в приведенной ниже таблице.

Имя. Город givenName Оценка
DesaiFamily NY Jesse 1
DesaiFamily NY Lisa 8

Возвращаются две строки, так как одна родительская строка объединяется с двумя дочерними строками, созданными путем синтаксического анализа двух элементов дочерних дочерних элементов. Функция OPENJSON анализирует фрагмент children из столбца doc и возвращает grade и givenName из каждого элемента в виде набора строк. Этот набор строк можно объединить с родительским документом.

Запрос вложенных вложенных вложенных вложенных фрагментов JSON

Вы можете использовать несколько вызовов CROSS APPLY OPENJSON для запроса вложенных структур JSON. В документе JSON, используемом в этом примере, есть вложенный массив с именем children, каждый дочерний элемент которого имеет вложенный массив pets. Следующий запрос анализирует дочерние элементы из каждого документа, возвращает каждый объект массива в виде строки, а затем анализирует pets массив:

SELECT c.familyName,
    c.givenName AS childGivenName,
    p.givenName AS petName
FROM Families f
CROSS APPLY OPENJSON(f.doc) WITH (
    familyName NVARCHAR(100),
    children NVARCHAR(MAX) AS JSON
) AS a
CROSS APPLY OPENJSON(children) WITH (
    familyName NVARCHAR(100),
    givenName NVARCHAR(100),
    pets NVARCHAR(max) AS JSON
) AS c
OUTER APPLY OPENJSON(pets) WITH (givenName NVARCHAR(100)) AS p;

Первый OPENJSON вызов возвращает фрагмент массива children с помощью предложения AS JSON. Этот фрагмент массива предоставляется второй OPENJSON функции, возвращающей givenNameкаждый firstName дочерний объект, а также массив pets. Массив pets предоставляется OPENJSON третьей функции, возвращающей givenName животного.

Результаты этого запроса показаны в приведенной ниже таблице.

familyName childGivenName petName
Desai Jesse Goofy
Desai Jesse Shadow
Desai Lisa NULL

Корневой документ объединяется с двумя строками children, возвращаемыми при первом вызове OPENJSON(children), в результате чего получаются две строки (или два кортежа). Затем каждая строка объединяется с новыми строками, созданными функцией OPENJSON(pets), с помощью оператора OUTER APPLY. Джесси имеет двух домашних животных, поэтому (Desai, Jesse) присоединены к двум строкам, созданным для Goofy и Shadow. Лиза не имеет домашних животных, поэтому нет строк, возвращаемых OPENJSON(pets) для этого кортежа. Тем не менее, так как мы используем OUTER APPLY, мы получаем NULL в столбце. Если мы ставим CROSS APPLY вместо OUTER APPLYэтого, Лиза не будет возвращена в результате, потому что нет строк домашних животных, которые могут быть присоединены с этим кортежем.

Сравнение JSON_VALUE и JSON_QUERY

Ключевое различие между и JSON_QUERY заключается в JSON_VALUE том, что JSON_VALUE возвращает скалярное значение, а JSON_QUERY возвращает объект или массив.

Рассмотрим следующий пример данных в формате JSON.

{
    "a": "[1,2]",
    "b": [1, 2],
    "c": "hi"
}

В приведенном примере элементы "а" и "c" являются строковыми значениями, а элемент "b" — массивом. JSON_VALUE и JSON_QUERY возвращает следующие результаты:

Путь Возвращаемые значения JSON_VALUE Возвращаемые значения JSON_QUERY
$ NULL или ошибка { "a": "[1,2]", "b": [1, 2], "c": "hi" }
$.a [1,2] NULL или ошибка
$.b NULL или ошибка [1,2]
$.b[0] 1 NULL или ошибка
$.c hi NULL или ошибка

Тестирование JSON_VALUE и JSON_QUERY на образце базы данных AdventureWorks

Проверьте встроенные функции, описанные в этой статье, выполнив следующие примеры с примерами AdventureWorks2022 базы данных. Дополнительные сведения о добавлении данных JSON для тестирования с помощью скрипта см. в статье "Поддержка тестового диска JSON".

В следующих примерах столбец Info таблицы SalesOrder_json содержит текст в формате JSON.

Пример 1. Возвращение стандартных столбцов и данных JSON

Следующий запрос возвращает значения и из стандартных реляционных столбцов, и из столбца JSON.

SELECT SalesOrderNumber,
    OrderDate,
    Status,
    ShipDate,
    AccountNumber,
    TotalDue,
    JSON_QUERY(Info, '$.ShippingInfo') ShippingInfo,
    JSON_QUERY(Info, '$.BillingInfo') BillingInfo,
    JSON_VALUE(Info, '$.SalesPerson.Name') SalesPerson,
    JSON_VALUE(Info, '$.ShippingInfo.City') City,
    JSON_VALUE(Info, '$.Customer.Name') Customer,
    JSON_QUERY(OrderItems, '$') OrderItems
FROM Sales.SalesOrder_json
WHERE ISJSON(Info) > 0;

Пример 2. Агрегирование и фильтрация значений JSON

Следующий запрос рассчитывает промежуточные итоги по имени клиента (хранятся в формате JSON) и состояние (хранится в обычном столбце). Затем результаты фильтруются по городу (в формате JSON) и по дате заказа OrderDate (в обычном столбце).

DECLARE @territoryid INT;
DECLARE @city NVARCHAR(32);

SET @territoryid = 3;
SET @city = N'Seattle';

SELECT JSON_VALUE(Info, '$.Customer.Name') AS Customer,
    Status,
    SUM(SubTotal) AS Total
FROM Sales.SalesOrder_json
WHERE TerritoryID = @territoryid
    AND JSON_VALUE(Info, '$.ShippingInfo.City') = @city
    AND OrderDate > '1/1/2015'
GROUP BY JSON_VALUE(Info, '$.Customer.Name'),
    Status
HAVING SUM(SubTotal) > 1000;

Обновление значений свойств в тексте JSON с помощью функции JSON_MODIFY

Функция JSON_MODIFY обновляет значение свойства в строке JSON и возвращает обновленную строку JSON.

В следующем примере показано изменение значения свойства JSON в переменной, содержащей JSON-данные.

SET @info = JSON_MODIFY(@jsonInfo, "$.info.address[0].town", 'London');

Дополнительные сведения см. в разделе JSON_MODIFY (Transact-SQL).

Связанный контент