Tag: SQL

Back in our last instalment, we looked at OPENJSON and how we can get data into a tabular format from a JSON document.

Readers may have noticed that we used data from the sys.configurations table in order to generate the JSON document.

However, when we read the data from the document into tabular format we specified that the columns

• Value
• minimum
• maximum
• value_in_use
• description

Which are of type SQL_VARIANT in the table were actually of type NVARCHAR(200) in the resultset that was brought back from the JSON document.

SELECT
     [configuration_id]
    ,[Configuration name] 
    ,[Value]
    ,[minimum] 
    ,[maximum]
    ,[value_in_use]
    ,[description]
    ,[is_dynamic]
    ,[is_advanced]
FROM
	OPENJSON
(
'
  {
    "configuration_id": 101,
    "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
  }
'
)
WITH
(
     [configuration_id]     INT 
    ,[Configuration name]   NVARCHAR(35)
    ,[Value]                NVARCHAR(200)
    ,[minimum]              NVARCHAR(200)
    ,[maximum]              NVARCHAR(200)
    ,[value_in_use]         NVARCHAR(200)
    ,[description]          NVARCHAR(200)
    ,[is_dynamic]           BIT
    ,[is_advanced]          BIT
);
GO

So, why was that? and can we fix it?

Well, JSON and has a handful of data types and SQL Server has lots. So there’s not a one-to-one match.

The best thing to do would be to being them back as one of the simple types (likely NVARCHAR()) and then CAST them in the SELECT to the actual desired data type.

Like so

SELECT
     [configuration_id]
    ,[Configuration name] 
    ,[Value]          = TRY_CONVERT(sql_variant  , [Value]) 
    ,[minimum]        = TRY_CONVERT(sql_variant  , [minimum]) 
    ,[maximum]        = TRY_CONVERT(sql_variant  , [maximum]) 
    ,[value_in_use]   = TRY_CONVERT(sql_variant  , [value_in_use]) 
    ,[description]    = TRY_CONVERT(sql_variant  , [description]) 
    ,[is_dynamic]
    ,[is_advanced]
FROM
	OPENJSON
(
'
  {
    "configuration_id": 101,
    "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
  }
'
)
WITH
(
     [configuration_id]     INT 
    ,[Configuration name]   NVARCHAR(35)
    ,[Value]                NVARCHAR(200)
    ,[minimum]              NVARCHAR(200)
    ,[maximum]              NVARCHAR(200)
    ,[value_in_use]         NVARCHAR(200)
    ,[description]          NVARCHAR(200)
    ,[is_dynamic]           BIT
    ,[is_advanced]          BIT
);
GO

The result set looks the same, however if the values returned are now of the correct data type. So if you wanted to do anything with the resultset like joining on the original table then you would not see any implicit casting.

So, that’s just a small tip of how to handle data type differences between SQL Server and 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

OPENJSON, explicit schema and data type

G’day,

Previously, we have looked at using OPENJSON to gain knowledge about the JSON document that we have presented to the function.

A bit like this

SELECT
	*
FROM
	OPENJSON
(
'
  {
    "configuration_id": 101,
    "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
  }
'
)

Notice that we didn’t request any columns in the SELECT statement, but we got three columns back

• Key
• value
• type

That’s great metadata information – but what if we wanted the actual values from the JSON.

Well, the statement above used OPENJSON with the default schema – which is basically no column list defined. If we want to define a list then we need to use a WITH clause that defines an EXPLICIT schema – like so

SELECT
	 *
FROM
	OPENJSON
(
'
  {
    "configuration_id": 101,
    "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
  }
'
)
WITH
(
     [configuration_id]     INT 
    ,[Configuration name]   NVARCHAR(35)
    ,[Value]                NVARCHAR(200)
    ,[minimum]              NVARCHAR(200)
    ,[maximum]              NVARCHAR(200)
    ,[value_in_use]         NVARCHAR(200)
    ,[description]          NVARCHAR(200)
    ,[is_dynamic]           BIT
    ,[is_advanced]          BIT
);
GO

You might also notice that the names in the WITH clause match those in the JSON document – We can also add these as a column list to the SELECT statement, rather than using SELECT *

Notice also that if we ask for a value in the WITH clause that does not appear in the JSON document (maybe because of a typo) then we simply get a NULL returned in the SELECT list.

Notice here that the name of the first column is incorrect. So we get a null in the resultset.

SELECT
	*
FROM
	OPENJSON
(
'
  {
    "configuration_id": 101,
    "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
  }
'
)
WITH
(
     [configuration_if]     INT 
    ,[Configuration name]   NVARCHAR(35)
    ,[Value]                NVARCHAR(200)
    ,[minimum]              NVARCHAR(200)
    ,[maximum]              NVARCHAR(200)
    ,[value_in_use]         NVARCHAR(200)
    ,[description]          NVARCHAR(200)
    ,[is_dynamic]           BIT
    ,[is_advanced]          BIT
);
GO

This is perhaps one reason that we should include an explicit column list in the SELECT statement

SELECT
     [configuration_id]
    ,[Configuration name] 
    ,[Value] 
    ,[minimum]
    ,[maximum] 
    ,[value_in_use]
    ,[description]
    ,[is_dynamic]
    ,[is_advanced]
FROM
	OPENJSON
(
'
  {
    "configuration_id": 101,
    "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
  }
'
)
WITH
(
     [configuration_id]     INT 
    ,[Configuration name]   NVARCHAR(35)
    ,[Value]                NVARCHAR(200)
    ,[minimum]              NVARCHAR(200)
    ,[maximum]              NVARCHAR(200)
    ,[value_in_use]         NVARCHAR(200)
    ,[description]          NVARCHAR(200)
    ,[is_dynamic]           BIT
    ,[is_advanced]          BIT
);
GO

And we pretty much see exactly what we had before.

Next up is some more useful tips about OPENJSON

Have a great day

Cheers

Marty

Download Files

OPENJSON – using an explicit schema. The WITH clause

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

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