Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Including NULL Values in JSON Output in SQL Server 2016


By:   |   Read Comments   |   Related Tips: More > SQL Server 2016

Attend a SQL Server Conference for FREE >> click to learn more


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


Last Update:


signup button

next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools