FOR JSON Clause With PATH Mode In SQL Server 2016

FOR JSON Clause With PATH Mode In SQL Server 2016

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:

Transact-SQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

SELECTsp.BusinessEntityID[Person.Entity],

sp.TerritoryID[Person.Territory],

st.CountryRegionCode[Territory.Code],

st.[Group][Territory.Group]

FROMsales.salespersonsp

JOINSales.SalesTerritorystONsp.TerritoryID=st.TerritoryID

WHEREsp.TerritoryID=10

FORJSONPATH

So we get the output in this format:

Transact-SQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

[{

"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.

Share This Story, Choose Your Platform!

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.