Format MS SQL data in JSON format

Prior to MS SQL server 2016 manipulating data in JSON format was not supported and sometimes a challenging task, forcing us to implement third party solutions (e.g. CLR).

In this article we will see how we can create JSON objects from MS SQL data.

Intro (about JSON)

JSON (JavaScript Object Notation) is a language in depended, open standard format for data exchange between applications and services. JSON format is simple and very popular. It is commonly used for example in AJAX applications, web services (REST), NoSQL databases. In simple words, JSON objects are human readable lists of key-value pairs.

Compared to XML, JSON format less verbose and easier to read. JSON object usually contains less data than an XML object and it's more adequate for network transfers. JSON format contains less text than an XML format. Data in JSON format are represented by arrays and objects while XML is a tree structure. XML can store more complex data types and is more robust than JSON. XML can store additional information’s and JSON can store only data.

Here is one example of a JSON formatted object representing a person

{
  "firstName": "Damir",
  "lastName": "Matešić",
  "blogUrl": "http:\/\/blog.matesic.info",
  "workAddress": {
    "streetAddress": "Koturaska 47",
    "city": "Zagreb",
    "postalCode": "10 000"
  },
  "favoriteDrinks": [
    {
      "type": "cocktail",
      "name": "Gin and tonic"
    },
    {
      "type": "classic",
      "name": "Coffe with milk"
    }
  ],
  "spouse": null
}

JSON supports only four data types:

String - escaped Unicode text surrounded by double quotes
Number - double-precision float
Boolean - true/false written in lowercase
null - represents a null value

JSON escaping rules (like already mentioned in my article about MS SQL STRING_ESCAPE):

