-1

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 ;)

  • What is your actual question here? You seem to be aware of the openjson() existence; what did you try and what didn't work as you expected? – mustaccio Jan 14 at 15:40
  • Im not able to find any examples on how to combine child arrays with parrent properties and get the right output of that – Mr Zach Jan 14 at 15:57
  • @MrZach What is the expected output? – Zhorov Jan 14 at 21:46
  • Whats written in the question – Mr Zach Jan 14 at 22:22
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.