Skip to content

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

Latest commit

 

History

History
69 lines (51 loc) · 2.5 KB

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

File metadata and controls

69 lines (51 loc) · 2.5 KB
title Include Null Values in JSON - INCLUDE_NULL_VALUES Option | Microsoft Docs
ms.custom
ms.date 06/02/2016
ms.prod sql
ms.reviewer genemi
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
manager craigg
monikerRange =azuresqldb-current||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current

Include Null Values in JSON - INCLUDE_NULL_VALUES Option

[!INCLUDEappliesto-ss-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 JSON in SQL Server and Azure SQL Database

Microsoft videos

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

See Also

FOR Clause (Transact-SQL)