Обработка данных JSON со встроенными функциями

12 мин

Подсказка

Дополнительные сведения см. на вкладке "Текст и изображения ".

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

Базы данных SQL Server, SQL Azure и SQL в Fabric обеспечивают встроенную поддержку JSON, которая позволяет анализировать, запрашивать, создавать и преобразовывать данные JSON непосредственно в T-SQL. В этом уроке вы узнаете, как использовать функции JSON для извлечения значений, создания выходных данных JSON, агрегирования данных в массивы JSON и проверки содержимого JSON.

Извлечение значений с помощью JSON_VALUE и JSON_QUERY

При работе с JSON, хранящимся в базе данных, необходимо извлечь определенные значения для фильтрации, присоединения или отображения. SQL Server предоставляет две функции для этой цели:

JSON_VALUE() извлекает скалярное значение (string, number, boolean) из строки JSON:

DECLARE @json NVARCHAR(MAX) = N'{
    "customer": {
        "id": 12345,
        "name": "Contoso Ltd",
        "active": true
    },
    "orderTotal": 1599.99
}';

SELECT 
    JSON_VALUE(@json, '$.customer.id') AS CustomerID,
    JSON_VALUE(@json, '$.customer.name') AS CustomerName,
    JSON_VALUE(@json, '$.orderTotal') AS OrderTotal;

Результирующий набор будет:

CustomerID   CustomerName   OrderTotal
----------   ------------   ----------
12345        Contoso Ltd    1599.99

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

JSON_QUERY() извлекает объект ИЛИ массив JSON (некаларные значения):

DECLARE @json NVARCHAR(MAX) = N'{
    "customer": {
        "id": 12345,
        "name": "Contoso Ltd"
    },
    "items": [
        {"product": "Widget", "qty": 5},
        {"product": "Gadget", "qty": 3}
    ]
}';

SELECT 
    JSON_QUERY(@json, '$.customer') AS CustomerObject,
    JSON_QUERY(@json, '$.items') AS ItemsArray;

Результирующий набор будет:

CustomerObject                          ItemsArray
--------------------------------------  ------------------------------------------------
{"id": 12345,"name": "Contoso Ltd"}     [{"product": "Widget", "qty": 5},{"product": "Gadget", "qty": 3}]

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

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

-- Access array elements by index (0-based)
SELECT JSON_VALUE(@json, '$.items[0].product') AS FirstProduct;

Результатом будет:

FirstProduct
------------
Widget

Индексы массива начинаются с 0, поэтому $.items[0] относится к первому элементу. Используйте этот синтаксис для извлечения определенных элементов, когда вы знаете их позицию, или комбинируйте с OPENJSON, когда необходимо обработать все элементы массива.

Подсказка

Используйте JSON_VALUE() когда необходимо использовать скалярное значение для сравнения или вычислений. Используйте JSON_QUERY() , когда необходимо сохранить структуру JSON вложенных объектов или массивов.

Анализ массивов JSON с помощью `OPENJSON`

OPENJSON — это табличное значение функция, которая преобразует данные JSON в реляционный набор строк. Используйте эту функцию для объединения данных JSON с реляционными таблицами или элементами массива обработки по отдельности.

Следующий запрос анализирует массив JSON в строки со схемой по умолчанию:

DECLARE @json NVARCHAR(MAX) = N'[
    {"id": 1, "name": "Widget", "price": 29.99},
    {"id": 2, "name": "Gadget", "price": 49.99},
    {"id": 3, "name": "Gizmo", "price": 19.99}
]';

SELECT * FROM OPENJSON(@json);

Результирующий набор будет:

key   value                                          type
---   --------------------------------------------   ----
0     {"id": 1, "name": "Widget", "price": 29.99}   5
1     {"id": 2, "name": "Gadget", "price": 49.99}   5
2     {"id": 3, "name": "Gizmo", "price": 19.99}    5

Без схемы OPENJSON возвращает три столбца: key (индекс массива или имя свойства), value (содержимое JSON) и type (число, указывающее тип данных JSON: 0=null, 1=string, 2=number, 3=boolean, 4=array, 5=object).

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

SELECT 
    ProductID,
    ProductName,
    Price
FROM OPENJSON(@json)
WITH (
    ProductID INT '$.id',
    ProductName NVARCHAR(100) '$.name',
    Price DECIMAL(10,2) '$.price'
);

Результирующий набор будет:

ProductID   ProductName   Price
---------   -----------   ------
1           Widget        29.99
2           Gadget        49.99
3           Gizmo         19.99

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

Совместить OPENJSON с данными таблицы с использованием CROSS APPLY:

-- Assuming Orders table has a JSON column called OrderDetails
SELECT 
    o.OrderID,
    o.CustomerID,
    items.ProductName,
    items.Quantity,
    items.UnitPrice
