Extract nested data in JSON variable in SQL

Question

I need some help extracting the data I need out of a JSON variable in SQL:

I need to get management_account_id, Month and cost_to_client/details in a table, the problem I am facing is that the month is higher up on the hierarchy than the details section, so I'm going into each individual month to access the details object, below is the code I have:

/* management_account_id, [cost_to_client] 8 fields */

SELECT table2.ACCID, 
h3.[Management fees received],
h3.[Ad hoc invoices]
FROM OPENJSON(@WP_ACCOUNT, '$.result') j1
OUTER APPLY OPENJSON(j1.[value]) WITH (
    ACCID nvarchar(50) '$.management_account_id',
    cost_to_client nvarchar(max) '$.cost_to_client' as JSON
) table2

CROSS APPLY OPENJSON(table2.cost_to_client)
    with(
        [December 2020] nvarchar(max) as JSON,
        [January 2021] nvarchar(max) as JSON,
        [February 2021] nvarchar(max) as JSON,
        [March 2021] nvarchar(max) as JSON,
        [April 2021] nvarchar(max) as JSON,
        [May 2021] nvarchar(max) as JSON,
        [June 2021] nvarchar(max) as JSON,
        [July 2021] nvarchar(max) as JSON
    ) h1

/* Level 2: Month expanded  */

CROSS APPLY OPENJSON(h1.[December 2020])
    with(
        details nvarchar(max) as JSON
    ) h2

/* Level 3: Month/details expanded  */

CROSS APPLY OPENJSON(h2.details)
    with(
        [Management fees received] nvarchar(50),
        [Ad hoc invoices] nvarchar(50)
    ) h3
;

This is what I'm getting:

What I want instead is the below which has all the months in it:

JSON in text below:

{
    "result": {
        "12183": {
            "management_account_id": "12183",
            "label": "The Zone",
            "waste_stream_statistics": [],
            "rebates": [],
            "cost_to_client": {
                "December 2020": {
                    "value": 62432.78,
                    "details": {
                        "Management fees received": 62432.78
                    }
                },
                "January 2021": {
                    "value": 62432.78,
                    "details": {
                        "Management fees received": 62432.78
                    }
                },
                "February 2021": {
                    "value": 62432.78,
                    "details": {
                        "Management fees received": 62432.78
                    }
                },
                "March 2021": {
                    "value": 62432.78,
                    "details": {
                        "Management fees received": 62432.78
                    }
                },
                "April 2021": {
                    "value": 62432.78,
                    "details": {
                        "Management fees received": 62432.78
                    }
                },
                "May 2021": {
                    "value": 62432.78,
                    "details": {
                        "Management fees received": 62432.78
                    }
                },
                "June 2021": {
                    "value": 62432.78,
                    "details": {
                        "Management fees received": 62432.78
                    }
                },
                "July 2021": {
                    "value": 62432.78,
                    "details": {
                        "Management fees received": 62432.78
                    }
                }
            }
        }
    }   
}   

Answer 1

If I understand you correctly and you want to avoid explicit columns definitions for the $."cost_to_client" part of the input JSON, the following statement is an option:

SELECT 
   j2.ACCID,
   h1.[key] AS [Month],
   h2.[Management fees received]
FROM OPENJSON(@WP_ACCOUNT, '$.result') j1
OUTER APPLY OPENJSON(j1.[value]) WITH (
   ACCID nvarchar(50) '$.management_account_id',
   cost_to_client nvarchar(max) '$.cost_to_client' AS JSON
) j2
CROSS APPLY OPENJSON(j2.cost_to_client) h1
CROSS APPLY OPENJSON(h1.[value]) WITH (
   [value] numeric(10, 2) '$.value',
   [Management fees received] numeric(10, 2) '$.details."Management fees received"'
) h2

Result:

ACCID Month         Management fees received
--------------------------------------------
12183 December 2020 62432.78
12183 January 2021  62432.78
12183 February 2021 62432.78
12183 March 2021    62432.78
12183 April 2021    62432.78
12183 May 2021      62432.78
12183 June 2021     62432.78
12183 July 2021     62432.78