CODEWITHSUNDEEP
jsonsql-server
Chris
Chris

Reputation: 289

SQL Server 2017 JSON Parse object with array into SQL Rows

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?

Upvotes: 1

Views: 143

Answers (3)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88851

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

Upvotes: 3

Zhorov
Zhorov

Reputation: 29933

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

Upvotes: 1

GSerg
GSerg

Reputation: 78134

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
;

Upvotes: 0

Related Questions