Transform Json Nested Object Array To Table Row

Question

I have a json like:

[
  {
    "Id": "1234",
    "stockDetail": [
      {
        "Number": "10022_1",
        "Code": "500"
      },
      {
        "Number": "10022_1",
        "Code": "600"
      }
    ]
  },
  {
    "Id": "1235",
    "stockDetail": [
      {
        "Number": "10023_1",
        "Code": "100"
      },
      {
        "Number": "10023_1",
        "Code": "100"
      }
    ]
  }
]

How to convert it in sql table like below:

+------+---------+------+
|  Id  | Number  | Code |
+------+---------+------+
| 1234 | 10022_1 |  500 |
| 1234 | 10022_1 |  600 |
| 1235 | 10023_1 |  100 |
| 1235 | 10023_1 |  100 |
+------+---------+------+

Andrea · Accepted Answer · 2018-12-24 09:31:49Z

3

If you need to define typed columns you can use OPENJSON with WITH clause:

DECLARE @j nvarchar(max) = N'[
  {
    "Id": "1234",
    "stockDetail": [
      { "Number": "10022_1",
        "Code": "500"
      },
      { "Number": "10022_1",
        "Code": "600"
      }
    ]
  },
  {
    "Id": "1235",
    "stockDetail": [
      { "Number": "10023_1",
        "Code": "100"
      },
      { "Number": "10023_1",
        "Code": "100"
      }
    ]
  }
]'

select father.Id, child.Number, child.Code
from openjson (@j) 
with (
    Id          int,
    stockDetail nvarchar(max) as json
) as father
cross apply openjson (father.stockDetail)  
with (
    Number nvarchar(100),
    Code   nvarchar(100)  
) as child

Result:

answered Dec 24, 2018 at 9:31

Andrea

12.5k17 gold badges71 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zhorov · Accepted Answer · 2018-12-24 08:18:40Z

In your case you may try to CROSS APPLY the JSON child node with the parent node:

DECLARE @json nvarchar(max)
SET @json = N'
[
  {
    "Id": "1234",
    "stockDetail": [
      {
        "Number": "10022_1",
        "Code": "500"
      },
      {
        "Number": "10022_1",
        "Code": "600"
      }
    ]
  },
  {
    "Id": "1235",
    "stockDetail": [
      {
        "Number": "10023_1",
        "Code": "100"
      },
      {
        "Number": "10023_1",
        "Code": "100"
      }
    ]
  }
]'

SELECT
    JSON_Value (i.value, '$.Id') as ID, 
    JSON_Value (d.value, '$.Number') as [Number], 
    JSON_Value (d.value, '$.Code') as [Code]
FROM OPENJSON (@json, '$') as i
CROSS APPLY OPENJSON (i.value, '$.stockDetail') as d

Output:

ID      Number  Code
1234    10022_1 500
1234    10022_1 600
1235    10023_1 100
1235    10023_1 100

Collectives™ on Stack Overflow

Transform Json Nested Object Array To Table Row

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related