FOR JSON Clause Mode With ROOT Option In SQL Server 2016

Hi friends, we will continue with FOR JSON clause in this blog and today we will learn formatting of query using FOR JSON clause mode with ROOT option in SQL Server 2016.

As per BOL we need to specify ROOT option in query, when we want to add a single, top level element to the JSON output of FOR JSON clause. JSON output will not have a root element until we specify the root option.

Syntax for ROOT option is:

ROOT [(‘RootName’)]

If we don’t provide the name of root element then it will take default value as root.

Let’s execute the below query to see the JSON ROOT option output:

SELECT BusinessEntityID, SalesYTD, TerritoryId, SalesQuota

FROM Sales.SalesPerson

WHERE BusinessEntityId = 285

FOR JSON AUTO, ROOT ('Sales')

JSON_Root

Here in output we can see that Sales is the root element.

{

“Sales”:

[{

“BusinessEntityID”:285,

“SalesYTD”:172524.4512

}]

}

 

That’s all for the day folks.

Regards,

Kapil Singh Kumawat

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook 

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Kapil Singh Kumawat

Kapil Singh Kumawat has been working with SQL Server since last 5 years. He is from Jaipur, Rajasthan, India and currently working with Cognizant Technology Solutions as SQL Server Developer. He has good experience in performance tuning, SSIS, data migration and data designing. Apart from database he has interest in travelling, watching football and listening music.

View all posts by Kapil Singh Kumawat →

Leave a Reply

Your email address will not be published. Required fields are marked *