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 откроет внутреннюю коллекцию тегов и вернет ее в виде таблицы. Затем мы можем фильтровать результаты по некоторому значению в таблице.