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

title	Include Null Values in JSON - INCLUDE_NULL_VALUES Option | Microsoft Docs
ms.custom	SQL2016_New_Updated
ms.date	06/02/2016
ms.prod	sql-server-2016
ms.reviewer
ms.suite
ms.technology	dbe-json
ms.tgt_pltfrm
ms.topic	article
helpviewer_keywords	INCLUDE_NULL_VALUES (FOR JSON)
ms.assetid	06873768-3778-4ed8-a1db-61758726bda0
caps.latest.revision	14
author	douglaslMS
ms.author	douglasl
manager	craigg

Include Null Values in JSON - INCLUDE_NULL_VALUES Option

[!INCLUDEtsql-appliesto-ss2016-asdb-xxxx-xxx_md]

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 the built-in JSON support in SQL Server

For lots of specific solutions, use cases, and recommendations, see the blog posts about the built-in JSON support in SQL Server and in Azure SQL Database by Microsoft Program Manager Jovan Popovic.

See Also

FOR Clause (Transact-SQL)