MS-sql (2016) Query for json with multiple array elements

Transact-SQL
1

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

I tried below SQL QUERY this. But Getting NULL in LINE_INFO ,CARD_NUMBER .
I can get the combination of FACTORY_NO. But LINE_INFO,CARD_NUMBER combination.

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

please let me know how i split in SQL query

Thanks in advance..

2

you had it right, just missing $

SELECT JSON_Value (c.value, '$.ITEM') as ITEM,
      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
2 Likes
3

Thank alot..@yosiasz

4

--I tried to parse the below json data into MS-sql 2016 .
--i tried with below select Query. But I'm Getting Blank on execute in MS-sql2016 .any one can help me to crack this..
--Expected no of rows in select query is 6 rows .
---( Hint:with the cross combination of factory_no & details array element values records should split )

DECLARE @JSON NVARCHAR(MAX) =N'{
"code":200,
"msg":"success",
"data":
[
{
"OU":"CN",
"SUBCON":0,
"BRANCH":"",
"DEPARTMENT":"SEWING",
"ISSUEDATE":"2020-05-26T13:22:59.807",
"EFFDATE":"2020-05-26T13:22:59.807",
"APPROVAL_NO":"CS2000225",
"BUYER":"UNARMOUR",
"STYLE_NO":"1357134",
"STYLE_TYPE":"BOTTOM",
"SUBCON_QTY":0.0000,
"SUBCON_QTY2":0.0,
"REVISION_NO":2,
"REVISION_RMK":"",
"CHANGEED_BY":"KWTPIE",
"CHANGE_DATE":"2020-08-05T14:01:06.393",
"FACTORY_NO":[
{
"FACTORY_NO":"204039",
"ORDERQTY":514
},
{

"FACTORY_NO":"204049",
"ORDERQTY":515
}
],
"DETAILS":[
{
"LINE_INFO":"OL",
"CARD_NUMBER":13,
"CARDCODE":"B06031107",
"CONSTRUCTION":"拷前口袋布",
"MACHINE":"拷边车",
"SAM_SEC":35.0000,
"SAM_QC":5.0000,
"SAM":40.0000,
"UNIT_PRICEPCS":0.19500000000000000000,
"UNIT_PRICEDZ":2.3400,
"DAILYTARGETQTY":720.000000,
"HEADCOUNT":1.0000,
"REMARKS":"",
"TOTALMACHINELIST":8,
"TOTALSAM":893.0000,
"TOTALHOURPCS":4.0,
"TOTAL8HOURPCS":32.00,
"WORKER":25,
"HOURTARGETQTY":806.0
},
{
"LINE_INFO":"IL",
"CARD_NUMBER":18,
"CARDCODE":"U06110210",
"CONSTRUCTION":"腰头锁眼夹衬2+撕衬",
"MACHINE":"锁眼车",
"SAM_SEC":8.0000,
"SAM_QC":4.0000,
"SAM":12.0000,
"UNIT_PRICEPCS":0.05666666666666666666,
"UNIT_PRICEDZ":0.6800,
"DAILYTARGETQTY":2400.000000,
"HEADCOUNT":0.5000,
"REMARKS":"锁眼车2800-3000rpm 双头锁眼车跳P06111704",
"TOTALMACHINELIST":8,
"TOTALSAM":893.0000,
"TOTALHOURPCS":4.0,
"TOTAL8HOURPCS":32.00,
"WORKER":25,
"HOURTARGETQTY":806.0
}
]
},
{
"OU":"CN",
"SUBCON":0,
"BRANCH":"",
"DEPARTMENT":"SEWING",
"ISSUEDATE":"2020-05-26T13:22:59.807",
"EFFDATE":"2020-05-26T13:22:59.807",
"APPROVAL_NO":"CS2000225",
"BUYER":"UNARMOUR",
"STYLE_NO":"1357134",
"STYLE_TYPE":"BOTTOM",
"SUBCON_QTY":0.0000,
"SUBCON_QTY2":0.0,
"REVISION_NO":2,
"REVISION_RMK":"",
"CHANGEED_BY":"KWTPIE",
"CHANGE_DATE":"2020-08-05T14:01:06.393",
"FACTORY_NO":[
{
"FACTORY_NO":"204039",
"ORDERQTY":514
}
],
"DETAILS":[
{
"LINE_INFO":"OL",
"CARD_NUMBER":13,
"CARDCODE":"B06031107",
"CONSTRUCTION":"拷前口袋布",
"MACHINE":"拷边车",
"SAM_SEC":35.0000,
"SAM_QC":5.0000,
"SAM":40.0000,
"UNIT_PRICEPCS":0.19500000000000000000,
"UNIT_PRICEDZ":2.3400,
"DAILYTARGETQTY":720.000000,
"HEADCOUNT":1.0000,
"REMARKS":"",
"TOTALMACHINELIST":8,
"TOTALSAM":893.0000,
"TOTALHOURPCS":4.0,
"TOTAL8HOURPCS":32.00,
"WORKER":25,
"HOURTARGETQTY":806.0
}
]
}
]
}'

