FOR JSON Clause With AUTO Mode In SQL Server 2016

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.

In the release of SQL Server 2016 CTP 2 one of the features that was introduced is JSON clause.

So, the first question that comes into everyone’s mind is What is JSON?

JSON stands for JavaScript Object Notation. JSON is a lightweight format which is used for storing and interchanging the data. JSON uses standard JavaScript functions to convert the 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 the 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 the basis of the number of columns present in the SELECT statement list. A FROM clause is necessary inquery with FOR JSON AUTO option.

When you join tables, columns present in the first table are used as properties of the root object in JSON array while columns present in the second table will be automatically formatted as a nested object within the 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 the output, we can see that table Sales.SalesTerritory is automatically formatted as a nested object under parent object.

So we have generated a 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, folks.