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 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.