(scottish) sql bob blog


Some thoughts of your typical data shepherd / data plumber / data dance teacher sort of person.

SQL and Json Part 2
In an earlier post I looked at FOR JSON PATH which outputs a JSON string from SQL Server.  Now I am going to look at FOR JSON AUTO and some other options to see how these command differ.  

As before let's start off with a select statement.   The query shown in Figure 1 is from AdventureWorks2012 sample database. 

Figure 1 – Simple select statement for the SalesOrderNumber SO463659

Next let's see how the output looks when added FOR JSON PATH to the select statement see Figure 2. 

Figure 2 – SELECT statement from Figure 1 showing the output when FOR JSON PATH is used 

Let's make one change to the SELECT statement in Figure 2, instead of using FOR JSON PATH use FOR JSON AUTO. 

Figure 3 – Output from Select statement with FOR JSON AUTO 

Looking at Figure 3 the JSON string outputted is different from one shown in Figure 2.  With the select statement in Figure 3 all the records are from a single SalesOrderNumber and Order date.  Hence all the records from Sales.SalesOrderDetail alias “D” are in a child node underneath the parent record from  Sales.SalesOrderHeader. 
Let's try a different SELECT query this time select 3 records with different SalesOrderNumbers see Figure 4. 

Figure 4 – results set for the three SalesOrderNumbers 

Now lets try the same query with ‘FOR JSON AUTO’ the query and output is shown in Figure 5. 

Figure 5 – results set for the three SalesOrderNumbers outputted as a JSON string 

Looking at Figure 5 each individual SalesOrderNumber are placed in their own node.  The corresponding SalesOrderDetail values are placed in a child node underneath the parent SalesOrderNumber. 

Using FOR JSON AUTO the format of the JSON string is determined by the SELECT statement.  Whereas FOR JSON PATH which we demonstrated in the previous blog post the JSON string output is controlled by the fields and table presented in the SELECT statement. 

your Comment will be showing after administrator's approval

b i u quote

Save Comment
Showing 0 Comment