Cross apply with JSON array

Question

I have table stored data is in below format -

Now, I want to write a SQL query to represent this data in below format-

Note: Data stored in Product column is JSON array.

Answer 1

You need two additional APPLY operators with two different OPENJSON() calls. First call is with default schema and the result is a table with columns key, value and type. The second call is with explicit schema with the appropriate columns, defined using the WITH clause:

Table:

CREATE TABLE Data (
   CustomerID int,
   City nvarchar(50),
   Product nvarchar(max)
)
INSERT INTO Data
   (CustomerID, City, Product)
VALUES
   (1, N'Delhi', N'[{"Products": [{"Id": "1", "Name": "TV"}, {"Id": "2", "Name": "Laptop"}]}]'),
   (2, N'Bamgalore', N'[{"Products": [{"Id": "1", "Name": "TV"}, {"Id": "2", "Name": "Laptop"}, {"Id": "3", "Name": "Mobile"}]}]')

Statement:

SELECT d.CustomerID, j2.Id, j2.Name
FROM Data d
CROSS APPLY OPENJSON(d.Product, '$') j1
CROSS APPLY OPENJSON(j1.[value], '$.Products') WITH (
   Id nvarchar(10) '$.Id',
   Name nvarchar(50) '$.Name'
) j2

Result:

----------------------
CustomerID  Id  Name
----------------------
1           1   TV
1           2   Laptop
2           1   TV
2           2   Laptop
2           3   Mobile