FOR JSON Clause With AUTO mode In SQL Server 2016

Hi friends, in release of SQL Server 2016 CTP2 one of the feature that introduced is JSON clause.

So first question that comes in everyone’s mind is What is JSON?

JSON stands for JavaScript Object Notation. JSON is a lightweight format which is used for storing and interchange the data. JSON uses standard javascript function to convert JSON data into native javascript objects. The main purpose of using FOR JSON is used to create new JSON objects.

We can format the query results using FOR JSON clause in these ways:

1. With AUTO mode
2. With PATH mode
3. With ROOT option
4. Output with INCLUDE_NULL_VALUES option

In this blog we will discuss query formatting using FOR JSON clause with AUTO mode option.

Syntax for FOR JSON clause with AUTO option is like this:

FOR JSON AUTO

When AUTO option is used, the format of JSON is determined automatically on basis of number of columns present in SELECT statement list. A FROM clause is necessary in query with FOR JSON AUTO option.

When you join tables, columns present in first table are used as properties of root object in JSON array while column present in second table will automatically formatted as nested object within root object.

Let’s execute the below query and see the JSON output:

SELECT sp.BusinessEntityID,

            sp.TerritoryID,

            st.CountryRegionCode,

            st.[Group] TerrritoryGroup

            FROM sales.salesperson sp

             JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID

             WHERE sp.TerritoryID = 10

FOR JSON AUTO

After executing the above query we get the output in this format:

[
  {
       "BusinessEntityID":289,

       "TerritoryID":10,

       "st":
       [{

         "CountryRegionCode":"GB",

         "TerrritoryGroup":"Europe"
        }]
  }
]

Brackets [ ] represents JSON array in output.

Here in output we can see that table Sales.SalesTerritory is automatically formatted as nested object under parent object.

So we have generated formatted query output using JSON clause. I will continue with other ways of formatted output using JSON clause in my next blogs.

That’s all for the day folks.

Regards,

Kapil Singh Kumawat

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

Follow me on Twitter