SQL Server and JSON by Martin Catherall

This article first appeared in the SQLServerGeeks Magazine.
Author: Martin Catherall
Subscribe to get your copy.

It was a huge honour to be asked to write a piece for DataPlatformGeeks – and I pondered a subject for a while.

Over the years SQL Server has introduced several pieces of functionality that have really benefited users – with the vast majority having good uptake. One of the things that I love about Microsoft products is that when they see their users actively using a feature, they give that feature a bit of extra care and attention based on feedback from those users.

One of the recent features that I’ve found myself using more times that I initially suspected is JSON – or JavaScript Object Notation, to give it it’s full name.

The ability to manipulate JSON inside SQL Server first appeared in the 2016 release. As you are probably already aware, JSON is a open standard file format for data interchange.

My initial thoughts on this was that it might be similar to the XML integration that SQL Server has had since 2005 – although that’s pretty robust, it can take a lot of learning.

That hasn’t been my exp experience with JSON. While there was obviously some learning involved I thought that it was quite intuitive and – if you have experience with any other JSON parser – very easy to get started.

Let’s dive in and have a look.

Firstly – and perhaps initially surprisingly, there is no JSON data type in SQL Server. It’s just a plain old text string.

However, SQL does give us a method of checking that the string is valid JSON.

SELECT ISJSON(N'
{
"configuration_id": 101,
"name": "recovery interval (min)",
"value": 0,
"minimum": 0,
"maximum": 32767,
"value_in_use": 0,
"description": "Maximum recovery interval in minutes",
"is_dynamic": true,
"is_advanced": true
}
') AS [Is Valid Json];
GO
SELECT ISJSON(N'I love data platform geeks') AS [Is Valid Json];
GO

This means that if we decide to store JSON in a column we can introduce a column constraint to ensure that the text we are inserting is in fact valid JSON

So, let’s create a database to play with, along with a table that has a column to store valid JSON

USE tempdb;
GO

IF EXISTS (SELECT 1 FROM sys.databases AS dbs WHERE dbs.[name] = 'JSONForSQLFolks')
BEGIN
ALTER DATABASE JSONForSQLFolks SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE JSONForSQLFolks;
END
GO
CREATE DATABASE JSONForSQLFolks;
GO
USE JSONForSQLFolks;
GO
CREATE TABLE dbo.JdataWithCheck
(
SomeJSONData NVARCHAR(MAX) NOT NULL
CONSTRAINT CheckJSON CHECK (ISJSON(SomeJSONData) = 1)
);
GO

Also, if we want to retrieve valid JSON from the database, then we have some syntax that we can use directly for that

FOR JSON AUTO

FOR JSON PATH;

Let’s have a look at those

FOR JSON AUTO is pretty straightforward.

In this example, we’ll simply pull back some data from the sys.configurations table

SELECT
     [Configuration_Property.configuration_id]    = C.configuration_id
    ,[Configuration_Property.Configuration name]  = C.[name]
    ,[Configuration_Property.Value]               = C.[value]
    ,[Configuration_Property.minimum]             = C.minimum
    ,[Configuration_Property.maximum]             = C.maximum
    ,[Configuration_Property.value_in_use]        = C.value_in_use
    ,[Configuration_Property.description]         = C.[description]
    ,[Configuration_Property.is_dynamic]          = C.is_dynamic
    ,[Configuration_Property.is_advanced]         = C.is_advanced
FROM
    sys.configurations AS C
ORDER BY
    C.configuration_id
FOR JSON AUTO;

You might notice that SSMS just brings back the column as a standard text column – which it is. Doble clicking on the column will show us the string in a new tab. It’d be nice if this formatted the JSON.

This experience is much nicer in Azure Data Studio – Where clicking on the returned column will open a new window with the JSON nicely formatted.

FOR JSON PATH Will give us a little more control over the format of the JSON document. Notice in the example below, we have chosen to format the JSON slight differently – by simple naming.

SELECT
     [Configuration_Property.configuration_id]    = C.configuration_id
    ,[Configuration_Property.Configuration name]  = C.[name]
    ,[Configuration_Property.Value]               = C.[value]
    ,[Configuration_Property.minimum]             = C.minimum
    ,[Configuration_Property.maximum]             = C.maximum
    ,[Configuration_Property.value_in_use]        = C.value_in_use
    ,[Configuration_Property.description]         = C.[description]
    ,[Configuration_Property.is_dynamic]       = C.is_dynamic
    ,[Configuration_Property.is_advanced]         = C.is_advanced
FROM
    sys.configurations AS C
ORDER BY
    C.configuration_id
FOR JSON PATH;

Having, touched on some of the simpler aspects of JSON inside SQL Server, we’ve set the stage to now get more advanced.

This article first appeared in the SQLServerGeeks Magazine.
Author: Martin Catherall
Subscribe to get your copy.