(scottish) sql bob blog

rss

Some thoughts of your typical data shepard / data groomer / data dance teacher sort of person.


SQL and Json Part1
Being the SQL server geek in the office means that my desk is the goto place when people have questions about SQL server.  One of the developers asked a question about Json.  When people ask me about Json and SQL server I begin to panic, based on previous experiences.   After a short discussion with my colleagues we made some suggestions and the developer when off to investigate them. 

The next day I was asked by the developer to look over a script which would be run against a client's database.  This was the day's lesson for me. The developer had found some TSQL which I freely admit is something which I had never heard of before and which happened be ‘FOR JSON PATH’.  Using this the developer was able to output some JSON and append some JSON to get the data they required. 

Minimum requirement is that the database is on a SQL 2106 instance.

So what does FOR JSON PATH do?  Very simply it outputs the result of a SELECT statement in JSON format.   So lets create a small table, insert some records into it, then run a SELECT on the table to see what is returned.
















Figure 1 – Three records returned from the temporary table. 

There are three records returned, note that the second record the ‘Colour’ field has a NULL value returned.  In the next step I am going to add ‘FOR JSON PATH’ after the ‘FROM’ statement. 



 

 

 

 

 

 

 

 

 

 

 

 

 

 

Figure 2 – Adding ‘JSON PATH’ to the select statement, the results set it outputted as a JSON formatted string

When the SELECT statement with the ‘JSON PATH’ command is run the results are returned as a JSON formatted string.  The query was ran in Azure Data Studio, so to see the JSON string formatted.  All that was required is to click once with left hand mouse button to open the results in a new window, as show in Figure 2.  

Note that for the second record, the ‘Colour’ field has no data returned, since the value for that record is ‘NULL’.  Can we write a SELECT statement which includes ‘JSON PATH’ and return records with NULL values? 

All we need to is add ‘INCLUDE_NULL_VALUES’ to the query see Figure 3.


Figure 3 - Adding ‘INCLUDE_NULL_VALUES’ to the query, and NULL values are returned by the query.

Whilst demonstrating this feature to the developers in my company. One question which was asked, would it be possible to remove the square brackets eg [ ] from the JSON string outputted.   Yes you can. 


Figure 4 - Adding ‘WITHOUT_ARRAY_WRAPPER’ to the query and the square brackets are suppressed. 

All the queries shown in the screenshots were ran using Azure Data Studio, which if you click on the Results set returned opens it in another new window and formats the JSON. 

That’s enough for just now there is more to come.