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

A trusty old friend – The BCP utility

One of the requirements that springs up around data stores – regardless of if they are relational, not relational, cloud based or some other variety is (unsurprisingly) the need to access the data for various reasons such as reporting or analysis (amongst other things). Often, this requires moving the data (off-loading the data) to another environment / system where analysis or reporting can take place without the need to impact the main system.

Very often we use tools such as SQL Server Integration Services or Azure Data Factory for this sort of thing. But occasionally we might wish to fall back to a utility that’s been in SQL Server (as well as Sybase) for a long time – and that is the Bulk Copy Program, or BCP for short.

The BCP utility (bcp.exe) can be found in the SQL Server installation directory structure. On my machine it can be found at

C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\bcp.exe

Your system may be slightly different. Regardless, the directory should be accessible via the PATH variable and so simply typing bcp or bcp -? should display a list of arguments that the bcp utility accepts.

As an aside, did you also know that you can look at the same display using the PowerShell ISE – which in some cases may provide a more pleasing display

Let’s see how easy it is. We’ll use the sys.configurations table as everybody has that.

Clear-Host;
bcp.exe "SELECT * FROM sys.configurations" queryout "C:\temp\configurations.txt" -Slocalhost -dtempdb  -T -c -t',';

If you do run the above code then you will notice that the text names and descriptions have spaces, this might be a problem. We could handle this in the bcp utility. However, I’d be more inclined to do that in the T-SQL query, but that’s just a personal choice others may prefer another method. But here’s the query I’d use – notice the use of QUOTENAME to enclose the text in double quotation marks.

USE tempdb;
GO

SELECT
	[configuration_id], 
	[name] = QUOTENAME([name], '"'),
	[value], 
	[minimum], 
	[maximum], 
	[value_in_use], 
	[description] = QUOTENAME([description] , '"'), 
	[is_dynamic], 
	[is_advanced]
FROM 
	sys.configurations AS C
ORDER BY 
	C.configuration_id ASC;

Although this is a relatively old utility, I still find it remarkably useful and use it reasonably often – hence this post.

There are other ways to accomplish the same requirement (maybe even without moving the data – but we’ll leave that for another day)

And, by the way, the data that has been extracted is now on disk – so be sure to have a plan that deals with security.

BCP can also be used to input data into a table as well. I’ll leave that example for the moment but will return to it in a later post.

Have a great day

Cheers

Marty.