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

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.

Data Events in Australia and New Zealand in early 2020

Over the course of the next few weeks there are some significant events taking place that reflect technologies on the Microsoft data platform and beyond.

Thursday 13th and 14th February will see Microsoft Ignite – The Tour Sydney

Saturday 15th February 2020 will see two events – focusing on Microsoft Business Applications

Power Platform Saturday Sydney

Power Platform Saturday Melbourne.

Both Power Platform events are entirely free.

Monday 17th February 202o sees the start of the Difinty conference in Auckland, New Zealand.

There’s quite a few high profile speakers that have traveled quite some distance to speak at this event – these are both members of the Microsoft Product teams and MVP’s

The Monday(17th February 2020) and Thursday(20th February 2020) are both dedicated to several workshops with the Tuesday and Wednesday being the main conference sessions

The conference is hosted by Reza Rad and Leila Etaati of Radacad – who are hosting the conference in #Auckland for the forth time.

Saturday 22nd February 2020 will see SQL Saturday Wellington taking place. This is a 100% free event and will see a lot of the speakers from the Difinity Conference traveling to give their time to benefit the community.

The day before (Friday 21st February 2020) will see SQL Saturday Wellington pre-conference events – and while these are paid training days they are substantially discounted and offer amazing value.

Later in the year will see SQL Saturday Brisbane taking place on Saturday 30th May 2020. This year they have an amazing venue that is right in the middle of the city and really convenient for public transport.

There’s a call-for-speakers currently open for the Brisbane event as well and some great sponsors lined up. You’ll need to get a PASS membership in order to submit – but that’s free and as an added bonus will give you access to some great educational content.

Later in the year we should see additional SQL Saturdays at

  • Auckland
  • Christchurch
  • Melbourne
  • Sydney
  • Perth

Keep your eye on the SQL Saturday website for these dates and if you are so inclined then submit a session in the call for speakers.

I’m looking forward to meeting many old friends at these events and hopefully making a few new ones too.

Have a great day.

Cheers

Martin.

Data Privacy – Playing your part

Tuesday 28th January 2020 was “Data Privacy Day 2020”

Some may refer to this as Data Protection Day, but it is really just a day to draw attention to privacy issues that exist around the use of digital data.

You can read more about this day here and here.

Ironically, on that day, I just happened to be investigating some privacy / security issues and I was reminded that some companies, individuals and even industry sectors that should be blatantly aware of the issues surrounding data privacy are either

  • Ignore of these issues
  • Playing ignorant
  • Thinking ‘it’ll never happen to me’
  • Not caring
  • putting profits above the protection of their clients.
  • All (or some of the above)

And there’s probably many more reasons that people are employing less than desirable practices to look after their data.

If you need more evidence you only have to look at the list of big global companies that have suffered data breaches of some kind in the last few years.

The damage caused by a data breach can be irreparable. Law suits may follow, reputations may be damaged, goodwill may be lost and that’ll soon be reflected in financial figures.

There are plenty of simple things that people can do to help keep their company’s (and their own) data safe. I’ll go into more details on these in later posts but initially I just wanted to start by saying (or rather reiterating) that security is everybody’s job.

Just because the word security is not in your job title does not mean you can get away with complacency.

If you see an issue – speak up. If you could suggest an improvement then speak up too. I get that there may be cultural barriers in some workplaces around these sort of things but good employers will remove these barriers and allow a physiologically safe workplace.

This is an amazingly cost effective way to help secure your environment.

A lack of data protection is not only causing embarrassment to the IT industry as whole but is costing individuals – who have trusted banks and other large institutions with their data – both time and money.

Nowadays, it is easy for a person to switch banks, insurance companies, airlines, phone / internet / TV provides or any other service provider really easily and that may have consequences for your employment.

So, whatever industry you are in I’d encourage you to play your own part – however small – in helping your company keep the data of their customers safe and secure.

Have a great day.

Cheers

Marty

Simply forgot – What’s in my path?

Simple things we often forget

I was recently doing some work that required me to look at the PATH system variable in Windows.

Nothing too hard about that, just open a command prompt and type path

and we get all the directories available in the PATH listed.

That’s sometimes a little hard to read, so say we wanted each directory on a separate line – simple, Powershell can do that for us.

In fact – I prefer using the Powershell ISE to the simple Commandline windows

If you’re looking for the Powershell ISE, just do a search directly from the start menu in windows

Then it’s just a simple matter of adding a few lines of code

and we’ll see the output listed (truncated here)

Hope this helps somebody out.

Cheers

Marty