Did you know that starting in SQL Server 2016, JSON was able in the database? That you were able to store and query JSON data just like a NoSQL database? So let’s talk about this and see some examples on how to do this with Azure SQL.

Just a quick note, while what this post is going to go over is an introduction, please follow or drop a line to Umachandar Jayachandran (UC), a fellow PM here at Microsoft in the Azure SQL team. He is the master of JSON in the database. And on a personal note, he helped me find where to buy the Xbox Series X Mini Fridge when I started at Microsoft a few months back.

Again, I am going to use Azure Data Studio for all my work.

Create A Table

First things first; we need to create a table to store our JSON data in an Azure SQL Database. Let’s imagine a scenario where our JSON is coming in from an IoT device that measures temperature, wind speed, wind direction, and humidity. The table we are going to create will be called weather_info and have a primary key of id, a station id and the JSON from the station itself with the above information.

CREATE TABLE [dbo].[weather_info] ( 
  id int identity not null primary key,
  weather_station_id int not null,
  weather_details nvarchar(max) NOT NULL CHECK (ISJSON (weather_details) = 1)
);

So what’s going on here? The table starts off with two pretty normal columns; the ID set as the primary key and identity (which will auto generate a sequence number for us) and the ID of the weather station from which this JSON data belongs to.

The next column, weather_details, is where the magic happens. We are storing the JSON as a nvarchar but we add a constraint with the function ISJSON() = 1. The ISJSON function will test the inserted data to ensure it’s a valid JSON object or array. If not valid, you will get an error:

12:08:37 PM
Started executing query at Line 9
Msg 13609, Level 16, State 4, Line 16
JSON text is not properly formatted. 

Load the JSON

Next, we have to load some data into the table. For this first example, we will not be using Azure Blob Storage but just pass the JSON in as a variable.

The format of the JSON payload is as follows:

{
    "weatherStationId": "2112",
    "date": "2022-08-08T12:00:00Z",
    "temperature": "80",
    "temperature_unit": "Fahrenheit",
    "wind_speed": "25",
    "wind_speed_units": "mph",
    "wind_direction": "NE",
    "humidity": "60"
}

And using this JSON, we can load it into our table:

DECLARE @json_weather nvarchar(1000) = N'
{
    "weatherStationId": "2112",
    "date": "2022-08-08T12:00:00Z",
    "temperature": "80",
    "temperature_unit": "Fahrenheit",
    "wind_speed": "25",
    "wind_speed_units": "mph",
    "wind_direction": "NE",
    "humidity": "60"
}
';

insert into [dbo].[weather_info] (weather_station_id, weather_details)
    select t.weatherStationId, @json_weather 
    from OPENJSON(@json_weather)
        with (
                weatherStationId int '$.weatherStationId'
        ) as t;

and if we select from the table

select * from [dbo].[weather_info];

We can see our data

Looking more closely at the insert statement, it starts out like any other insert statement

insert into [dbo].[weather_info] (weather_station_id, weather_details)

And we all have written inserts as selects as well, nothing crazy here

insert into [dbo].[weather_info] (weather_station_id, weather_details)
    select t.weatherStationId, @json_weather

Now we are passing the variable @json_weather directly into the weather_details column. If its valid JSON, it will be inserted with no problems. But the next section is where we can parse the JSON and insert individual values from the JSON and flatten out the JSON into a relational table if need be.

In this insert, we are pulling out the weather station ID and using that value for the weather_station_id column while storing the rest of the payload as a JSON document.

    from OPENJSON(@json_weather)
        with (
                weatherStationId int '$.weatherStationId'
        ) as t;

If we wanted to, we could just store the individual values into a table and not store the JSON at all:

CREATE TABLE [dbo].[more_weather_info] ( 
  id int IDENTITY NOT NULL PRIMARY KEY,
  weather_station_id int not null,
  weather_date date,
  temperture int,
  temperture_unit varchar(15),
  wind_speed int,
  wind_speed_units varchar(5),
  wind_direction varchar(5),
  humidity int
);

DECLARE @json_weather nvarchar(1000) = N'
{
    "weatherStationId": "2112",
    "date": "2022-08-08T12:00:00Z",
    "temperature": "80",
    "temperature_unit": "Fahrenheit",
    "wind_speed": "25",
    "wind_speed_units": "mph",
    "wind_direction": "NE",
    "humidity": "60"
}
';

