Json - With SQL Server


Json - With SQL Server

  • JSON can be used with SQL Server 2016 and SQL Azure databases.
  • Add FOR JSON clause to a SELECT statement along with explicit structure definition. 

Sample Query :

Select 
    ProductId as [Product.Id], 
    ProductName as [Product.Name], 
    Price as [Product.Price]
from 
wikiTechyProducts FOR JSON

Output :

[{
“Product”:
    {
        “Id” : “P001”,
        “Name” : “Samsung”,
        “Price” : 15000
    }
}]
  • PATH mode can be used with FOR JSON clause to determine the output format even for nested complex properties.

Sample Query :

Select 
    ProductId as [Product.Id], 
    ProductName as [Product.Name], 
    Price as [Product.Price]
from wikiTechyProducts FOR JSON PATH, ROOT(‘Products’)

Sample Output :

{
    “Products”:
[{
    “Product”:
{
   “Id” : “P001”,
   “Name” : “Samsung”,
   “Price” : 15000
},
   “Product”:
{
   “Id” : “P002”,
  “Name” : “Nokia”,
  “Price” : 13000
}
}]
  • AUTO mode with FOR JSON clause automatically formats the output based on the SELECT statement. 
  • It excludes any column with Null values.
  • The null column data can be made to appear with “INCLUDE_NULL_VALUES” clause.

Sample Query :

Select 
    ProductId, ProductName, Price 
from wikiTechyProducts
FOR JSON AUTO

Sample Output :

[
{
  “Id” : “P001”,
  “Name” : “Samsung”
},
{
  “Id” : “P002”,
  “Name” : “Nokia”,
  “Price” : 13000
}
}]

Note: Assuming that the price column data for “Samsung” row is null.