Include Null Values in JSON with the INCLUDE_NULL_VALUES Option

Contents

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#

Contents