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?
We can format the query results using FOR JSON clause in these ways:
- With AUTO mode
- With PATH mode
- With ROOT option
- 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:
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:
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.
Kapil Singh Kumawat