Include Null Values in JSON with the INCLUDE_NULL_VALUES Option#
To include null values in the JSON output of the FOR JSON clause, specify the INCLUDE_NULL_VALUES option.
If you don’t specify the INCLUDE_NULL_VALUES option, the JSON output doesn’t include properties for values that are null in the query results.
Examples#
The following example shows the output of the FOR JSON clause with and without the INCLUDE_NULL_VALUES option.
Without the INCLUDE_NULL_VALUES option:
{ "name": "John", "surname": "Doe" }
With the INCLUDE_NULL_VALUES option:
{ "name": "John", "surname": "Doe", "age": null, "phone": null }
Here’s another example of a FOR JSON* clause with the *INCLUDE_NULL_VALUES option.
SELECT name, surname
FROM emp
FOR JSON AUTO, INCLUDE_NULL_VALUES
Result
[{
"name": "John",
"surname": null
}, {
"name": "Jane",
"surname": "Doe"
}]