Loop through JSON dataset in SQL

Question

I wish to create a temp table with two columns "time" and "air_temperature" based on a JSON dataset (see sample sql-code below).

The issue I am facing is that time and air_temperature are in different levels in the hierarchy tree where air_temperature is a child of the time level. So I need to loop through the timeseries level to find the children for each time.

In .NET I can accomplish this with a for each loop but I wish to do something similiar in MSSQL.

foreach (var data in forecast.properties.timeseries)
                {
                    insertStmt = String.Format("INSERT INTO dbo.WeatherJSON(time,air_temperature) VALUES('{0}', '{1}')",
                    data.time,
                    data.data.instant.details.air_temperature,
                    cmd = new SqlCommand(insertStmt, conn);
                    cmd.ExecuteNonQuery();
                }

DECLARE @json NVARCHAR(MAX)

SET @json='{
                "type":"Feature",
                "geometry":{
                    "type":"Point",
                    "coordinates":[13.023,55.596,10]},
                "properties":{
                    "meta":{
                        "updated_at":"2021-03-10T05:57:53Z",
                        "units":{
                            "air_pressure_at_sea_level":"hPa",
                            "air_temperature":"celsius",
                            "cloud_area_fraction":"%",
                            "precipitation_amount":"mm",
                            "relative_humidity":"%",
                            "wind_from_direction":"degrees",
                            "wind_speed":"m/s"}},
                    "timeseries":[
                        {"time":"2021-03-10T06:00:00Z",
                        "data":{
                            "instant":{
                                "details":{
                                    "air_pressure_at_sea_level":1019.9,
                                    "air_temperature":-1.9,
                                    "cloud_area_fraction":7.2,
                                    "relative_humidity":88.8,
                                    "wind_from_direction":137.3,
                                    "wind_speed":1.2}},
                                "next_12_hours":{
                                    "summary":{
                                        "symbol_code":"partlycloudy_day"}},
                                "next_1_hours":{
                                    "summary":{
                                        "symbol_code":"clearsky_day"},
                                    "details":{"precipitation_amount":0.0}},
                                "next_6_hours":{
                                    "summary":{
                                        "symbol_code":"partlycloudy_day"},
                                    "details":{"precipitation_amount":0.0}
                                    }
                                }
                            },
                    {"time":"2021-03-10T07:00:00Z",
                        "data":{
                            "instant":{
                                "details":{
                                    "air_pressure_at_sea_level":1020.3,
                                    "air_temperature":-0.3,
                                    "cloud_area_fraction":9.1,
                                    "relative_humidity":80.8,
                                    "wind_from_direction":139.9,
                                    "wind_speed":1.2}},
                                        "next_12_hours":{
                                            "summary":{
                                                "symbol_code":"partlycloudy_day"}},
                                        "next_1_hours":{
                                            "summary":{
                                                "symbol_code":"clearsky_day"},
                                            "details":{
                                                "precipitation_amount":0.0}},
                                        "next_6_hours":{
                                            "summary":{
                                                "symbol_code":"partlycloudy_day"},
                                            "details":{"precipitation_amount":0.0}
                                            }
                                        }
                                    }]
                                }
                            }'

SELECT *
FROM OPENJSON(@json)
WITH (   
                time    datetime  '$.properties.timeseries',   
                air_temperature nvarchar(50) '$.properties.timeseries.data.instant.details' 
               
              
 ) 

Answer 1

You need to use the correct path expressions, based on the structure of the parsed JSON:

SELECT *
FROM OPENJSON(@json, '$.properties.timeseries')
WITH (   
   time datetime '$.time',   
   air_temperature nvarchar(50) '$.data.instant.details.air_temperature' 
)