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
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