Author: Marty Catherall

WITHOUT_ARRAY_WRAPPER

When we look at a single JSON object we can view it as

{
  "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
}

But when we have multiple objects , we can see them presented as 

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

However, say we just wanted to get a single row from an SQL Server table and present the JSON as in the first example above

We might try something of the form (note that we are only explicitly asking for 1 row, we could do that with a WHERE clause also – rather than a TOP 1)

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

But the JSON we get back is one element in an array

[
  {
    "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
  }
]

This, may not be what we want. We may for instance want to combine JSON objects and will see how to do that at some stage.

But what if we wanted this single objects brought back on it’s own and not in an array?

Well, we just add the clause WITHOUT_ARRAY_WRAPPER to either JSON AUTO or JSON PATH

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

This can be an amazingly useful function if we want to join different JSON documents together.

The OPENJSON function will still read a document without the array wrapper – for instance.


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
	},
	{
	  "configuration_id": 102,
	  "Configuration 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
	}
',
'$'
)
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
)
Only a single row is returned if the JSON is not in an array format.

Note however that only data from the first element is returned in the tabular resultset

If we want both rows (only 2 in this case)

Then the elements will need to be wrapped as an array – 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
	},
	{
	  "configuration_id": 102,
	  "Configuration 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
	}
]
',
'$'
)
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
)
All rows are brought back when the JSON is an array

You can read more about the WITHOUT_ARRAY_WRAPPER function in the docs.

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

WITHOUT_ARRAY_WRAPPER

OPENJSON – using the default schema to collect new information

We’ve looked at reading JSON from disk and also verifying that a string we have contains valid JSON data. But, naturally, we’d like to do more than that.

Well, there is a method of doing just that and that is using the OPENJSON function.

We can use OPENJSON in two forms – either when we know the schema of the JSON document (or at least the part of the schema that we want to retreive). This is know as using OPENJSON with an explicit schema – we’ll examine this in a latter installment.

The other way of using OPENJSON (and the one we’ll examine in this section) is known as using OPENJSON with the default schema.

SELECT * FROM 
OPENJSON
(
'
[
  {
    "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
  },
  {
    "configuration_id": 103,
    "name": "user connections",
    "value": 0,
    "minimum": 0,
    "maximum": 32767,
    "value_in_use": 0,
    "description": "Number of user connections allowed",
    "is_dynamic": false,
    "is_advanced": true
  }
]
'
);

Let’s look

And we get the flowing results.

Notice that we had three arrays in our JSON document and we have three lines in our resultset.

Notice also that the “type” column has a value of 5.

Let’s have a look at another piece of JSON – one that might be a bit more interesting

SELECT 
	 [key]
	,[value]
	,[type]
FROM 
OPENJSON
(
'
{
"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
	}
}
'
);

This time we get the output

Notice now that we get a new row and a new number in the type column. Maybe each different type has it’s own number. Let’s find something even more interesting.

SELECT 
	 [key]
	,[value]
	,[type]
FROM 
OPENJSON
(
'
	{
		"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
	}
'
);

And this time the output looks like

So, the numbers in the “type” column are indeed very relevant. There exact meaning is listed in the docs, but here’s a brief summary.

type meaning
0null
1string
2number
3boolean
4array
5object
definitions of the “type” column in OPENJSON

We can always insert the values into a table for easy retrieval later.

IF OBJECT_ID('dbo.JSONDetails') IS NOT NULL
	BEGIN
		DROP TABLE dbo.JSONDetails;
	END;

CREATE TABLE dbo.JSONDetails
(
	 JSONDetailID INT IDENTITY(1,1) NOT NULL
	,[key]        NVARCHAR(4000)
	,[value]      NVARCHAR(MAX) NULL
	,[type]       INT NOT NULL
	,[type_desc] AS
	(
		CASE
			WHEN [type] = 0 THEN 'Null'
			WHEN [type] = 1 THEN 'String'
			WHEN [type] = 2 THEN 'Number'
			WHEN [type] = 3 THEN 'Boolean'
			WHEN [type] = 4 THEN 'Array'	
			WHEN [type] = 5 THEN 'Object'
			ELSE 'ERROR'
		END
	)
)

INSERT INTO dbo.JSONDetails
(
	 [key] 
	,[value]
	,[type]
)
SELECT 
	 [key]
	,[value]
	,[type]
FROM 
OPENJSON
(
'
{
	"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
}
'
);

SELECT * FROM dbo.JSONDetails AS J ORDER BY JSONDetailID;

For a full description of the data types of key, value and type using OPENJSON, just see the docs.

Next up is using OPENJSON with an explicit schema.

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 – using the default schema and collect new information

Concatenating Strings, NULLs and EXEC

G’day,

SQL Server’s great – its been teaching me a lot for many years. But every so often you get a surprise. Sometimes it’s a nice one, sometimes it more of the ‘really, are you sure kind’ – and then you realise, yes SQL Server – you’re correct, I really should have known that.

This one involved

  • A cursor
  • String concatenation
  • Executing a T-SQL Statement

The first two of these didn’t suprise me – after I’d thought about it.

The last one did

I’ll attempt to give an example of what occured

I have a table

USE tempdb;
GO

IF OBJECT_ID('dbo.Issue') IS NOT NULL
	BEGIN
		DROP TABLE dbo.Issue
	END;


CREATE TABLE dbo.Issue
(
	SomeName NVARCHAR(50) NULL
);

and, we’re inserting into that table using a concatenated T-SQL string, like so

