I'm trying to wrap my head around this JSON structure:
{
"key": {
"type": "SOURCE_DOCUMENT",
"label": "2021-03-04T16:31:30.424950"
},
"document": "{\"id\":08154711,\"decisionFinding\":{\"decision\":\"OK\",\"actionCode\":\"ACCEPT\",\"ruleCodeList\":[{\"ruleCode\":\"ACCEPT\",\"priority\":0,\"classification\":null,\"description\":\"comment here\"}],\"agentId\":null,\"isTest\":false,\"authentication\":false,\"additionalInfo\":[],\"automatedDecision\":\"ACCEPTED\",\"manualDecision\":null}}",
"httpStatus": 200
}
As you can see, there is a completely separate JSON structure capsulated inside the JSON field "document". I know how to work with "normal" JSON structures on SQL Server with OPENJSON
to extract any given info from fields and arrays, but extracting the capsulated JSON structure in a way that I can "feed" it into a separate OPENJSON
extraction to access the capsulated info does not work for me.
Any tips on how to approach this?
Thanks!
The problem is not OPENJSON
, or SQL Server, it is your (inner) JSON, it is invalid. If we extract your JSON from the document
node, and format it, we get the following:
{
"id": 08154711,
"decisionFinding": {
"decision": "OK",
"actionCode": "ACCEPT",
"ruleCodeList": [
{
"ruleCode": "ACCEPT",
"priority": 0,
"classification": null,
"description": "comment here"
}
],
"agentId": null,
"isTest": false,
"authentication": false,
"additionalInfo": [],
"automatedDecision": "ACCEPTED",
"manualDecision": null
}
}
"id": 08154711,
is not valid; JSON integers cannot start with a zero.
As such you need to first fix what is generating the JSON you have shown us. This means that it should be "id": "08154711"
, and when escaped \"id\":\"08154711\"
. Then you can return the document
node as an nvarchar(MAX)
and consume it. I start you off here, but I don't extract every node in your JSON, I leave that to you:
DECLARE @YourJson nvarchar(MAX) = N'{
"key": {
"type": "SOURCE_DOCUMENT",
"label": "2021-03-04T16:31:30.424950"
},
"document": "{\"id\":\"08154711\",\"decisionFinding\":{\"decision\":\"OK\",\"actionCode\":\"ACCEPT\",\"ruleCodeList\":[{\"ruleCode\":\"ACCEPT\",\"priority\":0,\"classification\":null,\"description\":\"comment here\"}],\"agentId\":null,\"isTest\":false,\"authentication\":false,\"additionalInfo\":[],\"automatedDecision\":\"ACCEPTED\",\"manualDecision\":null}}",
"httpStatus": 200
}';
SELECT k.type,
k.label,
d.id,
d.decisionFinding, --You would be extracting this, not me.
YJ.httpStatus
FROM OPENJSON(@YourJson)
WITH ([key] nvarchar(MAX) AS JSON,
document nvarchar(MAX), --Note this lacks AS JSON
httpStatus int) YJ
CROSS APPLY OPENJSON(YJ.[key])
WITH ([type] nvarchar(100),
label datetime2(6)) k
CROSS APPLY OPENJSON(YJ.document)
WITH (id int,
decisionFinding nvarchar(MAX) AS JSON) d;