Including NULL Values in JSON Output in SQL Server 2016

Problem

In previous tips we explored the new JSON features in SQL Server 2016. By default, JSON doesn’t show attributes that have NULL values. So in this tip we will have a look at how to display NULL values for the JSON output.

Solution

To simulate the problem, we will create and run a few queries.

Typical SQL Server Output

Let’s run the following query without JSON output.

Use AdventureWorks2016
go
SELECT M.ProductModelID,M.Name as [ProductModel.Name],
ProductID, Size
FROM Production.Product P
INNER JOIN Production.ProductModel M ON P.ProductModelID = M.ProductModelID
WHERE M.ProductModelID= 33

Below is the output of above query.

As we can see the SIZE column has NULL values.

SQL Sever Query with JSON Output

If we run the same query using the JSON format output will can see that the SIZE column in not included in the output because there are NULL values.

Use AdventureWorks2016
go
SELECT M.ProductModelID,M.Name as [ProductModel.Name],
ProductID, Size
FROM Production.Product P
INNER JOIN Production.ProductModel M ON P.ProductModelID = M.ProductModelID
WHERE M.ProductModelID= 33
FOR JSON Path 

Query with JSON Output and the Include_Null_Values Option

To show the NULL values we have to use the INCLUDE_NULL_VALUES option which will force the output of the NULL values. Now if we run the query with this parameter we can see the NULL values are included in the output.

Use AdventureWorks2016
go
SELECT M.ProductModelID,M.Name as [ProductModel.Name],
ProductID, Size
FROM Production.Product P
INNER JOIN Production.ProductModel M ON P.ProductModelID = M.ProductModelID
WHERE M.ProductModelID= 33
FOR JSON Path, INCLUDE_NULL_VALUES

Summary

If you are creating JSON formatted output and you need to include NULL value columns, remember to use the Include_Null_Values option.

Next Steps

• Check out these tips about JSON in SQL Server 2016
  • JSON Support in SQL Server 2016.
  • Introducing JSON in SQL Server 2016.