How to Include Null Values in JSON Output in MSSQL

One of the top features of MSSQL server 2016 is built in support of JSON. The data from the MSSQL can be exported to JSON by adding the FOR JSON clause into a SELECT statement. However by default, this statement will not return any NULL values in the output result. However, we can Include Null Values in JSON Output in MSSQLInclude Null Values in JSON Output in MSSQL just by adding a keyword in the select query.

If you would like to include Null Values in JSON output, specify the INCLUDE_NULL_VALUES option in the statement.

Example of How to Include Null Values in JSON Output in MSSQL

SELECT name, surname
FROM emp
FOR JSON AUTO, INCLUDE_NULL_VALUES

Output of JSON data with NULL values

[ 
   {"name": "John",  "surname": null },
   {"name": "Jane",  "surname": "Doe"}
]