In previous blog we learnt about changing query format using JSON clause with AUTO mode option. In this blog we will learn formatting of query output using FOR JSON clause with PATH mode in SQL Server 2016.
Syntax for FOR JSON clause with PATH option is like this:
FOR JSON PATH
FOR JSON PATH clause is used to generate hierarchical structure of JSON objects. When you select rows from a query, the results are formatted as an array of JSON objects. By default, null are not included in output. Unlike AUTO option we can format our own output using PATH option.
Let’s execute the below query to see the JSON PATH output:
SELECT sp.BusinessEntityID [Person.Entity], sp.TerritoryID [Person.Territory], st.CountryRegionCode [Territory.Code], st.[Group] [Territory.Group] FROM sales.salesperson sp JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID WHERE sp.TerritoryID = 10 FOR JSON PATH
So we get the output in this format:
[{
"Person":
{
"Entity":289,
"Territory":10
},
"Territory":
{
"Code":"GB",
"Group":"Europe"
}
}]
As we can see in output that (.) is used as path separator for JSON paths. Brackets ([ ]) or apostrophes (‘) is used to encapsulates the alias in the query. In above query output Entity, Territory placed under Person object and Code, Group placed under Territory object.
So friends today, we have learnt how to create formatted output with FOR JSON clause using PATH option. I will write more on JSON clause in my next blogs.
Hope you like this post. Keep learning.
Regards,
Kapil Singh Kumawat
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
