Parse Nested JSON into SQL Table

Question

I am trying to get a JSON file parsed into a usable format so I can insert it into a SQL table.

The JSON file I have is heavily nested (and I can't get the vendor to change it at this point), and uses the same name at different levels.

I have used the following code, to start off, but it is the multi sections and potentially multiple accounts etc that has me stumped. I know I will probably need to iterate through somehow, but just not sure where to begin.

DECLARE @JSON VARCHAR(MAX)

SELECT @JSON = BulkColumn
FROM OPENROWSET 
(BULK 'C:\Users\joshu\Downloads\Cashflow.JSON', SINGLE_CLOB) 
AS j

If (ISJSON(@JSON)=1)
Select * FROM OPENJSON (@JSON,'$.data')
with
(
[id] nvarchar(50),
[title] nvarchar(50),
[sections] nvarchar(max) as json
) data_Structure

cross apply openjson(data_structure.sections, '$')

with (
[income] nvarchar(max) as json
) data2
--Income is one type, there should be a loop here

cross apply openjson(data2.income, '$')

Which is getting my down the tree. The last data points that I want to collect are based on this bit of code

DECLARE @JSON VARCHAR(MAX)
 
SELECT @JSON = BulkColumn
FROM OPENROWSET 
(BULK 'C:\Users\joshu\Downloads\Cashflow.JSON', SINGLE_CLOB) 
AS j
 
If (ISJSON(@JSON)=1)
Select * FROM OPENJSON (@JSON,'$.data.sections.income.sections.tracker_1193.sections.tracker_1193_income.rows."b5cfd1ce-bb7f-4f5c-a6b4-12b469ff0b9d".data."2017-06"') 
with
(
[date] nvarchar(50),
[value] decimal(18,2))

A sample of the JSON is here

        "data": {
        "id": "cashflow",
        "title": "Cashflow Report",
        "sections": {
            "income": {
                "id": "income",
                "title": "Income",
                "sections": {
                    "tracker_1193": {
                        "id": "tracker_1193",
                        "title": "xxxxxxx",
                        "sections": {
                            "tracker_1193_income": {
                                "id": "tracker_1193_income",
                                "title": "Income",
                                "sections": null,
                                "rows": {
                                    "b5cfd1ce-bb7f-4f5c-a6b4-12b469ff0b9d": {
                                        "account_id": "b5cfd1ce-bb7f-4f5c-a6b4-12b469ff0b9d",
                                        "account_name": "Bobby Calf Sales",
                                        "data": {
                                            "2017-06": {
                                                "date": "2017-06",
                                                "value": 0
                                            },
                                            "2017-09": {
                                                "date": "2017-09",
                                                "value": 4801.36
                                            },
                                            "2017-12": {
                                                "date": "2017-12",
                                                "value": 1997.33

Now the fun part.

• The income section is the most complicated;
• The First "section" in the JSON data is one of 13.
• The Second "section" is variable, as in there could be 1, or 12.
• The Third "section is fixed to 3 (income, costs, gross-profit)

The rest is more straight forward

{
    "data": {
        "id": "cashflow",
        "title": "Cashflow Report",
        "sections": {

//other data ahead of this
                      "operating_expenses": {
                "id": "operating_expenses",
                "title": "Operating Expenses",
                "sections": {
                    "operating_expenses_animal_health_animal": {
                        "id": "operating_expenses_animal_health_animal",
                        "title": "Animal Health",
                        "sections": null,
                        "rows": {
                            "0de82545-be93-4fb5-9d20-fa076af48e40": {
                                "account_id": "0de82545-be93-4fb5-9d20-fa076af48e40",
                                "account_name": "Animal Health - Minerals",
                                "data": {
                                    "2019-07": {
                                        "date": "2019-07",
                                        "value": 5827.93
                                    }
                                }
                            },
                            "9ba329a6-f77e-4779-9d79-28dd20465b9c": {
                                "account_id": "9ba329a6-f77e-4779-9d79-28dd20465b9c",
                                "account_name": "Animal Health - Other",
                                "data": {
                                    "2019-07": {
                                        "date": "2019-07",
                                        "value": 663.73
                                    }
                                }
                            },
                            "4f406965-3355-4968-a5ba-519d9706f329": {
                                "account_id": "4f406965-3355-4968-a5ba-519d9706f329",
                                "account_name": "Animal Health - Treatments",
                                "data": {
                                    "2019-07": {
                                        "date": "2019-07",
                                        "value": 8670.1
                                    }
                                }
                            },
                            "79c8ab89-22a2-4c5c-b591-0a3d95a4a95b": {
                                "account_id": "79c8ab89-22a2-4c5c-b591-0a3d95a4a95b",
                                "account_name": "Animal Health - Vet",
                                "data": {
                                    "2019-07": {
                                        "date": "2019-07",
                                        "value": 7645.18
                                    }
                                }
                            }
                        },
                        "totals": {
                            "2019-07": {
                                "date": "2019-07",
                                "value": 22806.94
                            }
                        }
                    },
                    

Because of the nature of this data, I haven't sorted my SQL table structure yet, but I am imagining it to be something along the lines of the below:

That's about it. I am stuck, need some help/guidance so anything you can do to assist is greatly appreciated

Answer 1

I've made an attempt with your JSON to traverse its data without having to define explicit keys. This only handles the "Income" portion, however, it should get you moving in the right direction to extract your data into SQL server. Note that given "rows" can have multiple values, some data is duplicated.

DECLARE @data nvarchar(MAX) = '{"data":{"id":"cashflow","title":"Cashflow Report","sections":{"income":{"id":"income","title":"Income","sections":{"tracker_1193":{"id":"tracker_1193","title":"xxxxxxx","sections":{"tracker_1193_income":{"id":"tracker_1193_income","title":"Income","sections":null,"rows":{"b5cfd1ce-bb7f-4f5c-a6b4-12b469ff0b9d":{"account_id":"b5cfd1ce-bb7f-4f5c-a6b4-12b469ff0b9d","account_name":"Bobby Calf Sales","data":{"2017-06":{"date":"2017-06","value":0},"2017-09":{"date":"2017-09","value":4801.36},"2017-12":{"date":"2017-12","value":1997.33}}}}}}}}}}}}';

SELECT
    section_id,
    section_title,
    tracker_id,
    tracker_title,
    string_id,
    string_title,
    account_id,
    account_title,
    [period],
    period_value
FROM OPENJSON ( @data, '$.data.sections.income' ) 
WITH (
    section_id varchar(50) '$.id',
    section_title varchar(50) '$.title',
    sections nvarchar(max) '$.sections' AS JSON
) AS dat
OUTER APPLY (

    SELECT * FROM OPENJSON ( dat.sections ) AS a
    CROSS APPLY (

        SELECT * FROM OPENJSON ( a.value )
        WITH (
            tracker_id varchar(50) '$.id',
            tracker_title varchar(50) '$.title',
            tracker_income nvarchar(max) '$.sections' AS json
        )

    ) AS b
    CROSS APPLY (
        SELECT [key] AS income_key, [value] AS income_value FROM OPENJSON ( b.tracker_income )
    ) AS c
    CROSS APPLY (

        SELECT * FROM OPENJSON ( c.income_value )
        WITH (
            string_id varchar(50) '$.id',
            string_title varchar(50) '$.title',
            income_rows nvarchar(max) '$.rows' AS json
        )

    ) AS d
    CROSS APPLY (

        SELECT [key] AS account_key, [value] AS account_value FROM OPENJSON ( d.income_rows )

    ) e
    CROSS APPLY (

        SELECT * FROM OPENJSON ( e.account_value )
        WITH (
            account_id varchar(255) '$.account_id',
            account_title varchar(50) '$.account_name',
            account_data nvarchar(max) '$.data' AS json
        )

    ) f
    CROSS APPLY (
        SELECT [key] AS [period], JSON_VALUE ( [value], '$.value' ) AS period_value FROM OPENJSON ( f.account_data )
    ) g

) AS Income;

Returns

+------------+---------------+--------------+---------------+---------------------+--------------+--------------------------------------+------------------+---------+--------------+
| section_id | section_title |  tracker_id  | tracker_title |      string_id      | string_title |              account_id              |  account_title   | period  | period_value |
+------------+---------------+--------------+---------------+---------------------+--------------+--------------------------------------+------------------+---------+--------------+
| income     | Income        | tracker_1193 | xxxxxxx       | tracker_1193_income | Income       | b5cfd1ce-bb7f-4f5c-a6b4-12b469ff0b9d | Bobby Calf Sales | 2017-06 | 0            |
| income     | Income        | tracker_1193 | xxxxxxx       | tracker_1193_income | Income       | b5cfd1ce-bb7f-4f5c-a6b4-12b469ff0b9d | Bobby Calf Sales | 2017-09 | 4801.36      |
| income     | Income        | tracker_1193 | xxxxxxx       | tracker_1193_income | Income       | b5cfd1ce-bb7f-4f5c-a6b4-12b469ff0b9d | Bobby Calf Sales | 2017-12 | 1997.33      |
+------------+---------------+--------------+---------------+---------------------+--------------+--------------------------------------+------------------+---------+--------------+