SQL Server 2017 Developer's Guide

Include NULL values in the JSON output

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":"[e-mail]",
    "PhoneNumber":"[phone]"
  },
  {
    "PersonID":3,
    "FullName":"Hudson Onslow",
    "EmailAddress":"[e-mail]", ...