Including NULL Values in JSON Output in SQL Server 2016

By: Rajendra Gupta | Comments | Related: > 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.

Sample SQL Server Query without JSON output

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

SQL Server Query with JSON output which does not include NULL values

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

SQL Server JSON Query including 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.

About the author

Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips