JSON, SQL Server and Escape Characters of the printable kind

We know that certain characters in SQL need escaping – an example is the single quote – ‘

SELECT 'This is a single quote '' mark' AS EscapedCharacter;

Microsoft have even given us a special function for escaping certain characters – and at present (May 2019) – it only does JSON. So lets have a look

SELECT STRING_ESCAPE('"This is a quote ". This is a backslash \"','json')  AS EscapedCharacter;

So, that’s an option that we always have.

But let’s create a table with some special JSON characters in it and see how FOR JSON AUTO treats it

IF OBJECT_ID('temp..#Characters') IS NOT NULL
	BEGIN
		DROP TABLE [#Characters];
	END

CREATE TABLE #Characters
(
	 CharacterID INT IDENTITY(1,1) NOT NULL
	,String NVARCHAR(100)
);

INSERT INTO #Characters (String) VALUES
('https:\\en.wikipedia.org\wiki\Albert_Einstein'),
('"This could be a quote"'),
('''This could be another quote''');

SELECT * FROM #Characters FOR JSON AUTO;

And we get the JSON

[
  {
    "CharacterID": 1,
    "String": "https:\\\\en.wikipedia.org\\wiki\\Albert_Einstein"
  },
  {
    "CharacterID": 2,
    "String": "\"This could be a quote\""
  },
  {
    "CharacterID": 3,
    "String": "'This could be another quote'"
  }
]

Which is perfectly escaped.

Notice that the JSON escape character is a backslash – \

Also notice that although we escaped the single quote character ‘ in the SQL table, the FOR JSON AUTO clause knew this was escaped in the table and addressed it appropriately in the resulting JSON.

We’ll look at more escaping of characters in the next installment.

I hope this has helped on your SQL Server JSON journey. We’ll see more real soon.

Have a great day

Cheers

Marty

Download Files

JSON, SQL Server and Esacpe Characters of the printable kind

SQL Server and JSON – JSON PATH

So, we’ve seen how FOR JSON AUTO works. It’s fairly simple – just add the clause onto the end of the SQL statement

But what happens if we need more control over how the JSON will look?

Well, we have another clause that’ll do that for us.

Similar to FOR JSON AUTO – this one is called FOR JSON PATH

For Example using out FOR JSON AUTO clause we ended up with JSON that looked like this. (I’ve just selected the TOP 1 row of the sys.configurations table here)

[
  {
    "configuration_id": 101,
    "Configuration name": "recovery interval (min)",
    "Configuration Value": 0,
    "minimum": 0,
    "maximum": 32767,
    "value_in_use": 0,
    "description": "Maximum recovery interval in minutes",
    "is_dynamic": true,
    "is_advanced": true
  }
]

But what if we wanted our JSON to look something like this

[
  {
    "configuration_id": 101,
    "Configuration_Property": {
      "Configuration 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
    }
  }
]

Well, we use FOR JSON PATH – and we also alias the T-SQL code. You’ll notice that there is a new block called “Configuration_Property” that contains 8 of the properties.

so, here’s the query that produced the above output

SELECT 
	 [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;

I think that although the JSON represents the same information, it’s easier to read. I also think it can make queries against the JSON easier to understand, but we’ll have a look at that in a later instalment.

I hope this helps

Have a great day.

Cheers

Marty.

SQL Server and JSON – JSON AUTO

We’ll seen how to get JSON data from a file and how to insert JSON directly into a table.

But how do take data from our SQL Server queries and turn that data into valid JSON?

Turns out that we have a few options – and we’ll look at some right now.

FOR JSON Auto

And it’s actually quite simple, all we need to do is place FOR JSON AUTO on the very end of our T-SQL statement

USE [tempdb];
GO

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

Notice that we have aliased the columns “name” to “Configuration name” and “value” to “Configuration Value” – both of which can be seen from the JSON produced.

[
  {
    "configuration_id": 101,
    "Configuration name": "recovery interval (min)",
    "Configuration Value": 0,
    "minimum": 0,
    "maximum": 32767,
    "value_in_use": 0,
    "description": "Maximum recovery interval in minutes",
    "is_dynamic": true,
    "is_advanced": true
  },
  {
    "configuration_id": 102,
    "Configuration name": "allow updates",
    "Configuration Value": 0,
    "minimum": 0,
    "maximum": 1,
    "value_in_use": 0,
    "description": "Allow updates to system tables",
    "is_dynamic": true,
    "is_advanced": false
  }
]

We can do something a something a touch more complex. Let’s see how many dynamic values we have on our instance

SELECT 
     [is_dynamic] = 
	 CASE
		WHEN C.is_dynamic = 0 THEN 'No'
		ELSE 'YES'
	 END
	,[Count] = COUNT(*)
FROM 
	sys.configurations AS C
GROUP BY
	C.is_dynamic
ORDER BY
	[is_dynamic]
FOR JSON AUTO;

Which will give us the JSON below

[
  {
    "is_dynamic": "No",
    "Count": 17
  },
  {
    "is_dynamic": "YES",
    "Count": 66
  }
]

And that’s all there really is too it.

We’ll have a look at something a bit flexible in the next instalment.

I hope this has helped on your SQL Server JSON journey. We’ll see more real soon.

Have a great day

Cheers

Marty

Download Files

SQL Server and JSON – JSON Auto

Reading JSON Files from disk

G’day,

Data comes in a variety of formats one of which is JSON, and often the source would be files that reside on the operating system.

We have a few different options to read these files into SQL Server, often SSIS is the one that springs to mind.

But we do have another option, one that is simpler than SSIS and uses just a language that we’re already familiar with T-SQL.

Lets, take a look.

Supposing we have the following JSON in a file (I’ve slimmed this down for display purposes – there’s a longer file in the Resources)

[
    {
        "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
    },
    {
        "configuration_id": 102,
        "name": "allow updates",
        "value": 0,
        "minimum": 0,
        "maximum": 1,
        "value_in_use": 0,
        "description": "Allow updates to system tables",
        "is_dynamic": true,
        "is_advanced": false
    }
]

Then we can simple load that file into a T-SQL variable using the following code (note – your path may vary)

USE tempdb;
GO

DECLARE @JSON NVARCHAR(MAX);

SELECT 
	@JSON = BulkColumn
FROM 
	OPENROWSET (BULK 'C:\data-marty\JSON\Reading JSON Files from disk\Reading_JSON_Files_from_disk.json', SINGLE_CLOB) as j;

SELECT @JSON AS [JSON];
GO

If we are using SQL Server Management Studio (SSMS) then the contents of the files are shown in a single column

This is somewhat ugly and the column is not recognised as JSON (as we’ve already seen there’s no JSON datatype in SQL Server)

So, if we want to see this in a nice JSON editor, then we can simply copy the output cell and paste the text into an online JSON editor / formatter / parser and ask to see it formatted nicely.

Some online JSON resources that I have found useful are

However, you could just use Visual Studio Code.

The same is true in Azure Data Studio.

And that’s simply because the environment has no clue what type of data is being returned – if it did, things might be different, as we’ll see in a later post.

And while it might be irrelevant to the tool (SSMS or ADS) exactly what sort of data has been brought back from the statement – it should be very relevant to the author of the script.

We’re already aware that there is no JSON data type in SQL Server. But we do have the ability to check that we have valid JSON.

So, let’s do that.

DECLARE @JSON NVARCHAR(MAX);

SELECT 
	@JSON = BulkColumn
FROM 
	OPENROWSET (BULK 'C:\data-marty\JSON\Reading_JSON_Files_from_disk\Reading_JSON_Files_from_disk_01.json', SINGLE_CLOB) as j;

SELECT ISJSON(@JSON);

I hope this has helped on your SQL Server JSON journey. We’ll see more real soon.

Have a great day

Cheers

Marty

Download Files

Reading JSON Files from disk

Just like XML? Dipping into JSON.

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

Just like XML? Dipping into JSON – Resources

JSON, SQL and NoSQL

Over the last year, I’ve been presenting on CosmosDB at SQL Saturday’s and various user groups.

This presentation has morphed somewhat over time to now be more of a comparison between functionality in CosmosDB and functionality in SQL Server -and a discussion of where one technology may be more relevant than the other.

In case you’re not aware of CosmosDB it’s Microsoft’s cloud based, planet scale, fully managed NoSQL (that’s Not only SQL) database – and it’s heavily JSON based.

While a lot of the knowledge gained as a SQL Server professional will be relevant to the world of Cosmos DB – there are elements that can lead the SQL Server professional down the wrong path and cause a substantial degree of pain at a later date.

However, lately, I’ve took to diving into the comparison with what I think is a good starting point – and that’s JavaScript Object Notation or JSON.

It’s kind of amazed me how many SQL professionals have turned up for my presentation and have not explored JSON at all – sure, they are aware of what it is, but the mechanics of it are mostly left as an exercise ‘to be explored’ at a later date.

That ‘later date’ maybe when some sort of data transfer has to done involving SSIS, ADF (v2), CosmosDB or some other technology or situation that lends itself well to JSON formats.

One of those uses – I’ve found – is setting up tables with configuration information -sure, there are lots of ways to achieve this, but the JSON format is one of the easier – and best – that I’ve found to date.

SQL Server 2016 introduced JSON support within the database and this support is in every edition of SQL Server from SQL Server 2016 forward.

One myth that I see regularly is that the JSON support in SQL Server may be analogous to the XML support offered by the same product. While, there are some comparisons, I’ve personally found dealing with JSON in SQL Server much more straightforward than dealing with XML – and, if you have never dealt with JSON in SQL Server before then you might be surprised how flexible it it.

For that reason, I’ve decided to invest some time in producing some posts that are specifically dedicated to the JSON functionality in SQL Server, while there’s a lot of good stuff already out there – and I’ll reference this when it’s relevant – one of my goals will be to build on each post to eventually explore many uses and potentials of JSON and the opportunites that it offers for SQL Server professionals.

I hope you enjoy it and I’d love to get feedback from you of any variety.

Have a great day

Cheers

Marty