Querying JSON Array in SQL

Question

I am trying to query a JSON file that's nested in an array, in SQL Server. Below is a snippet of the JSON:

[
    {
        "data_json": [
            {
                "site": "Test Western Cape DC",
                "year": 2021,
                "month": "2019-01-01 - 2019-01-31",
                "total_kWh": {
                    "grid_electricity": 845106.2790000001,
                    "pv_electricity": 234122.22499999998
                }
            }
        ]
    },
    {
        "data_json": [
            {
                "site": "Test Western Cape DC",
                "year": 2021,
                "month": "2019-02-01 - 2019-02-28",
                "total_kWh": {
                    "grid_electricity": 796965.3940000001,
                    "pv_electricity": 169540.028
                }
            }
        ]
    }
]

I would like to get the end result of two rows with columns for Site, Month, Grid Electricity and PV Electricity.

I was able to get the desired column output if I remove the square brackets, but I would like to leave the file as is and my results also only included the first row:

DECLARE @WP_ACCOUNT VARCHAR(MAX)

SELECT @WP_ACCOUNT =
    BulkColumn
    FROM OPENROWSET(BULK'C:\Users\taariq\Desktop\JSON\19-276.json', SINGLE_BLOB) JSON

    SELECT j2.SITEDESC, j2.MONTHID, h1.ELECGRID, h1.ELECPV
    FROM OPENJSON(@WP_ACCOUNT, '$.data_json') j1
    OUTER APPLY OPENJSON(j1.[value]) WITH (
        SITEDESC nvarchar(50) '$.site',
        MONTHID nvarchar(50) '$.month',
        total_kWh nvarchar(max) '$.total_kWh' AS JSON
    ) j2
    CROSS APPLY OPENJSON(j2.total_kWh) WITH (
        ELECGRID nvarchar(50) '$.grid_electricity',
        ELECPV nvarchar(50) '$.pv_electricity'
    ) h1

Thom A · Accepted Answer · 2021-09-02 13:48:35Z

4

You weren't far off. One way would be the following. I use JSON_VALUE for the last 2 columns to demonstrate it's use, but you could use another call to OPENJSON in a CROSS APPLY if you preferred:

DECLARE @JSON nvarchar(MAX) = N'[
    {
        "data_json": [
            {
                "site": "Test Western Cape DC",
                "year": 2021,
                "month": "2019-01-01 - 2019-01-31",
                "total_kWh": {
                    "grid_electricity": 845106.2790000001,
                    "pv_electricity": 234122.22499999998
                }
            }
        ]
    },
    {
        "data_json": [
            {
                "site": "Test Western Cape DC",
                "year": 2021,
                "month": "2019-02-01 - 2019-02-28",
                "total_kWh": {
                    "grid_electricity": 796965.3940000001,
                    "pv_electricity": 169540.028
                }
            }
        ]
    }
]'

SELECT dj.site,
       dj.year,
       dj.month,
       JSON_VALUE(dj.total_kWh,'$.grid_electricity') AS ELECGRID,
       JSON_VALUE(dj.total_kWh,'$.pv_electricity') AS ELECPV
FROM OPENJSON(@JSON)
     WITH (data_json nvarchar(MAX) AS JSON) J
     CROSS APPLY OPENJSON(J.data_json)
                 WITH(site nvarchar(50),
                      year int,
                      month nvarchar(50),
                      total_kWh nvarchar(MAX) AS JSON) dj;

answered Sep 2, 2021 at 13:48

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Charlieface Over a year ago

Alternatively, you can specify a JSON path in the second OPENJSON (no need for a third) grid_electricity decimal '$.total_kWh.grid_electricity', pv_electricity decimal '$.total_kWh.pv_electricity') dj

Collectives™ on Stack Overflow

Querying JSON Array in SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related