FROM Orders AS o
CROSS APPLY OPENJSON(o.OrderDetails)
WITH (
    ProductName NVARCHAR(100) '$.product',
    Quantity INT '$.qty',
    UnitPrice DECIMAL(10,2) '$.price'
) AS items;

Замечание

Если код используется OPENJSON с CROSS APPLY, строки из основной таблицы, имеющие NULL или пустые значения JSON, не отображаются в результатах. Используйте, OUTER APPLY если необходимо включить строки без данных JSON.

Создание JSON с помощью `JSON_OBJECT` и `JSON_ARRAY`

В SQL Server 2022 появились JSON_OBJECT и JSON_ARRAY функции для интуитивно понятного построения JSON:

JSON_OBJECT() создает объект JSON из пар "ключ-значение". В следующем примере показано, как создать объект JSON для продукта:

SELECT JSON_OBJECT(
    'id': ProductID,
    'name': Name,
    'price': ListPrice,
    'available': CASE WHEN SellEndDate IS NULL THEN 'true' ELSE 'false' END
) AS ProductJson
FROM SalesLT.Product
WHERE ProductID = 680;

Результатом будет:

ProductJson
---------------------------------------------------------------------------
{"id":680,"name":"HL Road Frame - Black, 58","price":1431.50,"available":"true"}

Функция автоматически обрабатывает преобразование типов данных и корректное экранирование JSON для специальных символов в строковых значениях.

JSON_ARRAY() создает массив JSON из значений. В следующем примере создается массив JSON:

SELECT JSON_ARRAY(
    'SQL Server',
    'Azure SQL Database',
    'SQL Database in Fabric'
) AS Platforms;

Результатом будет:

Platforms
---------------------------------------------------------
["SQL Server","Azure SQL Database","SQL Database in Fabric"]

Значения столбцов, переменные или литеральные значения можно передавать в JSON_ARRAY(). Функция создает правильно отформатированный массив JSON независимо от типов входных данных.

Затем объедините эти функции для создания вложенных структур JSON. В следующем примере создается объект JSON полного заказа с информацией о клиенте и итогах:

SELECT JSON_OBJECT(
    'orderId': soh.SalesOrderID,
    'orderDate': soh.OrderDate,
    'customer': JSON_OBJECT(
        'id': c.CustomerID,
        'name': c.CompanyName
    ),
    'totals': JSON_OBJECT(
        'subtotal': soh.SubTotal,
        'tax': soh.TaxAmt,
        'total': soh.TotalDue
    )
) AS OrderJson
FROM SalesLT.SalesOrderHeader AS soh
INNER JOIN SalesLT.Customer AS c
    ON soh.CustomerID = c.CustomerID
WHERE soh.SalesOrderID = 71774;

Результатом будет:

OrderJson
--------------------------------------------------------------------------------
{"orderId":71774,"orderDate":"2008-06-01","customer":{"id":29825,"name":"Contoso"},"totals":{"subtotal":880.35,"tax":70.43,"total":972.79}}

Вложенные JSON_OBJECT вызовы создают иерархические структуры, соответствующие ожидаемому формату приложения. Этот подход является более чистым, чем объединение строк и гарантирует допустимые выходные данные JSON.

Агрегирование данных с помощью `JSON_ARRAYAGG`

JSON_ARRAYAGG объединяет значения из нескольких строк в один массив JSON. Эта функция полезна для создания денормализованных выходных данных JSON из нормализованных реляционных данных:

SELECT 
    c.CustomerID,
    c.CompanyName,
    JSON_ARRAYAGG(soh.SalesOrderID) AS OrderIds
FROM SalesLT.Customer AS c
INNER JOIN SalesLT.SalesOrderHeader AS soh
    ON c.CustomerID = soh.CustomerID
GROUP BY c.CustomerID, c.CompanyName;

Результатом будет:

CustomerID   CompanyName           OrderIds
----------   -------------------   ------------------
29825        Contoso Retail        [71774,71776,71780]
29847        Adventure Works       [71782,71784]

Функция собирает все соответствующие значения из группированных строк и объединяет их в один массив JSON. Этот метод полезен для создания денормализованных ответов API из нормализованных таблиц базы данных.

Можно объединить JSON_ARRAYAGG с JSON_OBJECT созданием массивов сложных объектов:

SELECT 
    pc.Name AS Category,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'id': p.ProductID,
            'name': p.Name,
            'price': p.ListPrice
        )
    ) AS Products
FROM SalesLT.ProductCategory AS pc
INNER JOIN SalesLT.Product AS p
    ON pc.ProductCategoryID = p.ProductCategoryID
GROUP BY pc.ProductCategoryID, pc.Name;

Следующий результат будет следующим:

