SQL OPENJSON - Parse JSON Array

Question

I am hoping somebody can help point me in the right direction here regarding parsing an array object out of the following JSON example. Currently, I am able to parse out the majority of the json correctly.

Where I am getting stuck is pulling out the array of parentIds into individual rows, tied to the main ID field.

Below is a sample of the JSON.

{
  "kind": "folders",
  "data": [
    {
      "id": "IEABQ5EAI4KGTYGQ",
      "accountId": "IEABQ5EA",
      "title": "test prj",
      "createdDate": "2019-04-17T20:39:58Z",
      "updatedDate": "2020-01-16T22:49:09Z",
      "description": "",
      "sharedIds": [
        "KX74YUWR",
        "KUAD47VS",
        "KX75JYTL",
        "KUAEH2GT",
        "KUAERUOG",
        "KX75EJSV",
        "KX75JYTH",
        "KUAEPBXA",
        "KX74ZXJC",
        "KUADJ7OW",
        "KX75JYTF",
        "KX75JQE2",
        "KX75JYTA",
        "KUAC6PRX",
        "KUAD2ZGS",
        "KUADJYXY",
        "KX75JYS5",
        "KUADQEMC",
        "KUADJXDI",
        "KUADAEPD",
        "KX732DQC",
        "KUACJOOM"
      ],
      "parentIds": [
        "IEABQ5EAI4KGTXVG"
      ],
      "childIds": [],
      "scope": "WsFolder",
      "permalink": "www.test.com",
      "workflowId": "IEABQ5EAK776PC4A",
      "customFields": [],
      "customColumnIds": [],
      "project": {
        "authorId": "KUADJXDI",
        "ownerIds": [
          "KUADJXDI"
        ],
        "status": "Completed",
        "customStatusId": "IEABQ5EAJMA6STFV",
        "startDate": "2019-05-29",
        "endDate": "2019-06-18",
        "createdDate": "2019-05-31T04:56:58Z",
        "completedDate": "2020-01-16T22:49:09Z"
      }
    }
  ]
}

The below SQL code is what I am using to parse the main fields out --

SELECT [key] AS Doc_id, id, title, createdDate,  updatedDate, [description], permalink, workflowId,
parentIds, customFields, customColumnIds, project
into #JsonDocTemp
FROM OPENJSON (@WJSON2, '$.data') AS EachDoc
  CROSS APPLY OPENJSON(EachDoc.Value)
     WITH (
             id NVARCHAR(20) N'$.id',
             title NVARCHAR(500) N'$.title',
             createdDate DATETIME '$.createdDate',
             updatedDate DATETIME '$.updatedDate',
             [description] NVARCHAR(MAX) N'$.description',
             permalink NVARCHAR(100) N'$.permalink',
             workflowId NVARCHAR(20) N'$.workflowId',
             parentIds NVARCHAR(MAX) N'$.parentIds' AS JSON,
             customFields NVARCHAR(MAX) N'$.customFields' AS JSON,
             customColumnIds NVARCHAR(MAX) N'$.customColumnIds' AS JSON,
             project NVARCHAR(MAX) N'$.parentIds' AS JSON
        )

I would like to parse out the parentIds from the temp table into rows or directly from @WJSON2 using .data.parentId's would also work.

Any help - I think I am almost there but I am missing something.

Thank you Jeremy

Zhorov · Accepted Answer · 2020-01-20T09:46:52.663

You need to use an additonal APPLY operator and OPENJSON() call with explicit schema for each nested level:

JSON:

