Search code examples
sqljsonsql-serveropen-json

SQL OPENJSON - Parse JSON Array


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


Solution

  • 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