Category        Products
--------------  --------------------------------------------------------------------------
Road Bikes      [{"id":749,"name":"Road-150 Red, 62","price":3578.27},{"id":750,"name":"Road-150 Red, 44","price":3578.27}]
Mountain Bikes  [{"id":771,"name":"Mountain-100 Silver, 38","price":3399.99},{"id":772,"name":"Mountain-100 Black, 38","price":3374.99}]

Это важно

JSON_ARRAYAGG и JSON_OBJECT/JSON_ARRAY функции доступны в SQL Server 2022 и более поздних версиях, базах данных SQL Azure и базах данных SQL в Microsoft Fabric. Для более ранних версий используйте FOR JSON PATH для аналогичной функциональности.

Проверить и удостовериться в правильности JSON с помощью `JSON_CONTAINS`

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

Для надёжной обработки JSON требуется защитное программирование: перед разбором необходимо проверить, что JSON корректно сформирован. Затем убедитесь, что ожидаемые пути существуют перед извлечением значений и убедитесь, что значения соответствуют вашим ожиданиям перед их использованием в бизнес-логике. SQL Server предоставляет несколько функций для проверки содержимого JSON на каждом этапе обработки.

Понимание различий между нестрогими и строгими режимами путей

Выражения пути JSON можно использовать в двух режимах, которые управляют обработкой ошибок:

DECLARE @json NVARCHAR(MAX) = N'{"name": "Widget", "price": 29.99}';

-- Lax mode (default): Returns NULL for missing paths
SELECT JSON_VALUE(@json, 'lax $.description') AS LaxResult;

-- Strict mode: Raises an error for missing paths
SELECT JSON_VALUE(@json, 'strict $.description') AS StrictResult;

Результатом будет:

LaxResult
---------
NULL

-- Strict mode raises: Property cannot be found on the specified JSON path.

Используйте режим lax (по умолчанию), когда ожидаются отсутствующие свойства и должны возвращать NULL. Используйте strict режим, когда отсутствующие свойства указывают на проблему с данными, которая должна вызвать ошибку.

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

SELECT 
    ISJSON('{"name": "test"}') AS ValidJson,      -- Returns 1
    ISJSON('not valid json') AS InvalidJson,       -- Returns 0
    ISJSON(NULL) AS NullJson;                      -- Returns NULL

Результатом будет:

ValidJson   InvalidJson   NullJson
---------   -----------   --------
1           0             NULL

Чтобы корректно обрабатывать некорректные данные, используйте ISJSON в условиях WHERE для фильтрации строк с допустимым JSON или в выражениях CASE.

JSON_PATH_EXISTS Проверяет, существует ли определенный путь в документе JSON, например в следующем примере:

DECLARE @json NVARCHAR(MAX) = N'{"customer": {"name": "Contoso", "tier": "Gold"}}';

SELECT 
    JSON_PATH_EXISTS(@json, '$.customer.name') AS HasName,
    JSON_PATH_EXISTS(@json, '$.customer.email') AS HasEmail;

Результатом будет:

HasName   HasEmail
-------   --------
1         0

Эта функция возвращает значение 1, если путь существует, 0, если он не существует. Используйте его перед вызовом JSON_VALUE в строгом режиме или для условной обработки JSON с различными структурами.

Используйте JSON_CONTAINS для проверки, содержит ли документ JSON определенное значение или объект, как показано в следующем примере:

DECLARE @json NVARCHAR(MAX) = N'{"tags": ["sql", "database", "azure"]}';

SELECT 
    JSON_CONTAINS(@json, '"sql"', '$.tags') AS HasSqlTag,
    JSON_CONTAINS(@json, '"python"', '$.tags') AS HasPythonTag;

Результатом будет:

HasSqlTag   HasPythonTag
---------   ------------
1           0

Оптимизация запросов JSON с помощью вычисляемых столбцов

При частом запросе определенных свойств JSON ядро СУБД должно анализировать документ JSON для каждой строки в каждом запросе. Для таблиц с тысячами или миллионами строк этот повторяющийся анализ создает значительные затраты. Вычисляемые столбцы позволяют извлекать значения JSON один раз и хранить их в запрашиваемом формате, поддерживающем индексирование.

Почему анализ JSON влияет на производительность

Рассмотрим таблицу с 100 000 записями продуктов, где каждая строка содержит документ JSON с атрибутами продукта. Фильтрация запросов по категориям должна:

Прочитайте каждую строку из таблицы
Чтобы найти свойство категории, выполните синтаксический анализ документа JSON
Извлечение и сравнение значения

Без оптимизации даже простые фильтры требуют полного сканирования таблиц с анализом JSON для каждой строки.

Создание вычисляемых столбцов для свойств JSON

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

-- Add a computed column that extracts a JSON property
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category');

