SQL Server- JSON object contains array inside an array

Question

The JSON object contains a nested array of objects:

DECLARE @json   NVARCHAR(MAX) = 
'{
    "productId": "508634800",
    "description": "Generic description",
    "itemInfo": [
        {
            "sku": "4645309",
            "attributes": [
                {
                    "attrName": "Size",
                    "attrValue": "M"
                },
                {
                    "attrName": "Color",
                    "attrValue": "Burgundy"
                }
            ]
        },
        {
            "sku": "4645318",
            "attributes": [
                {
                    "attrName": "Size",
                    "attrValue": "XL"
                },
                {
                    "attrName": "Color",
                    "attrValue": "Burgundy"
                }
            ]
        },
            {
            "sku": "4645400",
            "attributes": [
                {
                    "attrName": "Size",
                    "attrValue": "L"
                },
                {
                    "attrName": "Color",
                    "attrValue": "White"
                }
            ]
        }
    ]
}'

I would like to get the full list of matching pairs:

color              size
-----------------  ----------------- 
Burgundy           M
Burgundy           XL
White              L

The problem that I'm hitting is that the only way I seem to be able to parse inside an array is to use OPENJSON, and I can't nest those calls.

The best solution I've come up with is to grab the first array, dump it to a temp table, then I can use CROSS APPLY to pull the data back out:

SELECT
    @json   = attributes
FROM
    OPENJSON(@json)
WITH (
        attributes  NVARCHAR(MAX) '$.itemInfo' AS JSON
    ) 

SELECT
    row = ROW_NUMBER() OVER (ORDER BY attributes) 
,   attributes
INTO
    #tempAttributes
FROM
    OPENJSON(@json)
WITH (
        attributes  NVARCHAR(MAX) '$.attributes'  AS JSON
) 


SELECT DISTINCT
    color   =   color.attrValue
,   size        =   size.attrValue
FROM
    #tempAttributes
CROSS APPLY OPENJSON(attributes)
WITH (
        attrName    NVARCHAR(MAX) '$.attrName' 
    ,   attrValue   NVARCHAR(MAX) '$.attrValue' 
    ) color
CROSS APPLY OPENJSON(attributes)
WITH (
        attrName    NVARCHAR(MAX) '$.attrName' 
    ,   attrValue   NVARCHAR(MAX) '$.attrValue' 
    ) size

WHERE
    size.attrName   =   'Size'
AND color.attrName  =   'Color'

This works, but it feels necessarily complicated- is there a simpler way to get the results without jumping through so many hoops?

DB Fiddle here: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=b837e4e060744cddbd56c8f2f57b720a

Take a look at this to see if it helps: red-gate.com/simple-talk/blogs/… — Dharmendar Kumar 'DK'
– Dharmendar Kumar 'DK', Commented Jan 17, 2019 at 5:35

Zhorov · Accepted Answer · 2019-01-17 16:19:20Z

Use your approach with OPENJSON(), AS JSON clause and CROSS APPLY, just don't create temporary tables:

DECLARE @json nvarchar(max)
SET @json = N'{... your JSON here ...}'

SELECT
   DENSE_RANK() OVER (ORDER BY json.productId) Rn,
   json.productId Product,
   color.attrValue Color, 
   size.attrValue Size
FROM OPENJSON(@json)
WITH (
   productId nvarchar(max) '$.productId',
   itemInfo nvarchar(max) '$.itemInfo' AS JSON
) json
CROSS APPLY (
   SELECT *
   FROM OPENJSON(json.itemInfo)
   WITH (
      attributes nvarchar(max) '$.attributes' AS JSON
   )
) item
CROSS APPLY (
   SELECT *
   FROM OPENJSON(item.attributes)
   WITH (
      attrName nvarchar(max) '$.attrName',
      attrValue nvarchar(max) '$.attrValue'
   )
   WHERE attrName = 'Color'
) color
CROSS APPLY (
   SELECT *
   FROM OPENJSON(item.attributes)
   WITH (
      attrName nvarchar(max) '$.attrName',
      attrValue nvarchar(max) '$.attrValue'
   )
   WHERE attrName = 'Size'
) size

Sanpas · Accepted Answer · 2019-01-17 09:48:51Z

i think you can try (JSON PARSER WITH PIVOT SQL) like this query :

DECLARE @json   NVARCHAR(MAX) = 
'{
    "productId": "508634800",
    "description": "Generic description",
    "itemInfo": [
        {
            "sku": "4645309",
            "attributes": [
                {
                    "attrName": "Size",
                    "attrValue": "M"
                },
                {
                    "attrName": "Color",
                    "attrValue": "Burgundy"
                }
            ]
        },
        {
            "sku": "4645318",
            "attributes": [
                {
                    "attrName": "Size",
                    "attrValue": "XL"
                },
                {
                    "attrName": "Color",
                    "attrValue": "Burgundy"
                }
            ]
        },
            {
            "sku": "4645400",
            "attributes": [
                {
                    "attrName": "Size",
                    "attrValue": "L"
                },
                {
                    "attrName": "Color",
                    "attrValue": "White"
                }
            ]
        }
    ]
}'




SELECT /*sku,*/ Size, Color
FROM 
(
SELECT productId, sku, attrName, attrValue
FROM
    OPENJSON(@json)
WITH ( productId  int 'strict $.productId',  
description  NVARCHAR(MAX) '$.description',
        itemInfos   NVARCHAR(MAX) '$.itemInfo' AS JSON
    ) 
     outer apply openjson( itemInfos ) 
                     with ( sku nvarchar(8) '$.sku' ,
                            attributes  NVARCHAR(MAX) '$.attributes' AS JSON    )
                    outer apply openjson( attributes ) 
                         with ( attrName nvarchar(MAX) '$.attrName' ,
                                attrValue   NVARCHAR(MAX) '$.attrValue' )
) AS SUBJSONQUERY
PIVOT(
    MAX(SUBJSONQUERY.attrValue) FOR SUBJSONQUERY.attrName IN (Size,Color) ) AS pvtSubJsonQuery

RESULT :

Size    Color
M        Burgundy
XL       Burgundy
L        White

DB Fiddle here : https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=4bdd519517b86f9321f6b12e78eddf18

Collectives™ on Stack Overflow

SQL Server- JSON object contains array inside an array

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related