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:

JSON_NULL_1

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

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

JSON_NULL_2

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

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