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

Простая демонстрация JSON_VALUE

Извлечение строкового значения из объекта JSON.

SELECT JSON_VALUE('{"name": "Иван", "age": 30}', '$.name') AS Имя;

Иван

Получение числового значения и преобразование типа.

SELECT CAST(JSON_VALUE('{"price": 125.50}', '$.price') AS decimal(10,2)) AS Цена;

125.50

Обращение к элементу массива по индексу.

SELECT JSON_VALUE('{"items": ["яблоко", "банан"]}', '$.items[0]') AS Элемент;

яблоко

Использование режима 'lax' при отсутствии пути.

SELECT JSON_VALUE('{"a": 1}', '$.b', 'lax') AS Результат;

NULL

Использование режима 'strict', вызывающего ошибку.

SELECT JSON_VALUE('{"a": 1}', '$.b', 'strict') AS Результат;

Сообщение об ошибке: "Property cannot be found on the specified JSON path."

Сравнение с другими функциями JSON в MS SQL

В MS SQL Server для работы с JSON доступно несколько функций, каждая имеет свою область применения.

• JSON_QUERY используется для извлечения фрагмента JSON в виде объекта или массива. В отличие от JSON_VALUE, возвращающей скаляр, JSON_QUERY возвращает JSON-фрагмент как строку. Пример: JSON_QUERY('{"data": {"x":1}}', '$.data') вернет {"x":1}.
• OPENJSON — это функция, преобразующая JSON-массив или объект в табличный формат. Она полезна, когда необходимо развернуть JSON в набор строк. Например, для парсинга списка элементов.
• ISJSON выполняет проверку строки на корректность JSON-формата и возвращает 1 или 0. Её рекомендуется использовать перед вызовом JSON_VALUE для избежания ошибок.

JSON_VALUE предпочтительнее, когда требуется одно конкретное значение. Для извлечения сложных структур или преобразования в таблицу выбирают JSON_QUERY или OPENJSON.

Аналоги в других системах управления базами данных

Работа с JSON поддерживается во многих современных СУБД, но синтаксис и возможности отличаются.

MySQL предлагает операторы ->> и функцию JSON_EXTRACT(). Пример: SELECT JSON_EXTRACT('{"name": "Иван"}', '$.name'); Результат: "Иван". Оператор ->> дополнительно удаляет кавычки.

PostgreSQL использует операторы -> и ->> для доступа по ключу. Пример: SELECT '{"name": "Иван"}'::json->>'name'; Результат: Иван.

Oracle имеет функцию JSON_VALUE с похожим синтаксисом, но с дополнительными возможностями, такими как возврат данных определенного типа. Пример: SELECT JSON_VALUE('{"id": 100}', '$.id' RETURNING NUMBER) FROM dual; Результат: 100.

SQLite использует функцию json_extract(). Пример: SELECT json_extract('{"value": 5}', '$.value'); Результат: 5.

В отличие от MS SQL, в некоторых СУБД нет явного разделения на скалярные и нескалярные извлечения через разные функции.

Распространенные проблемы при работе с функцией

Ошибки часто возникают из-за неверного формата пути или типа данных.

Некорректный JSON-формат в исходной строке приводит к возврату NULL.

SELECT JSON_VALUE('{not json}', '$.key') AS Результат;

NULL

Попытка извлечь объект или массив с помощью JSON_VALUE возвращает NULL.

SELECT JSON_VALUE('{"info": {"x": 1}}', '$.info') AS Объект;

NULL

Превышение длины возвращаемого значения (более 4000 символов).

SELECT JSON_VALUE('{"text": "' + REPLICATE('A', 5000) + '"}', '$.text') AS ДлинныйТекст;

NULL

Использование несуществующего пути без указания режима 'lax'.

SELECT JSON_VALUE('{"a": 1}', '$.b') AS Путь;

NULL

Ошибка в режиме 'strict' при некорректном пути.

SELECT JSON_VALUE('{"a": 1}', '$.b', 'strict');

Сообщение об ошибке: "Property cannot be found on the specified JSON path."

Эволюция функциональности в новых версиях

Функция JSON VALUE была введена в SQL Server 2016 вместе с начальной поддержкой JSON. В последующих версиях существенных изменений в синтаксисе или поведении функции не произошло. Однако общая работа с JSON в SQL Server продолжает развиваться: добавляются новые функции, улучшается производительность и расширяется стандарт JSON Path. В SQL Server 2022 были улучшены оптимизации запросов, использующих JSON функции, включая JSON_VALUE. Рекомендуется использовать актуальные версии для получения лучшей производительности и стабильности.

Сложные сценарии применения JSON_VALUE

Извлечение значения из вложенного объекта внутри массива.

DECLARE @json NVARCHAR(MAX) = '{"orders": [{"id": 1, "sum": 100}, {"id": 2, "sum": 200}]}';
SELECT JSON_VALUE(@json, '$.orders[0].sum') AS СуммаПервогоЗаказа;

100

Использование в условии WHERE для фильтрации записей.

CREATE TABLE #Products (Id INT, Data NVARCHAR(MAX));
INSERT INTO #Products VALUES (1, '{"name": "стол", "price": 5000}'), (2, '{"name": "стул", "price": 1500}');
SELECT * FROM #Products WHERE CAST(JSON_VALUE(Data, '$.price') AS INT) > 2000;

Id Data
1  {"name": "стол", "price": 5000}

Комбинация с функцией OPENJSON для обработки массива.

DECLARE @jsonArray NVARCHAR(MAX) = '[{"city": "Москва"}, {"city": "Санкт-Петербург"}]';
SELECT value FROM OPENJSON(@jsonArray) WHERE JSON_VALUE(value, '$.city') = 'Москва';

{"city": "Москва"}

Извлечение значения с использованием переменной в пути.

DECLARE @path NVARCHAR(100) = '$.details.weight';
SELECT JSON_VALUE('{"details": {"weight": 10}}', @path) AS Вес;

10

Работа с логическими значениями и преобразование в BIT.

SELECT CASE WHEN JSON_VALUE('{"active": true}', '$.active') = 'true' THEN 1 ELSE 0 END AS Активен;

1

Обработка NULL-значений внутри JSON с помощью ISNULL.

SELECT ISNULL(JSON_VALUE('{"comment": null}', '$.comment'), 'нет комментария') AS Комментарий;

нет комментария