SQL JSON Query — получение данных в массиве JSON

У меня есть следующая структура таблицы SQL "Product":

int Id
nvarchar(max) Details

Details содержит JSON строку, имеющую следующую структуру:

{
"Id": "10001",
"Description": "example description",
"Variants": [{
        "Title": "ABC / no",
        "Price": "10"
    }, {
        "Title": "ABC / Yes",
        "Price": "20",
    }, {
        "Title": "ABC / Yes",
        "Price": "30",
    }]
}

Мне нужно написать SQL-запрос, который будет просматривать таблицу и возвращать все варианты с определенным заголовком.

Следующая работа

Получить все строки из таблицы, поле сведений которой содержит определенный заголовок

SELECT * FROM Products 
WHERE JSON_VALUE(Details, '$.Description') = 'example description'

Получить все строки из таблицы, где Details.Variants[0].Title равно '{string}'

SELECT * FROM Products 
WHERE JSON_VALUE(Details, '$.Variants[0].Title') = 'ABC / no'

Получить все Id из таблицы, где Details.Variants[0].Title равно '{string}'

SELECT JSON_VALUE(Details, '$.Id') 
FROM Products 
WHERE JSON_VALUE(Details, '$.Variants[0].Title') = 'ABC / no'

Мне нужно получить все варианты из всех строк таблицы продуктов, где заголовок варианта равен '{string}'

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

Также есть этот запись стека


json sql sql-server json-query json-value
person Razvan Olariu    schedule 18.06.2020    source источник
comment
Какую базу данных вы используете?   -  person Jim Macaulay    schedule 18.06.2020
comment
@JimMacaulay SqlExpress — версия 15.0.2000.5   -  person Razvan Olariu    schedule 18.06.2020

Ответы (1)


arrow_upward
0
arrow_downward

Вам нужно использовать OPENJSON() с явной схемой (определения столбцов) и дополнительные APPLY для анализа входного JSON и получения ожидаемых результатов. Обратите внимание, что вам нужно использовать параметр AS JSON, чтобы указать, что часть $.Variants сохраненного JSON является массивом JSON.

Таблица:

CREATE TABLE Products (Id int, Details nvarchar(max))
INSERT INTO Products (Id, Details)
VALUES (1, N'{"Id":"10001","Description":"example description","Variants":[{"Title":"ABC / no","Price":"10"},{"Title":"ABC / Yes","Price":"20"},{"Title":"ABC / Yes","Price":"30"}]}"')

Заявление:

SELECT p.Id, j1.Id, j1.Description, j2.Title, j2.Price
FROM Products p
CROSS APPLY OPENJSON (p.Details, '$') WITH (
    Id int '$.Id',
    [Description] nvarchar(100) '$.Description',
    Variants nvarchar(max) '$.Variants' AS JSON
) j1
CROSS APPLY OPENJSON(j1.Variants) WITH (
    Title nvarchar(100) '$.Title',
    Price nvarchar(10) '$.Price'
) j2
WHERE 
   j2.Title = 'ABC / no'
   -- or j1.Description = 'example description'

Результат:

Id  Id      Description         Title       Price
1   10001   example description ABC / no    10
person Zhorov    schedule 18.06.2020