FOR JSON Clause With INCLUDE_NULL_VALUES Option In SQL Server 2016

Hi folks,

Today we learn how to format query using FOR JSON clause with INCLUDE_NULL_VALUES option in SQL Server 2016. As we seen in previous blogs that when we used FOR JSON clause with AUTO and PATH mode NULL values were not included in the output.

To include the NULL values in JSON output we need to specify the INCLUDE_NULL_VALUES option.

Syntax for INCLUDE_NULL_VALUES is:

INCLUDE_NULL_VALUES

Now first we will execute the below query using AUTO mode to verify the output without NULL values:

SELECT BusinessEntityID, SalesYTD, TerritoryId, SalesQuota

FROM Sales.SalesPerson

WHERE BusinessEntityId = 285

FOR JSON AUTO

JSON_NULL_1

Here we can see in the output that NULL values are not included in the output.

[{

"BusinessEntityID":285,

"SalesYTD":172524.4512

}]

Now we will put the option of INCLUDE_NULL_VALUES in the query to include NULL values.

SELECT BusinessEntityID, SalesYTD, TerritoryId, SalesQuota

FROM Sales.SalesPerson

WHERE BusinessEntityId = 285

FOR JSON AUTO, INCLUDE_NULL_VALUES

JSON_NULL_2

When we execute the above query we can now see that NULL values are included in the output.

[{

"BusinessEntityID":285,

"SalesYTD":172524.4512,

"TerritoryId":null,

"SalesQuota":null

}]

That’s all for the day folks. Hope you like the post.

Regards,

Kapil Singh Kumawat

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook 

 

Avatar

About Kapil Singh Kumawat

Kapil Singh Kumawat has been working with SQL Server since last 5 years. He is from Jaipur, Rajasthan, India and currently working with Cognizant Technology Solutions as SQL Server Developer. He has good experience in performance tuning, SSIS, data migration and data designing. Apart from database he has interest in travelling, watching football and listening music.

View all posts by Kapil Singh Kumawat →

Leave a Reply

Your email address will not be published. Required fields are marked *