SQL JSON Query -retrieve data within a JSON array

Question

I have the following SQL "Product" table structure:

int Id
nvarchar(max) Details

Details contains JSON a string having the following structure:

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

I need to write an SQL Query that would look through the table and return all the Variants with a particular title.

The following work

Get all rows from the table whose Details field contains a specific title

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

Get all rows from the table where Details.Variants[0].Title is equal to '{string}'

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

Get all Ids from the table where Details.Variants[0].Title is equal to '{string}'

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

I need to get all Variants from all rows in the Product table, where the Variant title is equal to '{string}'

There is a similar example in this documentation but I can't get it to work for my particuar case.

There is also this stack post

Which database are you using? – Jim Macaulay Jun 18 '20 at 12:05 — Jim Macaulay, Jun 18 '20 at 12:05
@JimMacaulay SqlExpress - version 15.0.2000.5 – Razvan Olariu Jun 18 '20 at 12:12 — Razvan Olariu, Jun 18 '20 at 12:12

Zhorov · Answer 1 · 2020-06-18T13:00:04.320

You need to use OPENJSON() with explicit schema (columns definitions) and additional APPLYs to parse the input JSON and get the expected results. Note, that you need to use AS JSON option to specify that the $.Variants part of the stored JSON is a JSON array.

Table:

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"}]}"')

Statement:

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'

Result:

Id  Id      Description         Title       Price
1   10001   example description ABC / no    10

SQL JSON Query -retrieve data within a JSON array

1 Answers1