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
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
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
Hello,
Question: I need to use the null option in JSON script – if I do not have any value, so how to specify the INCLUDE_NULL_VALUES option in JSON, is it in beginning ? or should I use include or what is the exact syntax after schema ?
Thank you.
Michael.
this is how i used it>
FOR JSON AUTO, INCLUDE_NULL_VALUES, ROOT(‘operators’))