Learn more about SQL Server tools

mssqltips logo
 

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 this free live MSSQLTips webcast

Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here 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:


next webcast 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    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools