Sifiso Ndlovu

Convert SQL Server results into JSON

July 12, 2016 by

In my article, Warehousing JSON Formatted Data in SQL Server 2016, we had a look at available T-SQL options for converting JSON data into rows and columns for the purposes of populating a SQL Server based data warehouse. The increased popularity of JSON in modern web applications may create a requirement for data teams to expose some of their data to client applications (i.e. reporting tools, web services etc.) in a JSON format. In this article we take a look at how such a requirement can be implemented by data teams using SQL Server 2016 FOR JSON clause

SQL Server to JSON Supported Data Types

Like many of the features in SQL Server, there are terms and conditions to using them and JSON is no different. Thus, it is important that we take note of the supported data types. SQL Server data stored in the following data types cannot be converted into JSON:

A breakdown of supported data types is shown in Table1  

SQL Server Data Type JSON Data Type
char, nchar, varchar, nvarchar, date, datetime, datetime2, time, datetimeoffset, uniqueidentifier, money string
int, bigint, float, decimal, numeric number
Bit Boolean
varbinary, binary, image, timestamp, rowversion BASE64-encoded string
Table 1

FOR JSON T-SQL Clause

Although SQL Server’s support for XML allowed for graphical representation of the data via an editor (shown in Figure 1), attempting to view JSON data via an editor may be frustrating as JSON data is shown as an unformatted single row.


Figure 1


Figure 2

It is therefore advisable that whilst you teach yourself JSON in SQL Server that you find yourself a JSON editor. For the purposes of this discussion, I will be using JSONFormatter from curiousconcept.com. As can be seen in Figure 3, the JSON output from Figure 2 is now properly formatted.


Figure3

There are two ways that relational results can be converted into JSON, namely, the AUTO and PATH options.

  1. Convert Results Using AUTO Mode

    This is the simplest way to convert relational data into a JSON format as all that you have to do is to add FOR JSON AUTO clause at the end of your SELECT statement. In this mode, the structure of the JSON output is determined by a combination of the order of columns in your SELECT statement as well as the tables that are referenced by the SELECT statement. Figure 4 shows a T-SQL statement that converts the results from our fictitious Fruit Sales data mart into JSON.

    Figure 4

    The results of the above scripts are shown in below in which a single array (represented by square bracket) was returned with rows held as objects (represented by curly braces).


    Figure 5

    The sample script provided in Figure 4 does not fully demonstrate the role of column and table ordering in FOR JSON AUTO clause as only a single table was used. Figure 6 shows the revised script with a join to another fictitious customer lookup dimension that stores additional information regarding customers that have purchased fruits.

    Figure 6

    The execution of the above script results into an output that is shown in Figure 7. You will now notice that another child array (with its own objects) labelled cust appears in the output. The child array represents the information retrieved from the customer dimension.


    Figure 7

    However, when we change the column order of the SELECT statement such that it begins with a column from the customer dimension as shown in Figure 8, we get a different output than the one in Figure 7 in that the child array is now based off the FruitSales dimension.

    Figure 8


    Figure 9

    Therefore, although using the AUTO mode is convenient, it often returns an inconsistent output whose ordering is subject to change based on the column ordering in the SELECT statement. To ensure that your JSON results are consistent, you will have to make use of the PATH mode.

  2. Convert Results Using PATH Mode

    The PATH mode can be used in two ways:

    1. Without a dot syntax

    2. With a dot syntax

    When you are using it without a dot syntax, it works similar to the AUTO mode in that it will generate a JSON output based on the ordering of columns in your SELECT statement.

    Figure 10

    However, as it can be seen in Figure 11, the PATH mode doesn’t automatically group information from joined tables into child arrays. In fact, all columns from the two tables are shown in the same root level.


    Figure 11

    To organise the JSON output into child arrays, you will have to use the dot syntax as shown in Figure 12. The label before the dot represents the name of the object – in this case we have two objects named Sales and Cust.

    Figure 12

    The execution results of Figure 12 are shown below.


    Figure 13

    You can still organise your JSON PATH output into child arrays by converting your script into a nested join as shown in Figure 14.

    Figure 14


    Figure 15

    Options FOR JSON Clause

    Both AUTO and PATH modes allow you to specify additional options such as ROOT, INCLUDE_NULL_VALUES and WITHOUT_ARRAY_WRAPPER.

    1. ROOT

      The ROOT option is used assign a label to the top-level array. Figure 16 shows the application of the ROOT option.

      Figure 16

      As it can be seen in Figure 17, our main array is now titled TOP_LEVEL.


      Figure 17

    2. INCLUDE_NULL_VALUES 

      The default behaviour when using FOR JSON clause is that NULL values will not be included in your JSON output. This can be overridden by specifying the INCLUDE_NULL_VALUES option. To illustrate this point I have added a dummy fruit transaction in my SELECT statement as shown in Figure 18. You will notice that the last four values in my dummy transaction are NULL.

      Figure 18

      The output of this statement is shown in Figure 19 in which the last 4 values that were NULL are not included in the JSON output.


      Figure 19

      To override this behaviour, you just need to include INCLUDE_NULL_VALUES option as shown in Figure 20.

      Figure 20

      As it can now be seen in Figure 21, after including the INCLUDE_NULL_VALUES option, our JSON output includes NULL values.


      Figure 21

    3. WITHOUT_ARRAY_WRAPPER

      By default every FOR JSON clause returns JSON data wrapped around square brackets – also known as, an array. There are instances whereby you want the square brackets excluded from the output because you may want to concatenate two or more JSON data. Figure 22 shows the application of the WITHOUT_ARRAY_WRAPPER option.

      Figure 22

      The results of the above scripts are shown below.


      Figure 23

    Finally, you would have noticed in Figure 22 that I used a TOP clause to limit my selection into a single row. This is because when you use the WITHOUT_ARRAY_WRAPPER option against a dataset that has more than one row, you will run into JSON validation error (shown in below) caused by the missing square brackets. Thus, be careful in the way you use the WITHOUT_ARRAY_WRAPPER option as it may lead to unintended validation errors.


    Figure 24

    Downloads

    Sample selectSIFISOBlogs database

    Reference


    Sifiso Ndlovu
    Latest posts by Sifiso Ndlovu (see all)

    Related posts:

    1. How to import/export JSON data using SQL Server 2016
    2. Import JSON data into SQL Server
    3. SQL Server JSON functions: a bridge between NoSQL and relational worlds
    4. The JSON_QUERY() function to extract objects from JSON Data
    5. Illustration of an example availability monitoring service Using PowerShell and SQL
JSON

About Sifiso Ndlovu

Sifiso is Data Architect and Technical Lead at SELECT SIFISO – a technology consulting firm focusing on cloud migrations, data ingestion, DevOps, reporting and analytics. Sifiso has over 15 years of across private and public business sectors, helping businesses implement Microsoft, AWS and open-source technology solutions. He is the member of the Johannesburg SQL User Group and also hold a Master’s Degree in MCom IT Management from the University of Johannesburg. Sifiso's LinkedIn profile View all posts by Sifiso W. Ndlovu

168 Views