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"
}]

See also

• SELECT - FOR