DECLARE @InsertValue NVARCHAR(50) = N'TestValue';
DECLARE @SQL NVARCHAR(500) = 'INSERT INTO dbo.Issue(SomeName) VALUES(''' + @InsertValue + ''');';
PRINT @SQL;
EXEC(@SQL);

As you’d expect one row goes in

However, the issue that I saw arose when the variable being INSERTed was NULL, like so

DECLARE @InsertValue NVARCHAR(50) = NULL;
DECLARE @SQL NVARCHAR(500) = 'INSERT INTO dbo.Issue(SomeName) VALUES(''' + @InsertValue + ''');';
PRINT @SQL;
EXEC(@SQL);

You’ll notice (if you run the code) that nothing gets printed – which is fair enough as a NULL in a string concatenation will unltimatly yeild a NULL, as anything + an unknown (NULL) is an unknown (NULL)

That wasn’t the surprising part – although it did get me to think a bit at first.

The surprising part (to me at least), was that the string – which is now NULL -has been run inside the EXEC(..) call, had not errored, had not failed, but had not inserted anything either

Try it

DECLARE @InsertValue NVARCHAR(50) = NULL;
DECLARE @SQL NVARCHAR(500) = 'INSERT INTO dbo.Issue(SomeName) VALUES(''' + @InsertValue + ''');';
PRINT @SQL;
EXEC(@SQL);
SELECT * FROM dbo.Issue;

You can try this more simply by executing a NULL string

DECLARE @Test NVARCHAR(20) = NULL;
EXEC(@Test);

Note however, that you can’t directly execute a NULL (although why you’d want to is a different matter 🙂 )

EXEC(NULL)

Msg 156, Level 15, State 1, Line 44
Incorrect syntax near the keyword 'NULL'.

So, now you know.

If you’ve never seen this before (or even if you have, but a long time ago) then this could easily make you wonder what is going on.

I hope this helps somebody.

Have a great day.

Cheers

Martin.

JSON data and a T-SQL variable.

G’day,

You may have noticed when looking at FOR JSON AUTO or FOR JSON PATH that both clauses result in one single column that contains a JSON string.

USE tempdb;
GO

SELECT 
	*
FROM
	sys.configurations AS C
ORDER BY
	C.[configuration_id]
FOR JSON AUTO;
/*
Or
*/
SELECT 
	*
FROM
	sys.configurations AS C
ORDER BY
	C.[configuration_id]
FOR JSON PATH;

But, what if we wanted this data to be put directly into a T-SQL variable?

For a standard T-SQL Statement, we might use something of the form

DECLARE @ConfigurationName SYSNAME = N'';
SELECT 
	@ConfigurationName = C.[name]
FROM	
	sys.configurations AS C
WHERE
	C.configuration_id = 101;
PRINT @ConfigurationName;

The WHERE clause in the above statement ensures that only one row is brought back for [name] and everything is fine.

As an aside, consider what would happen in the above statement if we omitted the WHERE clause (and it brought back multiple rows of data)

Yes, we’d bring back more than a single value, but would we expect to get an error?

Try it

DECLARE @ConfigurationName SYSNAME = N'';
SELECT 
	@ConfigurationName = C.[name]
FROM	
	sys.configurations AS C
PRINT @ConfigurationName;

Notice that this time we get the last value in the returned list of names. On my system that is ‘allow polybase export’ and not ‘recovery interval (min)’ as in the initial example when we had a WHERE clause.

The lesson here is if you expect a single value then ensure that you are only getting a single value back.

But that is a different discussion for a different day.

Right now back to JSON (where the statement guarantees us a single value)

So we have a query that returns a JSON string? So how do we get that string into a variable? we know that there is only one item coming back – however, we can’t access that column directly – so we have to get creative! (or maybe not that creative depending on your T-SQL knowledge)

DECLARE @JSON NVARCHAR(MAX) = N'';
SELECT @JSON = (SELECT 
	*
FROM
	sys.configurations AS C
ORDER BY
	C.[configuration_id]
FOR JSON AUTO);
PRINT @JSON
GO
/*
OR
*/
DECLARE @JSON NVARCHAR(MAX) = N'';
SELECT @JSON = (SELECT 
	*
FROM
	sys.configurations AS C
ORDER BY
	C.[configuration_id]
FOR JSON AUTO);
PRINT @JSON
GO

And there we have it. Now we have a piece of JSON that we have gained directly from a SQL Server table placed straight into a T-SQL variable;

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 data and a T-SQL variable.

JSON, SQL Server and Esacpe Characters of the non-printable kind

G’day,

We observed in a previous installment that JSON uses the backslash character “\” as the escape character.

However, what happens if we actually want a backslash in our sting.

Well, We just escape that with another backslash

SELECT STRING_ESCAPE('\' , 'json');

Which gives (simply)

\\

So when we see a JSON document like this

[
  {
    "Character": "\n"
  },
  {
    "Character": "\r"
  }
]

Then we might wonder

  • Has something gone wrong?
  • Is this valid JSON?

The answer would be that everything is fine and this is perfectly valid JSON.

Why?

Because \r and \n are both non-printable characters.

You’ll see this is valid if you use STRING_ESCAPE

SELECT STRING_ESCAPE(CHAR(10) , 'json') AS [Character]
UNION
SELECT STRING_ESCAPE(CHAR(13) , 'json');

Which gives

Char(10) – ASCII 10 – is the new line character, while CHAT(13) – ASCII 13 – is the carriage return character.

If you’d like to look at other examples of non printable characters then you can play with the STRING_EXCAPE and ASCII T-SQL functions

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 non-printable kind

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