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.
