SQL Server 2017 JSON Parse object with array into SQL Rows

Question

I'm trying to take a JSON object which has an array inside of it, and break it out into corresponding rows.

I Have the following JSON object:

{
"CompID":["1","20","22"],
"Date":"21122005",
"ID":"12",
"Total":"5"
}

This is what I'm trying to get this from the JSON object:

Date        ID  Total   CompID
21122005    12    5     1
21122005    12    5     20
21122005    12    5     22

This is my SQL Code:

Declare @Json varchar(max) = '{"CompID":["1","20","22"],
"Date":"21122005",
"ID":"12",
"Total":"5"}'

Select A.Date, A.ID, A.Total,
B.CompID
From OpenJson(@Json)
With(
Date varchar(12) 
,ID varchar(10)
,Total varchar(4)
,CompID varchar(3) '$.CompID'
)A
OUTER APPLY OPENJSON(JSON_QUERY(@Json,'$.CompID')) 
    WITH(CompID NVARCHAR(MAX) AS JSON) B

With the above code it returns the three rows that I need, but I get Null values in the CompID column.

Date        ID  Total   CompID
21122005    12    5     NULL
21122005    12    5     NULL
21122005    12    5     NULL

How do I replace the Null values with the correct CompID numbers?

David Browne - Microsoft · Accepted Answer · 2020-01-25 19:03:16Z

3

You're pretty close. You don't reference the JSON again in the APPLY. You project a JSON column from the first OPENJSON and use that.

Like this:

Declare @Json varchar(max) = '
{
 "CompID":["1","20","22"],
 "Date":"21122005",
 "ID":"12",
 "Total":"5"
}'

Select 
  A.Date, 
  A.ID, 
  A.Total,
  --A.CompID,
  cast(B.value as int) CompID 
From OpenJson(@Json)
With
(
     Date varchar(12) 
    ,ID varchar(10)
    ,Total varchar(4)
    ,CompID nvarchar(max) as json
)A
OUTER APPLY OPENJSON(CompID) B

outputs

Date         ID         Total CompID
------------ ---------- ----- -----------
21122005     12         5     1
21122005     12         5     20
21122005     12         5     22

edited Jan 25, 2020 at 19:03

answered Jan 25, 2020 at 17:23

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

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

Comments

Zhorov · Accepted Answer · 2020-01-25 17:51:53Z

1

You are close - just fix the columns definitions in the OPENJSON() calls:

JSON:

DECLARE @Json varchar(max) = 
'{
   "CompID":[
      "1",
      "20",
      "22"
   ],
   "Date":"21122005",
   "ID":"12",
   "Total":"5"
}'

Statement:

SELECT
   A.Date, 
   A.ID, 
   A.Total,
   b.CompID
FROM OpenJson(@Json) WITH (
   Date varchar(12) '$.Date',
   ID varchar(10) '$.ID',
   Total varchar(4) '$.Total'
) A
OUTER APPLY OPENJSON(@Json, '$.CompID') WITH (
   CompID varchar(3) '$'
) B

Result:

------------------------------
Date        ID  Total   CompID
------------------------------
21122005    12  5       1
21122005    12  5       20
21122005    12  5       22

answered Jan 25, 2020 at 17:51

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Comments

GSerg · Accepted Answer · 2020-01-25 16:55:48Z

0

select
  a.Date,
  a.ID,
  a.Total,
  b.CompID
from
  openjson(@js) with (Date varchar(12), ID varchar(10), Total varchar(4)) a
  cross join (select value as CompID from openjson(@js, N'$.CompID')) b
;

answered Jan 25, 2020 at 16:55

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

Collectives™ on Stack Overflow

SQL Server 2017 JSON Parse object with array into SQL Rows

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related