-- The column is now available in queries
SELECT ProductID, ProductName, ProductCategory
FROM Products
WHERE ProductCategory = 'Electronics';

Результатом будет:

ProductID   ProductName           ProductCategory
---------   -------------------   ---------------
101         Wireless Mouse        Electronics
102         USB Keyboard          Electronics
103         HD Monitor            Electronics

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

-- Persisted computed column stores the extracted value physically
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category') PERSISTED;

Сохраненные столбцы хранят извлеченное значение на диске, поэтому JSON анализируется только во время INSERT и UPDATE операций, а не во время SELECT запросов.

Добавление индексов для ускорения фильтрации

Реальный прирост производительности происходит от индексирования вычисляемых столбцов:

-- Create an index on the computed column
CREATE INDEX IX_Products_Category ON Products(ProductCategory);

-- Now this query uses an index seek instead of a table scan
SELECT ProductID, ProductName
FROM Products
WHERE ProductCategory = 'Electronics';

Без индекса запрос сканирует все 100 000 строк. С помощью индекса обработчик запросов выполняет поиск индекса и извлекает только соответствующие строки. Этот индекс может сократить время запроса с секунд до миллисекунд.

Индексирование нескольких свойств JSON

Для запросов, которые фильтруют по нескольким свойствам JSON, создайте вычисляемые столбцы и составной индекс:

-- Extract multiple properties
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category') PERSISTED,
    ProductBrand AS JSON_VALUE(ProductData, '$.brand') PERSISTED,
    ProductPrice AS CAST(JSON_VALUE(ProductData, '$.price') AS DECIMAL(10,2)) PERSISTED;

-- Create a composite index for common query patterns
CREATE INDEX IX_Products_Category_Brand ON Products(ProductCategory, ProductBrand);

-- Create an index for price range queries
CREATE INDEX IX_Products_Price ON Products(ProductPrice);

Теперь запросы фильтрации по категориям и бренду или сортировке по цене могут эффективно использовать эти индексы.

Подсказка

Для часто доступных свойств JSON вычисляемые столбцы с индексами могут повысить производительность запросов по сравнению с анализом JSON во время запроса. Отслеживайте шаблоны запросов и создавайте вычисляемые столбцы для свойств, используемых в WHERE, JOINили ORDER BY предложениях.

Преобразование реляционных данных в JSON с помощью FOR JSON

Для комплексных выходных данных JSON из запросов используйте FOR JSON PATH или FOR JSON AUTO:

SELECT 
    p.ProductID,
    p.Name,
    p.ListPrice,
    pc.Name AS CategoryName
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > 1000
FOR JSON PATH, ROOT('products');

Результатом будет:

{"products":[{"ProductID":749,"Name":"Road-150 Red, 62","ListPrice":3578.27,"CategoryName":"Road Bikes"},{"ProductID":750,"Name":"Road-150 Red, 44","ListPrice":3578.27,"CategoryName":"Road Bikes"}]}

FOR JSON PATH позволяет управлять структурой JSON с помощью псевдонимов столбцов. Используйте нотацию точек в псевдонимах для создания вложенных объектов:

SELECT 
    p.ProductID AS 'product.id',
    p.Name AS 'product.name',
    pc.Name AS 'product.category'
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ProductID = 680
FOR JSON PATH;

Результатом будет:

[{"product":{"id":680,"name":"HL Road Frame - Black, 58","category":"Road Frames"}}]

Псевдоним столбца 'product.id' создает вложенный product объект со свойством id . Этот метод позволяет формировать выходные данные в соответствии с ожидаемым форматом API без последующей обработки.

Для получения дополнительной информации о функциях JSON в SQL Server см. JSON данные в SQL Server и Функции JSON.

Обратная связь

Были ли сведения на этой странице полезными?

Обработка данных JSON со встроенными функциями

Извлечение значений с помощью JSON_VALUE и JSON_QUERY

Анализ массивов JSON с помощью OPENJSON

Создание JSON с помощью JSON_OBJECT и JSON_ARRAY

Агрегирование данных с помощью JSON_ARRAYAGG

Проверить и удостовериться в правильности JSON с помощью JSON_CONTAINS

Понимание различий между нестрогими и строгими режимами путей

Оптимизация запросов JSON с помощью вычисляемых столбцов

Почему анализ JSON влияет на производительность

Создание вычисляемых столбцов для свойств JSON

Добавление индексов для ускорения фильтрации

Индексирование нескольких свойств JSON

Преобразование реляционных данных в JSON с помощью FOR JSON

Обратная связь

Анализ массивов JSON с помощью `OPENJSON`

Создание JSON с помощью `JSON_OBJECT` и `JSON_ARRAY`

Агрегирование данных с помощью `JSON_ARRAYAGG`

Проверить и удостовериться в правильности JSON с помощью `JSON_CONTAINS`