As you can see in the preceding example, the JSON output does not map a column to a JSON property if the column value is NULL. To include null values in the JSON output, you can specify the INCLUDE_NULL_VALUES option. Let's apply it to our initial example:
SELECT TOP (3) PersonID, FullName, EmailAddress, PhoneNumber FROM Application.People ORDER BY PersonID ASC FOR JSON AUTO, INCLUDE_NULL_VALUES;
Let's observe the result:
[ { "PersonID":1, "FullName":"Data Conversion Only", "EmailAddress":null, "PhoneNumber":null }, { "PersonID":2, "FullName":"Kayla Woodcock", "EmailAddress":"[email protected]", "PhoneNumber":"(415) 555-0102" }, { "PersonID":3, "FullName":"Hudson Onslow", "EmailAddress":"[email protected]", "PhoneNumber":"(415) 555-0102" } ]
Now each element has all properties listed even if they don't have a value. This option is similar to the XSINIL option used with the ELEMENTS directive in the case of FOR XML AUTO.