Complex .Json Data Parsing with multiples Array elements into SQL (MS-SQL 2016)

Question

Complex .Json Data Parsing with multiples Array elements into SQL (MS-SQL 2016)

manihindus

SSC Enthusiast

Points: 113
More actions
April 15, 2021 at 12:12 pm

#3871537

DECLARE @json NVARCHAR(MAX) =N'{
"code": 200,
"msg": "success",
"data": [{
"ITEM": "SP",
"FACTORY_NO": [{
"FACTORY_NO": "1",
"ORDERQTY": 10
},
{
"FACTORY_NO": "1",
"ORDERQTY": 20
}
],
"DETAILS": [{
"LINE_INFO": "INLNE",
"CARD_NUMBER": 13
},
{
"LINE_INFO": "OUTLINE",
"CARD_NUMBER": 14
}
]
}
]
}'
Expected OUTPUT IN MS-SQL 2016
ITEM FACTORY_NO ORDERQTY LINE_INFO CARD_NUMBER
SP 1 10 INLNE 13
SP 1 10 OUTLNE 14
SP 1 20 INLNE 13
SP 1 20 OUTLNE 14
SELECT JSON_Value (c.value, '$.Item') as OU,
JSON_Value (p.value, '$.FACTORY_NO') as FACTORY_NO,
JSON_Value (p.value, '$.ORDERQTY') as ORDERQTY,
JSON_Value (C.value, '$.LINE_INFO') as LINE_INFO,
JSON_Value (C.value, '$.CARD_NUMBER') as CARD_NUMBER
from OPENJSON (@json, '$.Data') as c
CROSS APPLY OPENJSON (c.value, '$.FACTORY_NO') as p
I tried below SQL QUERY this. But Getting NULL in LINE_INFO ,CARD_NUMBER .
I can get the combination of FACTORY_NO. But NOT LINE_INFO,CARD_NUMBER combination.
Pls help me
Thanks in Advance ....

April 23, 2021 at 7:26 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 1

SELECT a.ITEM, b.FACTORY_NO, b.ORDERQTY, c.LINE_INFO, c.CARD_NUMBER
FROM OPENJSON (@json, '$.data')
WITH (ITEM VARCHAR(10) '$.ITEM',
      FACTORY_NO NVARCHAR(MAX) '$.FACTORY_NO' as json,
  DETAILS NVARCHAR(MAX) '$.DETAILS' as json ) a
CROSS APPLY OPENJSON(a.FACTORY_NO)
WITH (FACTORY_NO VARCHAR(10),
      ORDERQTY INT) b
CROSS APPLY OPENJSON(a.DETAILS)
WITH (LINE_INFO VARCHAR(10),
      CARD_NUMBER INT) c;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Alan Burstein SSC Guru Points: 61136 More actions · Answer 2

This is my first time messing with JSON in SQL. The BOL entry on this helped me.

First, JSON is cases sensitive so, $.Data is not the same as $.data. Next, LINE_INFO and CARD_NUMBER are in the FACTORY_NO subtree, ORDERQTY and CARDNUMBER are in the DETAILS subtree. ; you need an additional APPLY. This is what you need:

SELECT 
  JSON_Value (c.value, '$.ITEM')        as OU,
  JSON_Value (p.value, '$.FACTORY_NO')  as FACTORY_NO,
  JSON_Value (p.value, '$.ORDERQTY')    as ORDERQTY,
  JSON_Value (d.value, '$.LINE_INFO')   as LINE_INFO,
  JSON_Value (d.value, '$.CARD_NUMBER') as CARD_NUMBER
from        OPENJSON (@json, '$.data')         as c
CROSS APPLY OPENJSON (c.value, '$.FACTORY_NO') as p
CROSS APPLY OPENJSON (c.value, '$.DETAILS')    as d;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Harsh78 Newbie Points: 5 More actions · Answer 3

Thankyou, I really Appreciate the way you did it, It really helped a lot. Keep Sharing more such.

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 4

Harsh78 wrote:

Thankyou, I really Appreciate the way you did it, It really helped a lot. Keep Sharing more such.

That actually looks and sounds like a precursor to SPAM, as has happened so many times on this site. If it is, simply go away because you attempt won't work here. If it's not, then welcome aboard.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)