insert into [dbo].[more_weather_info] (weather_station_id, weather_date, temperture, temperture_unit, wind_speed, wind_speed_units, wind_direction, humidity)
    select t.weatherStationId, t.weather_date, t.temperture, t.temperture_unit, t.wind_speed, t.wind_speed_units, t.wind_direction, t.humidity
    from OPENJSON(@json_weather)
        with (
                weatherStationId int '$.weatherStationId',
                weather_date date '$.date',
                temperture int '$.temperature',
                temperture_unit varchar(15) '$.temperature_unit',
                wind_speed int '$.wind_speed',
                wind_speed_units varchar(5) '$.wind_speed_units',
                wind_direction varchar(5) '$.wind_direction',
                humidity int '$.humidity'                                                                                                      
        ) as t;

And a select from the table show the data

select * from [dbo].[more_weather_info];

Working with JSON in a Table

Back to the weather_info table with the JSON column, we can extract the values out much like we did in the previous section when we converted the JSON document into a row. We use the function JSON_VALUE to select the values we want from the JSON in the weather_details column.

select id, JSON_VALUE(weather_details,'$.weatherStationId') [station_id], 
    CAST(JSON_VALUE(weather_details,'$.date') as datetime) [date] 
from [dbo].[weather_info]

You can also use JSON_VALUE in where clauses as well

select count(*) [total]
from [dbo].[weather_info]
where JSON_VALUE(weather_details, '$.temperature') = 80;

Going back to the more_weather_info table that we converted from JSON to a row, we can convert it back to JSON simply with the FOR JSON AUTO/PATH clause

select * from more_weather_info FOR JSON AUTO;

with the output being

[
    {
        "id": 1,
        "weather_station_id": 2112,
        "weather_date": "2022-08-08",
        "temperture": 80,
        "temperture_unit": "Fahrenheit",
        "wind_speed": 25,
        "wind_speed_units": "mph",
        "wind_direction": "NE",
        "humidity": 60
    }
]

If we need to modify a value in a JSON document in a column of our table, we can use the JSON_MODIFY function. The function takes in the column, the element, and the new value you want to set it to as you can see in the following statement

update [dbo].[weather_info]
set weather_details=JSON_MODIFY(weather_details,'$.temperature',81)
where JSON_VALUE(weather_details, '$.weatherStationId') = 2112;

We are setting the temperature to 81 where the weather station ID is 2112. We can check the update with the following query

select id, JSON_VALUE(weather_details,'$.temperature') [temperature]
from [dbo].[weather_info]
where JSON_VALUE(weather_details, '$.weatherStationId') = 2112;

Extra Credit – Loading JSON from Azure Blob Storage

Bonus round!

This section expands on the previous post about loading data so we need to have some prerequisites done; an External Datasource created as well as a container in Azure Blob Storage. You can go step by step in the Loading data into Azure SQL (with Azure Data Studio and Azure Blob Storage) post and ill put the code for reference here:

-- Create Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SuperSecretPassword';

-- Create Scoped Credential
CREATE DATABASE SCOPED CREDENTIAL AzureBlobStorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'SharedAccesstoken';

-- Create External Data Source
CREATE EXTERNAL DATA SOURCE ExternalAzureBlobStorage
WITH (	TYPE = BLOB_STORAGE, 
	LOCATION = 'https://STORAGE_ACCOUNT_NAME.blob.core.windows.net/CONTAINER_NAME',
        CREDENTIAL = AzureBlobStorageCredential);

Next, we can load JSON data from a container in Azure Blob Storage directly into a table. Maybe that same IoT weather station was streaming this JSON data directly into a container and we needed to load it. Just like we did in a previous post with CSV files, we can load JSON files.

(Grab the weather-2.json file here)

INSERT INTO [dbo].[weather_info] (weather_station_id, weather_details) 
    select JSON_VALUE(t.BulkColumn, '$.weatherStationId'), * 
        FROM OPENROWSET(
                        BULK 'weather-2.json',
                        DATA_SOURCE = 'ExternalAzureBlobStorage',
                        SINGLE_CLOB
         ) as t

and checking our work shows us

select * from [dbo].[weather_info];

It really is easy to work with JSON data in Azure SQL Database and this post just scratches the surface of what you can do. You can look at the documentation here for more on the functions and more on working with JSON in the database here.