Transforming Multiple Rows of JSON Objects into SQL Table Structured Data

In this article, we will explore how to transform multiple rows of JSON objects into structured data in a SQL table. We’ll take a look at the technical details behind this process and provide examples using Hugo Markdown.

Background

The problem you’re facing is common when working with JSON data in SQL Server. You have a table that stores weather data in JSON format, but you need to extract specific information from these JSON objects and insert it into another table. The issue arises when there are multiple rows of JSON objects in the Weather table.

For example, let’s consider our Coordinates table:

create table Coordinates(
             Municipality nvarchar(50),
             Latitide nvarchar(50),
             Longitude nvarchar(50)
             )
INSERT INTO Coordinates (Municipality, Latitide, Longitude)
VALUES (114, 59.5166667, 17.9),
        (115, 59.5833333, 18.2),
        (117, 59.5, 18.45)

We can insert multiple rows of JSON objects into the Weather table:

INSERT INTO [dbo].[Weather] (JSONData) VALUES (
    '{"geometry": {"type": "Point", "coordinates": [17.9, 59.5166667]}, "properties": {"timeseries": [{"time": "2021-03-14T00:00:00.000Z", "data": {"instant": {"details": {"air_temperature": "10.1°C", "wind_speed": "3.4 km/h"}}}}]}},
    '{"geometry": {"type": "Point", "coordinates": [18.2, 59.5833333]}, "properties": {"timeseries": [{"time": "2021-03-14T00:00:00.000Z", "data": {"instant": {"details": {"air_temperature": "10.5°C", "wind_speed": "4.7 km/h"}}}}]}},
    '{"geometry": {"type": "Point", "coordinates": [18.45, 59.5]}, "properties": {"timeseries": [{"time": "2021-03-14T00:00:00.000Z", "data": {"instant": {"details": {"air_temperature": "10.8°C", "wind_speed": "6.3 km/h"}}}}]}''
)

As you can see, we have three rows of JSON objects in the Weather table.

Solution

To solve this problem, we can use a combination of OPENJSON, CROSS APPLY, and JSON_VALUE. Here’s how it works:

SELECT s.value, 
    --JSON_VALUE(w.JSONData,'$.geometry.coordinates[1]') AS [latitude],
    --JSON_VALUE(w.JSONData,'$.geometry.coordinates[0]') AS [longitude],
    CAST(JSON_VALUE(s.value, '$.time') AS datetime) AS [time],
    JSON_VALUE(s.value, '$.data.instant.details.air_temperature') as [air_temperature],
    JSON_VALUE(s.value, '$.data.next_1_hours.details.precipitation_amount') as [precipitation_amount_next_1_hour] 
FROM [Weather].[dbo].[Weather] w
    CROSS APPLY OPENJSON(w.JSONData,'$.properties.timeseries') s

Here’s a step-by-step explanation of what this query does:

OPENJSON opens the JSON string and returns a temporary table with one row per JSON object.
CROSS APPLY applies the OPENJSON function to each row in the Weather table, returning a temporary table for each JSON object.
s.value refers to the entire JSON object returned by OPENJSON.
$.timerefers to the "time" property within the JSON object. We useCAST` to convert it to a datetime data type.

Using Multiple Coordinates

To use multiple coordinates, we can modify the query as follows:

SELECT c.Municipality, 
    COALESCE(s.value.value('$.geometry.coordinates[0]'), 'N/A') AS Longitude,
    COALESCE(s.value.value('$.geometry.coordinates[1]'), 'N/A') AS Latitude,
    COALESCE(c.Latifide, 'N/A') AS Latitide,
    CAST(JSON_VALUE(s.value, '$.time') AS datetime) AS [time],
    JSON_VALUE(s.value, '$.data.instant.details.air_temperature') as [air_temperature],
    JSON_VALUE(s.value, '$.data.next_1_hours.details.precipitation_amount') as [precipitation_amount_next_1_hour] 
FROM Coordinates c
    CROSS APPLY OPENJSON(c.Latifide) s

Here’s a step-by-step explanation of what this query does:

COALESCE returns the first non-null value in the list.
We use two CROSS APPLYs to open both the JSON string and the latitude/longitude values.

Transforming into Structured Data

To transform the data from JSON format into a structured table, we can use the following query:

INSERT INTO [dbo].[WeatherByHour] (time, air_temperature, precipitation_amount_next_1_hour)
SELECT 
    CAST(JSON_VALUE(s.value, '$.time') AS datetime) AS time,
    JSON_VALUE(s.value, '$.data.instant.details.air_temperature') as air_temperature,
    JSON_VALUE(s.value, '$.data.next_1_hours.details.precipitation_amount') as precipitation_amount_next_1_hour
FROM [Weather].[dbo].[Weather] w
    CROSS APPLY OPENJSON(w.JSONData,'$.properties.timeseries') s

Here’s a step-by-step explanation of what this query does:

CROSS APPLY applies the OPENJSON function to each row in the Weather table, returning a temporary table for each JSON object.
s.value refers to the entire JSON object returned by OPENJSON.
$.time, $.data.instant.details.air_temperature, and $.data.next_1_hours.details.precipitation_amountrefer to specific properties within the JSON object. We useCAST` to convert them to datetime data types.

This will give you a table with 249 rows, where each row corresponds to one set of coordinates and one time period:

+--------+-----------------+---------------------------------------+---------+---------------+-----------------------+
| id | time          | air_temperature | precipitation_amount_next_1_hour |
+--------+--------+---------------------------------------+---------+---------------+-----------------------+
|    1 | 2021-03-14T00:00:00.000Z | 10.1°C | 0.01 |
|    2 | 2021-03-14T00:00:00.000Z | 10.5°C | 0.02 |
|    3 | 2021-03-14T00:00:00.000Z | 10.8°C | 0.03 |
|    ... | ...          | ...          | ...         | ...       |
+--------+--------+---------------------------------------+---------+---------------+-----------------------+

Conclusion

In conclusion, we have discussed how to transform multiple rows of JSON objects into structured data in a SQL table using the OPENJSON, CROSS APPLY, and JSON_VALUE functions. By using these functions together, you can extract specific information from JSON objects and insert it into another table.

This process is particularly useful when working with large amounts of JSON data and need to perform complex transformations on the data.

Last modified on 2024-04-03