select isjson(@json)
IF isjson(@json) =1
BEGIN
SELECT JSON_Value (c.value, '.OU') as OU, JSON_Value (c.value, '.SUBCON') as SUBCON,
JSON_Value (c.value, '.BRANCH') as BRANCH, JSON_Value (c.value, '.DEPARTMENT') as DEPARTMENT,
JSON_Value (c.value, '.ISSUEDATE') as ISSUEDATE, JSON_Value (c.value, '.EFFDATE') as EFFDATE,
JSON_Value (c.value, '.APPROVAL_NO') as APPROVAL_NO, JSON_Value (c.value, '.BUYER') as BUYER,
JSON_Value (c.value, '.STYLE_NO') as STYLE_NO, JSON_Value (c.value, '.STYLE_TYPE') as STYLE_TYPE,
JSON_Value (c.value, '.SUBCON_QTY') as SUBCON_QTY, JSON_Value (c.value, '.SUBCON_QTY2') as SUBCON_QTY2,
JSON_Value (c.value, '.REVISION_NO') as REVISION_NO, JSON_Value (c.value, '.REVISION_RMK') as REVISION_RMK,
JSON_Value (c.value, '.CHANGEED_BY') as CHANGEED_BY, JSON_Value (c.value, '.CHANGE_DATE') as CHANGE_DATE,
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,
JSON_Value (d.value, '.CARDCODE') as CARDCODE, JSON_Value (d.value, '.CONSTRUCTION') as CONSTRUCTION,
JSON_Value (d.value, '.MACHINE') as MACHINE, JSON_Value (d.value, '.SAM_SEC') as SAM_SEC,
JSON_Value (d.value, '.SAM_QC') as SAM_QC, JSON_Value (d.value, '.SAM') as SAM,
JSON_Value (d.value, '.UNIT_PRICEPCS') as UNIT_PRICEPCS, JSON_Value (d.value, '.UNIT_PRICEDZ') as UNIT_PRICEDZ,
JSON_Value (d.value, '.DAILYTARGETQTY') as DAILYTARGETQTY, JSON_Value (d.value, '.HEADCOUNT') as HEADCOUNT,
JSON_Value (d.value, '.REMARKS') as REMARKS, JSON_Value (d.value, '.TOTALMACHINELIST') as TOTALMACHINELIST,
JSON_Value (d.value, '.TOTALSAM') as TOTALSAM, JSON_Value (d.value, '.TOTALHOURPCS') as TOTALHOURPCS,
JSON_Value (d.value, '.TOTAL8HOURPCS') as TOTAL8HOURPCS, JSON_Value (d.value, '.WORKER') as WORKER,
JSON_Value (d.value, '.HOURTARGETQTY') as HOURTARGETQTY from OPENJSON (@json, '.Data') as c
CROSS APPLY OPENJSON (c.value, '.FACTORY_NO') as p CROSS APPLY OPENJSON (c.value, '.DETAILS') as d

END

5

Look at the previous answer and compare. I encourage you to read the documentation and understand fully what openjson does.

it is case sensitive so change to


from OPENJSON (@json, '$.data') as c

instead of


from OPENJSON (@json, '$.Data') as c
6

Thanks for sharing this...Really it's helpful.. By the way ,fixed my query ..@yosiasz