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:

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

JSON_Path_1

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 

Follow me on 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 *