include-null-values-in-json-include-null-values-option.md

description	Include Null Values in JSON - INCLUDE_NULL_VALUES Option
title	Include Null Values in JSON - INCLUDE_NULL_VALUES Option
ms.date	06/03/2020
ms.prod	sql
ms.technology
ms.topic	conceptual
helpviewer_keywords	INCLUDE_NULL_VALUES (FOR JSON)
ms.assetid	06873768-3778-4ed8-a1db-61758726bda0
author	jovanpop-msft
ms.author	jovanpop
ms.reviewer	jroth
ms.custom	seo-dt-2019
monikerRange	=azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

Include Null Values in JSON - INCLUDE_NULL_VALUES Option

[!INCLUDE SQL Server Azure SQL Database]

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	With the INCLUDE_NULL_VALUES option
{ "name": "John", "surname": "Doe" }	{ "name": "John", "surname": "Doe", "age": null, "phone": null }

Here's another example of a FOR JSON clause with the INCLUDE_NULL_VALUES option.

Query

SELECT name, surname  
FROM emp  
FOR JSON AUTO, INCLUDE_NULL_VALUES    

Result

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

Learn more about JSON in SQL Server and Azure SQL Database

Microsoft videos

Note

Some of the video links in this section may not work at this time. Microsoft is migrating content formerly on Channel 9 to a new platform. We will update the links as the videos are migrated to the new platform.

For a visual introduction to the built-in JSON support in SQL Server and Azure SQL Database, see the following videos:

• JSON as a bridge between NoSQL and relational worlds

See Also

FOR Clause (Transact-SQL)