Native JSON support (SQL 2016)

The new release of SQL server has taken further steps and added native JSON support. JSON lately become one of important part of any developer’s toolkit. Recently it become clear that sites are start sharing data using JSON in addition to RSS feeds. JSON feeds can be loaded asynchronously much more easily than XML/RSS

JSON (JavaScript Object Notation) outputs human-readable collection of data that consists name/value pairs with individual values separated by comma. Objects are containers of one or more name/value pairs and are contained within curly brackets as shown below.

For instance it will store data as

var Product = {

        "ProductId" : "24",

        "ProductName" : "Lego Jurassic World",

        "Price" : "$30.00"

        };

Appending FOR JSON AUTO to a standard SELECT statement returns the result set in a JSON format. JSON arrays can contain multiple objects, and arrays are contained within square brackets.  Note here JSON is not treated as another data type in SQL server like XML.

Let us now create a table in SQL server and insert some data and output the result.

create table products (productId int primary key not null, productName nvarchar(100), Price money)

go

insert into products(productId, productName, Price)

values (1, 'Terraria Xbox 360', 12),

       (2, 'Batman Arkham Origins',6),

          (3, 'FIFA 16', 40)

go

select productId, ProductName, Price from [dbo].[Products]

FOR JSON PATH , ROOT ('PRODUCTS')

The result of the above select statement is.

{"PRODUCTS":[{"productId":1,"ProductName":"Terraria Xbox 360","Price":12.0000},

                 {"productId":2,"ProductName":"Batman Arkham Origins","Price":6.0000},

                 {"productId":3,"ProductName":"FIFA 16","Price":40.0000}]}

 A simple T-SQL construct with “FOR JSON PATH” outputs JSON output that can be feed to web site.