DECLARE @json nvarchar(max) = N'{
   "kind":"folders",
   "data":[
      {
         "id":"IEABQ5EAI4KGTYGQ",
         "accountId":"IEABQ5EA",
         "title":"test prj",
         "createdDate":"2019-04-17T20:39:58Z",
         "updatedDate":"2020-01-16T22:49:09Z",
         "description":"",
         "sharedIds":[
            "KX74YUWR",
            "KUAD47VS",
            "KX75JYTL",
            "KUAEH2GT",
            "KUAERUOG",
            "KX75EJSV",
            "KX75JYTH",
            "KUAEPBXA",
            "KX74ZXJC",
            "KUADJ7OW",
            "KX75JYTF",
            "KX75JQE2",
            "KX75JYTA",
            "KUAC6PRX",
            "KUAD2ZGS",
            "KUADJYXY",
            "KX75JYS5",
            "KUADQEMC",
            "KUADJXDI",
            "KUADAEPD",
            "KX732DQC",
            "KUACJOOM"
         ],
         "parentIds":[
            "IEABQ5EAI4KGTXVG"
         ],
         "childIds":[

         ],
         "scope":"WsFolder",
         "permalink":"www.test.com",
         "workflowId":"IEABQ5EAK776PC4A",
         "customFields":[

         ],
         "customColumnIds":[

         ],
         "project":{
            "authorId":"KUADJXDI",
            "ownerIds":[
               "KUADJXDI"
            ],
            "status":"Completed",
            "customStatusId":"IEABQ5EAJMA6STFV",
            "startDate":"2019-05-29",
            "endDate":"2019-06-18",
            "createdDate":"2019-05-31T04:56:58Z",
            "completedDate":"2020-01-16T22:49:09Z"
         }
      }
   ]
}'

Statement (returns only id and parentIds from each JSON level):

SELECT j1.id, j2.parentId
FROM OPENJSON (@json, '$.data') WITH (
   id nvarchar(50) '$.id',
   parentIds nvarchar(max) '$.parentIds' AS JSON
) j1
OUTER APPLY OPENJSON(j1.parentIds) WITH (
   parentId nvarchar(50) '$'
) j2

Result:

id                  parentId
IEABQ5EAI4KGTYGQ    IEABQ5EAI4KGTXVG

Statement (parses additional JSON keys and nested arrays):

SELECT 
   j1.kind, 
   j2.id, j2.accountId, j2.title, j2.createdDate, j2.updatedDate, j2.description,
   j3.sharedId, j4.parentId, j5.childId,
   j2.scope, j2.permalink, j2.workflowId,
   j6.customField, j7.customColumnId
FROM OPENJSON (@json, '$') WITH (
   kind nvarchar(50) '$.kind',
   data nvarchar(max) '$.data' AS JSON
)j1
OUTER APPLY OPENJSON(j1.data) WITH (
   id nvarchar(50) '$.id',
   accountId nvarchar(50) '$.accountId',
   title nvarchar(50) '$.title',
   createdDate nvarchar(50) '$.createdDate',
   updatedDate nvarchar(50) '$.updatedDate',
   description nvarchar(50) '$.description',
   sharedIds nvarchar(max) '$.sharedIds' AS JSON,
   parentIds nvarchar(max) '$.parentIds' AS JSON,
   childIds nvarchar(max) '$.childIds' AS JSON,
   scope nvarchar(50) '$.scope',
   permalink nvarchar(50) '$.permalink',
   workflowId nvarchar(100) '$.workflowId',
   customFields nvarchar(max) '$.customFields' AS JSON,
   customColumnIds nvarchar(max) '$.customColumnIds' AS JSON
) j2
OUTER APPLY OPENJSON(j2.sharedIds) WITH (
   sharedId nvarchar(50) '$'
) j3
OUTER APPLY OPENJSON(j2.parentIds) WITH (
   parentId nvarchar(50) '$'
) j4
OUTER APPLY OPENJSON(j2.childIds) WITH (
   childId nvarchar(50) '$'
) j5
OUTER APPLY OPENJSON(j2.customFields) WITH (
   customField nvarchar(50) '$'
) j6
OUTER APPLY OPENJSON(j2.customColumnIds) WITH (
   customColumnId nvarchar(50) '$'
) j7

Useful info on how to parse a JSON array of values (in this case `sharedIds`) via `OPENJSON` with a `WITH` clause. Thanks. — Simon Elms, Jul 24 '23 at 11:10

SQL OPENJSON - Parse JSON Array

1 Answers1