JSON QUERY: примеры (SQL)

Примеры использования

Базовые примеры демонстрируют различные сценарии вызова функции.

Извлечение объекта:

DECLARE @json NVARCHAR(MAX) = N'{"info": {"address": {"city": "Moscow", "street": "Tverskaya"}}}';
SELECT JSON_QUERY(@json, '$.info.address') AS AddressObject;

AddressObject
---------------------------------
{"city": "Moscow", "street": "Tverskaya"}

Извлечение массива:

DECLARE @json NVARCHAR(MAX) = N'{"orders": [100, 200, 300]}';
SELECT JSON_QUERY(@json, '$.orders') AS OrdersArray;

OrdersArray
-------------------
[100, 200, 300]

Использование без указания пути (возврат всего JSON):

DECLARE @json NVARCHAR(MAX) = N'{"id": 1, "active": true}';
SELECT JSON_QUERY(@json) AS FullJson;

FullJson
------------------------
{"id": 1, "active": true}

Попытка извлечь скалярное значение (возвращает NULL):

DECLARE @json NVARCHAR(MAX) = N'{"name": "Ivan"}';
SELECT JSON_QUERY(@json, '$.name') AS ScalarResult;

ScalarResult
-------------
NULL

Похожие функции в MS SQL

В T-SQL существуют другие функции для работы с JSON, каждая решает свою задачу.

• JSON_VALUE – Извлекает скалярное значение из строки JSON (строку, число, boolean). Возвращает nvarchar(4000). Используется, когда необходим именно единичный, простой элемент, а не объект или массив.
• JSON_MODIFY – Обновляет значение свойства, добавляет новое свойство или удаляет существующее в строке JSON. Работает с любыми типами значений, включая объекты и массивы, передаваемые как строки JSON.
• OPENJSON – Табличная функция, преобразующая массив или объект JSON в набор строк. Позволяет выполнять реляционные операции (JOIN, WHERE) над данными JSON. Предпочтительна для парсинга и массового преобразования JSON в табличный формат.

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

Типичные ошибки

Ошибки обычно связаны с непониманием типа данных по указанному пути.

Ожидание объекта при скалярном значении:

DECLARE @json NVARCHAR(MAX) = N'{"count": 42}';
SELECT JSON_QUERY(@json, '$.count') AS Result; -- NULL, так как 42 - скаляр

Result
------
NULL

Невалидный JSON на входе:

SELECT JSON_QUERY('{invalid json}', '$');

Ошибка: Ожидался объект JSON или массив.

Некорректный синтаксис пути:

DECLARE @json NVARCHAR(MAX) = N'{"data": {}}';
SELECT JSON_QUERY(@json, '$invalid.path') AS Result;

Ошибка: Недопустимый escape-символ в пути JSON.

Игнорирование NULL результата при последующей обработке:

DECLARE @json NVARCHAR(MAX) = N'{"name": "Anna"}';
SELECT JSON_MODIFY(@json, '$.new', JSON_QUERY(@json, '$.name')) AS Result;
-- Поле 'new' будет удалено, так как JSON_QUERY вернул NULL

Result
------------------------
{"name": "Anna"}

Изменения в последних версиях

В SQL Server 2016 (13.x) функция была введена. Существенных изменений в синтаксисе или поведении в более поздних версиях SQL Server (2017, 2019, 2022) не было. Однако, с обновлениями ядра СУБД могут улучшаться производительность и обработка ошибок. В SQL Server 2022 продолжается развитие JSON-функциональности, например, улучшена поддержка типов данных TIME и DATETIME в функциях JSON. Для функции JSON QUERY специфичных нововведений не зафиксировано.

Расширенные примеры

Рекурсивное извлечение вложенных структур для последующего парсинга.

DECLARE @catalog NVARCHAR(MAX) = N'
{
  "store": {
    "books": [
      {"title": "Book A", "tags": ["fiction", "novel"]},
      {"title": "Book B", "tags": ["science"]}
    ]
  }
}';
SELECT 
  JSON_QUERY(book.value, '$.tags') AS BookTags
FROM OPENJSON(@catalog, '$.store.books') AS book;

BookTags
-------------------
["fiction", "novel"]
["science"]

Использование JSON_QUERY для безопасной вставки объекта в JSON_MODIFY.

DECLARE @old_json NVARCHAR(MAX) = N'{"id": 1}';
DECLARE @new_object NVARCHAR(MAX) = N'{"code": "X1", "active": false}';

SELECT JSON_MODIFY(@old_json, '$.data', JSON_QUERY(@new_object)) AS ModifiedJson;

ModifiedJson
---------------------------------------------------
{"id": 1, "data": {"code": "X1", "active": false}}

Извлечение и агрегация данных из массива объектов с использованием OPENJSON.

DECLARE @data NVARCHAR(MAX) = N'
{
  "results": [
    {"score": 85, "subject": "Math"},
    {"score": 90, "subject": "Physics"}
  ]
}';

SELECT 
  AVG(CAST(JSON_VALUE(value, '$.score') AS INT)) as AvgScore
FROM OPENJSON(JSON_QUERY(@data, '$.results'));

AvgScore
---------
87

Динамическое построение пути для извлечения данных.

DECLARE @config NVARCHAR(MAX) = N'{"ui": {"theme": "dark", "settings": {"lang": "ru"}}}';
DECLARE @path NVARCHAR(200) = '$.ui.settings';

SELECT JSON_QUERY(@config, @path) as DynamicPathResult;

DynamicPathResult
-------------------
{"lang": "ru"}

Обработка отсутствующего пути с ISNULL.

DECLARE @profile NVARCHAR(MAX) = N'{"name": "Alex"}';
SELECT 
  ISNULL(JSON_QUERY(@profile, '$.address'), '{}') AS Address
INTO #temp;
SELECT * FROM #temp;

Address
-------
{}

Аналоги в других СУБД

MySQL: Функция JSON_EXTRACT() возвращает элемент JSON, который может быть как скаляром, так и объектом/массивом. Для получения именно строкового представления часто комбинируют с JSON_UNQUOTE().

SELECT JSON_EXTRACT('{"a": [1,2]}', '$.a');

[1, 2]

PostgreSQL: Оператор -> возвращает тип json, а ->> – текст. Для извлечения объекта по пути используют комбинацию операторов.

SELECT ('{"x": {"y": 10}}'::json) -> 'x';

{"y": 10}

Oracle: Метод JSON_QUERY() в SQL стандартен и похож на MS SQL. Возвращает строку с фрагментом JSON.

SELECT JSON_QUERY('{"a": {"b": 5}}', '$.a') FROM dual;

{"b": 5}

SQLite: Функция json_extract() ведет себя аналогично MySQL, возвращая элемент JSON.

SELECT json_extract('{"id": [7]}', '$.id');

[7]

Основное отличие MS SQL – явное разделение на JSON_VALUE для скаляров и JSON_QUERY для объектов/массивов, тогда как в других СУБД одна функция часто возвращает значение любого типа JSON.