Skip to content

add-a-root-node-to-json-output-with-the-root-option-sql-server.md

Latest commit

 

History

History
175 lines (147 loc) · 4.06 KB

add-a-root-node-to-json-output-with-the-root-option-sql-server.md

File metadata and controls

175 lines (147 loc) · 4.06 KB
title Add a Root Node to JSON Output with the ROOT Option (SQL Server) | Microsoft Docs
ms.custom
ms.date 06/02/2016
ms.prod sql-non-specified
ms.prod_service database-engine, sql-database
ms.component json
ms.reviewer douglasl
ms.suite sql
ms.technology
dbe-json
ms.tgt_pltfrm
ms.topic article
helpviewer_keywords
ROOT (FOR JSON)
ms.assetid b9afa74a-f59f-483e-a178-42be2e9882c9
caps.latest.revision 16
author jovanpop-msft
ms.author jovanpop
manager craigg
ms.workload Inactive

Add a Root Node to JSON Output with the ROOT Option (SQL Server)

[!INCLUDEappliesto-ss-asdb-xxxx-xxx-md]

To add a single, top-level element to the JSON output of the FOR JSON clause, specify the ROOT option.

If you don't specify the ROOT option, the JSON output doesn't include a root element.

Examples

The following table shows the output of the FOR JSON clause with and without the ROOT option.

The examples in the following table assume that the optional RootName argument is empty. If you provide a name for the root element, this value replaces the value root in the examples.

Without the ROOT option

{  
   <<json properties>>  
}  
[  
   <<json array elements>>  
]

With the ROOT option

{   
  "root": {  
   <<json properties>>  
 }  
}  
{   
  "root": [  
   << json array elements >>  
  ]  
}

Here's another example of a FOR JSON clause with the ROOT option. This example specifies a value for the optional RootName argument.

Query

SELECT TOP 5   
       BusinessEntityID As Id,  
       FirstName, LastName,  
       Title As 'Info.Title',  
       MiddleName As 'Info.MiddleName'  
   FROM Person.Person  
   FOR JSON PATH, ROOT('info')

Result

{
	"info": [{
		"Id": 1,
		"FirstName": "Ken",
		"LastName": "Sánchez",
		"Info": {
			"MiddleName": "J"
		}
	}, {
		"Id": 2,
		"FirstName": "Terri",
		"LastName": "Duffy",
		"Info": {
			"MiddleName": "Lee"
		}
	}, {
		"Id": 3,
		"FirstName": "Roberto",
		"LastName": "Tamburello"
	}, {
		"Id": 4,
		"FirstName": "Rob",
		"LastName": "Walters"
	}, {
		"Id": 5,
		"FirstName": "Gail",
		"LastName": "Erickson",
		"Info": {
			"Title": "Ms.",
			"MiddleName": "A"
		}
	}]
}

Result (without root)

[{
	"Id": 1,
	"FirstName": "Ken",
	"LastName": "Sánchez",
	"Info": {
		"MiddleName": "J"
	}
}, {
	"Id": 2,
	"FirstName": "Terri",
	"LastName": "Duffy",
	"Info": {
		"MiddleName": "Lee"
	}
}, {
	"Id": 3,
	"FirstName": "Roberto",
	"LastName": "Tamburello"
}, {
	"Id": 4,
	"FirstName": "Rob",
	"LastName": "Walters"
}, {
	"Id": 5,
	"FirstName": "Gail",
	"LastName": "Erickson",
	"Info": {
		"Title": "Ms.",
		"MiddleName": "A"
	}
}]

Learn more about JSON in SQL Server and Azure SQL Database

Microsoft blog posts

For specific solutions, use cases, and recommendations, see these blog posts about the built-in JSON support 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)