jsonsql-server

Work with complete JSON objects inside JSON fields


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!


Solution

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