Sql-server – converting json to columns

jsonsql server

I have the following json structure

{ "products":  [

{
  "description": "Horse shoes",
  "productid": 1,
  "orders": [
    { "customerid": 101 },
    { "customerid": 102 },
    { "customerid": 103 }
  ]
},
{
  "description": "Horse food",
  "productid": 2,
  "orders": [
    { "customerid": 104 },
    { "customerid": 105 },
    { "customerid": 106 }
  ]
}  ]}

How can i convert this into rows by using something like

select * from myTable cross apply openJson(myJsonColumn)…

And get a result like this:

rowId|productId|Description|CustomerIdHasOrdered
1000|1|Hourse shoes"|101
1000|1|Hourse shoes"|102
1000|1|Hourse shoes"|103
1001|2|Hourse food"|104
1001|2|Hourse food"|105
1001|2|Hourse food"|106

This is just some simple example data and not a real way to store this kind of information 😉

Best Answer

This has been answered before. The problem is you're reading the top-level JSON only (Products). You need to use CROSS APPLY and read the nested JSON to get all the attributes. See this working example.

DECLARE @J NVARCHAR(MAX) = N'{ "products":  [

{
  "description": "Horse shoes",
  "productid": 1,
  "orders": [
    { "customerid": 101 },
    { "customerid": 102 },
    { "customerid": 103 }
  ]
},
{
  "description": "Horse food",
  "productid": 2,
  "orders": [
    { "customerid": 104 },
    { "customerid": 105 },
    { "customerid": 106 }
  ]
}  ]}'

SELECT ProductID
  ,Description
  ,CustomerID
FROM OPENJSON(@j)
WITH 
(
  Products NVARCHAR(MAX) '$.products' AS JSON
) AS J
CROSS APPLY OPENJSON(J.Products)
WITH
(
  Description NVARCHAR(255) '$.description',
  ProductID INT '$.productid',
  Orders NVARCHAR(MAX) '$.orders' AS JSON
) AS Products
CROSS APPLY OPENJSON(Products.Orders)
WITH
(
  CustomerID INT '$.customerid'
)

Results:

ProductID   Description     CustomerID
--------------------------------------
1           Horse shoes     101
1           Horse shoes     102
1           Horse shoes     103
2           Horse food      104
2           Horse food      105
2           Horse food      106