In case you were unaware JSON stands for JavaScript Object Notation, and it’s basically used to represent and mostly interchange data. It’s obviously machine readable but at the same time very human readable. Well, that’s my definition at least, toy can read more here if you are interested.
JSON support was introduced into SQL Server with the 2016 release. Its also in Azure SQL Database and Azure Synapse Analytics (SQL DW). Its in every version of SQL Server since.
One of the complaints that I hear in some of my SQL Saturday presentations that touch on JSON in any way is that people who have never used it (inside SQL Server) often consider (assume) it to be implemented in the same way as XML is in SQL Server.
The first thing that I have to say about that is “never assume…” – I guess you may have come across that phrase before and can even finish the rest of that sentence.
But one of the main crucial differences – at least for me – is
“There is no JSON data type in SQL Server”
There are, however, many functions available to manipulate JSON in SQL – and we’ll look at these in detail over the course of this series.
So, that brings up one question
“How is JSON data stored within SQL Server?”
In fact JSON data is simply stored in an NVARCHAR(MAX) column most of the time.
So, let’s put our first piece of JSON into the database
[
{
"NameID": 1,
"FirstName": "Peter",
"LastName": "Jackson"
},
{
"NameID": 2,
"FirstName": "Kate",
"LastName": "Sheppard"
},
{
"NameID": 3,
"FirstName": "Ernest",
"LastName": "Rutherford"
},
{
"NameID": 4,
"FirstName": "Jacinda",
"LastName": "Ardern"
}
]
Here’s the T-SQL to CREATE the database and table that we’ll use – and also insert the above JSON (Note this will drop and recreate a database called JSONDB)
USE tempdb;
GO
IF EXISTS (SELECT * FROM sys.databases AS dbs WHERE dbs.[name] = 'JSONDB')
BEGIN
ALTER DATABASE [JSONDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE JSONDB;
END;
CREATE DATABASE JSONDB;
GO
USE JSONDB;
GO
CREATE TABLE dbo.Jdata
(
SomeJSONData NVARCHAR(MAX) NOT NULL
);
GO
INSERT INTO dbo.Jdata(SomeJSONData) VALUES
(
'[
{
"NameID": 1,
"FirstName": "Peter",
"LastName": "Jackson",
"About": "https:\/\/en.wikipedia.org\/wiki\/Albert_Einstein"
},
{
"NameID": 2,
"FirstName": "Kate",
"LastName": "Sheppard",
"About": "https:\/\/en.wikipedia.org\/wiki\/Kate_Sheppard"
},
{
"NameID": 3,
"FirstName": "Ernest",
"LastName": "Rutherford",
"About": "https:\/\/en.wikipedia.org\/wiki\/Ernest_Rutherford"
},
{
"NameID": 4,
"FirstName": "Jacinda",
"LastName": "Ardern",
"About": "https:\/\/en.wikipedia.org\/wiki\/Jacinda_Ardern"
}
]'
);
SELECT * FROM dbo.Jdata;
Notice that the final SELECT statement displays a single NVARCHAR column with the JSON incorporated – as expected.
Here, we see one of the differences, because if the column was an XML data type then we’d see a clickable link . The T-SQL fragment below creates a table with a single XML column and inserts some XML data just so that you can see the difference in action.
USE JSONDB;
GO
CREATE TABLE dbo.xlTest
(
XMLCol XML
);
INSERT INTO dbo.xlTest(XMLCol) VALUES(
'<Names>
<FirstName>Peter</FirstName>
<LastName>Jackson</LastName>
</Names>
');
select * FROM dbo.xlTest
So, how do we go about verifying that we actually do have valid JSON in our NVARCHAR column and not just some random text.
Well, lets see our first JSON function – ISJSON.
This will determine (as the name suggests) if we have valid JSON data or not. We can use this function directly with a column or with a text string.
Here, we’ll use it directly on our JSON column
SELECT
J.SomeJSONData ,
[ValidJSON] = ISJSON(J.SomeJSONData)
FROM
dbo.Jdata AS J;
And here we’ll use it directly on a valid string
SELECT ISJSON('
[
{
"NameID": 3,
"FirstName": "Ernest",
"LastName": "Rutherford",
"About": "https:\/\/en.wikipedia.org\/wiki\/Ernest_Rutherford"
}
]')
And on a string that’s not valid JSON
SELECT ISJSON('Just a random string');
So, all we need to do in order to validate that the string we are adding to our table is indeed valid JSON – we can do that with a check constraint.
USE JSONDB;
GO
CREATE TABLE dbo.JdataWithCheck
(
SomeJSONData NVARCHAR(MAX) NOT NULL
CONSTRAINT CheckJSON CHECK (ISJSON(SomeJSONData) = 1)
);
GO
Now lets try to insert a string that’s not valid JSON
INSERT INTO dbo.JdataWithCheck(SomeJSONData) VALUES
(
'Just a random piece of text'
);
And we get the expected error message
Msg 547, Level 16, State 0, Line 13
The INSERT statement conflicted with the CHECK constraint “CheckJSON”. The conflict occurred in database “JSONDB”, table “dbo.JdataWithCheck”, column ‘SomeJSONData’.
So, we’ve took our first steps into the world of SQL Server and JSON.
We’ll see more soon
Have a great day
Cheers
Marty
Download Files