Microsoft SQL Server
Запросы с данными JSON

Использование значений из JSON в запросе

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

select ProductID, Name, Color, Size, Price, JSON_VALUE(Data, '$.Type') as Type
from Product
where JSON_VALUE(Data, '$.Type') = 'part'

Использование значений JSON в отчетах

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

select JSON_VALUE(Data, '$.Type') as type,
        AVG( cast(JSON_VALUE(Data, '$.ManufacturingCost') as float) ) as cost
from Product
group by JSON_VALUE(Data, '$.Type') 
having JSON_VALUE(Data, '$.Type') is not null

Отфильтровать плохой текст JSON из результатов запроса

Если некоторый текст JSON может быть неправильно отформатирован, вы можете удалить эти записи из запроса с помощью функции ISJSON.

select ProductID, Name, Color, Size, Price, JSON_VALUE(Data, '$.Type') as Type
from Product
where JSON_VALUE(Data, '$.Type') = 'part'
and ISJSON(Data) > 0

Обновить значение в столбце JSON

Функция JSON_MODIFY может использоваться для обновления значения на каком-то пути. Вы можете использовать эту функцию для изменения исходного значения ячейки JSON в инструкции UPDATE:

update Product
set Data = JSON_MODIFY(Data, '$.Price', 24.99)
where ProductID = 17;

Функция JSON_MODIFY обновит или создаст ценовой ключ (если он не существует). Если новое значение равно NULL, ключ будет удален. Функция JSON_MODIFY будет обрабатывать новое значение в виде строки (специальные символы escape, обернуть ее двойными кавычками для создания правильной строки JSON). Если ваше новое значение - фрагмент JSON, вы должны обернуть его функцией JSON_QUERY:

update Product
set Data = JSON_MODIFY(Data, '$.tags', JSON_QUERY('["promo","new"]'))
where ProductID = 17;

Функция JSON_QUERY без второго параметра ведет себя как «приведение в JSON». Поскольку результатом JSON_QUERY является действительный фрагмент JSON (объект или массив), JSON_MODIFY не сможет избежать этого значения при изменении ввода JSON.

Добавить новое значение в массив JSON

Функция JSON_MODIFY может использоваться для добавления нового значения в некоторый массив внутри JSON:

update Product
set Data = JSON_MODIFY(Data, 'append $.tags', "sales")
where ProductID = 17;

Новое значение будет добавлено в конце массива, или будет создан новый массив со значением ["sales"]. Функция JSON_MODIFY будет обрабатывать новое значение в виде строки (специальные символы escape, обернуть ее двойными кавычками для создания правильной строки JSON). Если ваше новое значение - фрагмент JSON, вы должны обернуть его функцией JSON_QUERY:

update Product
set Data = JSON_MODIFY(Data, 'append $.tags', JSON_QUERY('{"type":"new"}'))
where ProductID = 17;

Функция JSON_QUERY без второго параметра ведет себя как «приведение в JSON». Поскольку результатом JSON_QUERY является действительный фрагмент JSON (объект или массив), JSON_MODIFY не сможет избежать этого значения при изменении ввода JSON.

Стол JOIN с внутренней коллекцией JSON

Если у вас есть «дочерняя таблица», отформатированная как коллекция JSON и сохраненная в строке как столбец JSON, вы можете распаковать эту коллекцию, преобразовать ее в таблицу и присоединиться к ней с родительской строкой. Вместо стандартного оператора JOIN вы должны использовать CROSS APPLY. В этом примере части продукта отформатированы как коллекция объектов JSON и хранятся в столбце данных:

select ProductID, Name, Size, Price, Quantity, PartName, Code
from Product
    CROSS APPLY OPENJSON(Data, '$.Parts') WITH (PartName varchar(20), Code varchar(5))

Результат запроса эквивалентен соединению между таблицами Product и Part.

Поиск строк, содержащих значение в массиве JSON

В этом примере массив тегов может содержать различные ключевые слова, такие как [«promo», «sales»], поэтому мы можем открыть этот массив и значения фильтра:

select ProductID, Name, Color, Size, Price, Quantity
from Product
    CROSS APPLY OPENJSON(Data, '$.Tags') 
where value = 'sales'

OPENJSON откроет внутреннюю коллекцию тегов и вернет ее в виде таблицы. Затем мы можем фильтровать результаты по некоторому значению в таблице.