Quotation mark (") -> \"
Reverse solidus (\) -> \\
Solidus (/) -> \/
Backspace -> \b
Form feed -> \f
New line -> \n
Carriage return -> \r
Horizontal tab -> \t
Control characters (0-31) -> \u<code> (e.g. CHAR(0) -> \u0000)

More about JSON you can read on some of the following links:

JSON in MS SQL Server

Pretty much like creating XML data in MS SQL server (using FOR XML clause), in MS SQL server we can use FOR JSON clause in the SELECT statement.

There are two modes supported by the FOR JSON clause:

FOR JSON AUTO – the JSON output will be formatted automatically
FOR JSON PATH – the JSON output will be formatted by the query itself (using aliases) and its purpose is to create more complex output like nested objects

Because of limited data types used in JSON format, MS SQL will convert data types by the following rules:

Char, Varchar, Nchar, NVarchar, Text, Ntext, Date, DateTime, DateTime2, DateTimeOffset, Time, UniqueIdentifier, Smallmoney, Money, XML, HierarchyId, Sql_Variant -> String
Tinyint, Smallint, Int, Bigint, Decimal, Float, Numeric -> Number
Bit -> Boolean
Binary, Varbinary, Image, Rowversion, Timestamp -> Base 64 encoded string
null -> null

Data types not supported by JSON in MS SQL are geography, geometry, and CLR-based user defined data types. We will see an example later in this article.

FOR JSON AUTO

Using FOR JSON AUTO the data will be formatted by MS SQL Server engine. The JSON output format is based on how the SELECT statement is written. Let's try to select some data in JSON format.

Example:

SELECT 'Gin tonic' AS Drink FOR JSON AUTO;

Since the FOR JSON AUTO requires a table the following error message is displayed:

"Msg 13600, Level 16, State 1, Line 4
FOR JSON AUTO requires at least one table for generating JSON objects. Use FOR JSON PATH or add a FROM clause with a table name."

Ok, let's try to select a table [Sales].[Customers] from the [WideWorldImporters] demo database.

Example:

SELECT C.* FROM [Sales].[Customers] AS C FOR JSON AUTO;

A new exception is raised:

"Msg 13604, Level 16, State 1, Line 7
FOR JSON cannot serialize CLR objects. Cast CLR types explicitly into one of the supported types in FOR JSON queries."

The reason for this is that the used table [Sales].[Customers] contains the column [DeliveryLocation] of type geography that cannot be represented (serialized) in JSON format.

Anyway, we don't even need all the columns from the [Sales].[Customers] table, so we will just select a part of its data.

Example:

SELECT 
    C.[CustomerID]
    , C.[CustomerName]
    , C.PhoneNumber
    , C.FaxNumber
    , C.WebsiteURL
    , GETDATE() 
FROM 
    [Sales].[Customers] AS C 
FOR JSON AUTO;

Because i did select the GETDATE() value with no alias associated with it the MS SQL engine was not able to serialize this column and a new exception has been raised:

"Msg 13605, Level 16, State 1, Line 10
Column expressions and data sources without names or aliases cannot be formatted as JSON text using FOR JSON clause. Add alias to the unnamed column or table."

Now finally let's try to select the data not getting any exception.

Example:

SELECT TOP(5)
    C.[CustomerID]
    , C.[CustomerName]
    , C.PhoneNumber
    , C.FaxNumber
    , C.WebsiteURL
    , GETDATE() AS DataDateTime 
FROM 
    [Sales].[Customers] AS C 
FOR JSON AUTO;

[{"CustomerID":1,"CustomerName":"Tailspin Toys (Head Office)","PhoneNumber":"(308) 555-0100","FaxNumber":"(308) 555-0101","WebsiteURL":"http:\/\/www.tailspintoys.com","DataDateTime":"2018-08-26T13:43:39.713"},{"CustomerID":2,"CustomerName":"Tailspin Toys (Sylvanite, MT)","PhoneNumber":"(406) 555-0100","FaxNumber":"(406) 555-0101","WebsiteURL":"http:\/\/www.tailspintoys.com\/Sylvanite","DataDateTime":"2018-08-26T13:43:39.713"},{"CustomerID":3,"CustomerName":"Tailspin Toys (Peeples Valley, AZ)","PhoneNumber":"(480) 555-0100","FaxNumber":"(480) 555-0101","WebsiteURL":"http:\/\/www.tailspintoys.com\/PeeplesValley","DataDateTime":"2018-08-26T13:43:39.713"},{"CustomerID":4,"CustomerName":"Tailspin Toys (Medicine Lodge, KS)","PhoneNumber":"(316) 555-0100","FaxNumber":"(316) 555-0101","WebsiteURL":"http:\/\/www.tailspintoys.com\/MedicineLodge","DataDateTime":"2018-08-26T13:43:39.713"},{"CustomerID":5,"CustomerName":"Tailspin Toys (Gasport, NY)","PhoneNumber":"(212) 555-0100","FaxNumber":"(212) 555-0101","WebsiteURL":"http:\/\/www.tailspintoys.com\/Gasport","DataDateTime":"2018-08-26T13:43:39.713"}]

We managed to get the result successfully. Let's try to format the result a little bit readable since the current result is represented by a single line string. For that mission I will use Notepad++ with JSON viewer plugin developed by Kapil Ratnani. And here it is:

[{
	"CustomerID": 1,
	"CustomerName": "Tailspin Toys (Head Office)",
	"PhoneNumber": "(308) 555-0100",
	"FaxNumber": "(308) 555-0101",
	"WebsiteURL": "http:\/\/www.tailspintoys.com",
	"DataDateTime": "2018-08-26T12:58:34.860"
},
{
	"CustomerID": 2,
	"CustomerName": "Tailspin Toys (Sylvanite, MT)",
	"PhoneNumber": "(406) 555-0100",
	"FaxNumber": "(406) 555-0101",
	"WebsiteURL": "http:\/\/www.tailspintoys.com\/Sylvanite",
	"DataDateTime": "2018-08-26T12:58:34.860"
},
...
]

Here is the data also in tabular format:

And finally let's have a look at the same data displayed in XML format using FOR XML AUTO clause.

Example:

SELECT TOP(5)
    C.[CustomerID]
    , C.[CustomerName]
    , C.PhoneNumber
    , C.FaxNumber
    , C.WebsiteURL
    , GETDATE() AS DataDateTime 
FROM 
    [Sales].[Customers] AS C 
FOR XML AUTO;

<C CustomerID="1" CustomerName="Tailspin Toys (Head Office)" PhoneNumber="(308) 555-0100" FaxNumber="(308) 555-0101" WebsiteURL="http://www.tailspintoys.com" DataDateTime="2018-08-26T13:46:24.043" />
<C CustomerID="2" CustomerName="Tailspin Toys (Sylvanite, MT)" PhoneNumber="(406) 555-0100" FaxNumber="(406) 555-0101" WebsiteURL="http://www.tailspintoys.com/Sylvanite" DataDateTime="2018-08-26T13:46:24.043" />
...

FOR JSON PATH

Let's try to run our first SQL example again using the FOR JSON PATH clause.

Example:

SELECT 'Gin tonic' AS Drink FOR JSON PATH;

As you remember the execution of this query was not successful using FOR JSON AUTO clause. But with FOR JSON PATH clause work well. The reason for this is that FOR JSON PATH clause does not require a table.

You will use FOR JSON PATH clause when you need to create more complex types like nested objects. How we can achieve this? Using dots in the column alias you can force the engine to create a nested object. Let's see one example of this.

Example:

SELECT TOP(5)
    C.[CustomerID]
    , C.[CustomerName]
    , C.PhoneNumber AS 'Contact.Phone'
    , C.FaxNumber AS 'Contact.Fax'
    , C.WebsiteURL
    , GETDATE() AS DataDateTime 
FROM 
    [Sales].[Customers] AS C 
FOR JSON PATH;

Result:

[{
	"CustomerID": 1,
	"CustomerName": "Tailspin Toys (Head Office)",
	"Contact": {
		"Phone": "(308) 555-0100",
		"Fax": "(308) 555-0101"
	},
	"WebsiteURL": "http:\/\/www.tailspintoys.com",
	"DataDateTime": "2018-08-26T16:26:38.200"
},
{
	"CustomerID": 2,
	"CustomerName": "Tailspin Toys (Sylvanite, MT)",
	"Contact": {
		"Phone": "(406) 555-0100",
		"Fax": "(406) 555-0101"
	},
	"WebsiteURL": "http:\/\/www.tailspintoys.com\/Sylvanite",
	"DataDateTime": "2018-08-26T16:26:38.200"
},
...]

As you can see int he output, our JSON result contains a nested node "Contact" because we used dot in the name of the alias in the Phone and Fax columns.

Similar when creating XML data using FOR XML clause, you could nest objects using sub select in which you will also use FOR JSON clause.

JSON additional options

NULL values in JSON output

Let's try to create a JSON output from a table containing NULL values and see what will be the result normally.

Example:

DROP TABLE IF EXISTS [Sales].[CustomersN]

SELECT TOP(5)
    C.[CustomerID]
    , C.[CustomerName]
    , CASE WHEN CustomerID IN (2, 5) THEN NULL ELSE C.WebsiteURL END AS WebsiteURL
INTO [Sales].[CustomersN]
FROM 
    [Sales].[Customers] AS C 

-- Select data in JSON format with NULL values
SELECT 
    C.[CustomerID]
    , C.[CustomerName]
    , C.WebsiteURL
FROM 
    [Sales].[CustomersN] AS C 
FOR JSON AUTO;

[{
	"CustomerID": 1,
	"CustomerName": "Tailspin Toys (Head Office)",
	"WebsiteURL": "http:\/\/www.tailspintoys.com"
},
{
	"CustomerID": 2,
	"CustomerName": "Tailspin Toys (Sylvanite, MT)"
}
...
]

As you can see in the output example the columns with NULL values are not mapped to a JSON property. To include NULL values in the JSON output we should use the INCLUDE_NULL_VALUES option. Let's find out what will be the result in that case.
Example:

SELECT 
    C.[CustomerID]
    , C.[CustomerName]
    , C.WebsiteURL
FROM 
    [Sales].[CustomersN] AS C 
FOR JSON AUTO, INCLUDE_NULL_VALUES;

[{
	"CustomerID": 1,
	"CustomerName": "Tailspin Toys (Head Office)",
	"WebsiteURL": "http:\/\/www.tailspintoys.com"
},
{
	"CustomerID": 2,
	"CustomerName": "Tailspin Toys (Sylvanite, MT)",
	"WebsiteURL": null
},
...]

Add a root node to JSON output

Similar to the FOR XML ROOT option you can use the ROOT option to add a single, top-level element to the JSON output

Example:

SELECT TOP(5)
    C.[CustomerID]
    , C.[CustomerName]
    , C.WebsiteURL
FROM 
    [Sales].[Customers] AS C 
FOR JSON PATH, ROOT ('Customers');

{
	"Customers": [{
		"CustomerID": 1,
		"CustomerName": "Tailspin Toys (Head Office)",
		"WebsiteURL": "http:\/\/www.tailspintoys.com"
	},
	{
		"CustomerID": 2,
		"CustomerName": "Tailspin Toys (Sylvanite, MT)",
		"WebsiteURL": "http:\/\/www.tailspintoys.com\/Sylvanite"
	},
	...]
}

JSON single object

As seen in previous examples every JSON output is enclosed with square brackets and it assumes that every output is an array.

Example:

SELECT 
    C.[CustomerID]
    , C.[CustomerName]
    , C.WebsiteURL
FROM 
    [Sales].[Customers] AS C 
WHERE
	CustomerID = 1
FOR JSON PATH;

[{
	"CustomerID": 1,
	"CustomerName": "Tailspin Toys (Head Office)",
	"WebsiteURL": "http:\/\/www.tailspintoys.com"
}]

Using the WITHOUT_ARRAY_WRAPPER option we can format the result as a single object.

Example:

SELECT 
    C.[CustomerID]
    , C.[CustomerName]
    , C.WebsiteURL
FROM 
    [Sales].[Customers] AS C 
WHERE
	CustomerID = 1
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

{
	"CustomerID": 1,
	"CustomerName": "Tailspin Toys (Head Office)",
	"WebsiteURL": "http:\/\/www.tailspintoys.com"
}

The result is valid only if we return a single result like in this case. We can still try to select multiple rows in JSON format and the SQL engine will do the work but the result is not a valid JSON format.

Example:

SELECT 
    C.[CustomerID]
    , C.[CustomerName]
    , C.WebsiteURL
FROM 
    [Sales].[Customers] AS C 
WHERE
	CustomerID IN (1, 2)
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

{
	"CustomerID": 1,
	"CustomerName": "Tailspin Toys (Head Office)",
	"WebsiteURL": "http:\/\/www.tailspintoys.com"
},
{
	"CustomerID": 2,
	"CustomerName": "Tailspin Toys (Sylvanite, MT)",
	"WebsiteURL": "http:\/\/www.tailspintoys.com\/Sylvanite"
}

Do you remember the ROOT option mentioned before? Let's wrap the result using the ROOT option and get a valid JSON output in this case.
Example:

SELECT 
    C.[CustomerID]
    , C.[CustomerName]
    , C.WebsiteURL
FROM 
    [Sales].[Customers] AS C 
WHERE
	CustomerID IN (1, 2)
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, ROOT ('Customers');

The result is a error message:

"Msg 13620, Level 16, State 1, Line 153
ROOT option and WITHOUT_ARRAY_WRAPPER option cannot be used together in FOR JSON. Remove one of these options."

I hope that in some near future this will change but for now is not possible to combine the ROOT option with the WITHOUT_ARRAY_WRAPPER option.

If you would like to read how to format MS SQL data in JSON format, you can find a corresponding post here.

*MS SQL Server sample database "WideWorldImporters" can be downloaded at: https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0

*You can download the complete SQL Script with all examples from the post here: SQL Script.sql (3.37 kb)

IS [NOT] DISTINCT FROM (The Distinct Predicate)

MS SQL GENERATE_SERIES

MS SQL TRIM, LTRIM, RTRIM

MS SQL STRING_SPLIT

LEAST()

GREATEST()

HierarchyID data type Performance, tips & tricks

HierarchyID data type Methods

Hierarchical data in MS SQL

Last Actual Plan with sys.dm_exec_query_plan_stats

Format MS SQL data in JSON format

Intro (about JSON)

JSON in MS SQL Server

FOR JSON AUTO

FOR JSON PATH

JSON additional options

NULL values in JSON output

Add a root node to JSON output

JSON single object

Related posts