jsonsql-serversql-server-2017sql-server-json

Can't parsing an array of objects with Dynamic Keys using OPENJSON And SQL Server 2017


I'm after running into some trouble parsing a set of JSON documents using SQL Server 2017.

I've encountered a nested array (sample below) within a document which uses dynamic ObjectId's i.e. 123 as the object Key as opposed to using a static key i.e. Category and then having a separate key:value to ref the ObjectId.

As a result I can't extract the items to a table using the regular CROSS APPLY OPENJSON syntax without specifying each individual Object Id (there are thousands)?

Is there a way to do this without referencing each ObjectId explicitly ideally i'd like to return all product items in a table and just have a field withe the categoryId.

 "ProductItems": {
            "123": [
              {
                "item": "13663"
              }
            ]
            "124": [
              {
                "value": "2336"
              },
              {
                "value": "3667"
              }             
            ],
            "453": [
              {
                "value": "8667"
              },
              {
                "value": "1956"
              }
            ]
          }

Solution

  • Try something like this:

    DECLARE @x NVARCHAR(MAX)=
    '{"ProductItems":{
    "123": [
        {
        "item": "13663"
        }
    ],
    "124": [
        {
        "value": "2336"
        },
        {
        "value": "3667"
        }             
    ],
    "453": [
        {
        "value": "8667"
        },
        {
        "value": "1956"
        }
    ]
    }}'
    
    SELECT j2.*, j3.* 
    FROM OPENJSON(@x) j1
    CROSS APPLY OPENJSON(j1.Value) j2
    CROSS APPLY OPENJSON(j2.Value) j3