This article is half-done without your Comment! *** Please share your thoughts via Comment ***
SQL Server 2016 introduced compelling data type called JSON data type. It also added a variety of new operators and functions related to JSON data type.
This is one of the interesting topics for us, and I have recently published a series of articles on SQL Server JSON Data type.
SQL Server 2016: Introduced JSON support, to store and retrieve JSON document
In this post, I am sharing one of the relevant queries to convert SQL Server tabular data into JSON formatted data.
We are storing JSON formatted data into database and access base on the different filters.
Here, I am sharing one type of utility script to convert your table data into JSON formatted data.
Sometimes it requires populating JSON formatted data for a web service purpose.
Create a sample table with data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE TABLE tbl_Employees ( EmpID INT ,EmpName VARCHAR(10) ,EmpSalary INT ,EmpDOJ DATE ) GO INSERT INTO tbl_Employees VALUES (1,'Anvesh',150000,'2017-01-01') ,(2,'Neevan',125000,'2016-01-01') ,(3,'Martin',90000,'2014-01-01') ,(4,'Roy',175000,'2014-05-08') ,(5,'Maria',60000,'2016-08-10') ,(6,'Jenny',40000,'2016-11-05') GO |
Use FOR JSON AUTO, and generate the JSON formatted data:
|
1 2 3 4 5 6 7 |
SELECT EmpID ,EmpName ,EmpSalary ,EmpDOJ FROM tbl_Employees FOR JSON AUTO |
Result:
|
1 2 3 4 5 6 7 8 |
[ {"EmpID":1,"EmpName":"Anvesh","EmpSalary":150000,"EmpDOJ":"2017-01-01"} ,{"EmpID":2,"EmpName":"Neevan","EmpSalary":125000,"EmpDOJ":"2016-01-01"} ,{"EmpID":3,"EmpName":"Martin","EmpSalary":90000,"EmpDOJ":"2014-01-01"} ,{"EmpID":4,"EmpName":"Roy","EmpSalary":175000,"EmpDOJ":"2014-05-08"} ,{"EmpID":5,"EmpName":"Maria","EmpSalary":60000,"EmpDOJ":"2016-08-10"} ,{"EmpID":6,"EmpName":"Jenny","EmpSalary":40000,"EmpDOJ":"2016-11-05"} ] |
Use ROOT option, and generate the JSON formatted data:
|
1 2 3 4 5 6 7 |
SELECT EmpID ,EmpName ,EmpSalary ,EmpDOJ FROM tbl_Employees FOR JSON PATH, ROOT ('Employee_Root') |
Result:
|
1 2 3 4 5 6 7 8 9 10 |
{"Employee_Root": [ {"EmpID":1,"EmpName":"Anvesh","EmpSalary":150000,"EmpDOJ":"2017-01-01"} ,{"EmpID":2,"EmpName":"Neevan","EmpSalary":125000,"EmpDOJ":"2016-01-01"} ,{"EmpID":3,"EmpName":"Martin","EmpSalary":90000,"EmpDOJ":"2014-01-01"} ,{"EmpID":4,"EmpName":"Roy","EmpSalary":175000,"EmpDOJ":"2014-05-08"} ,{"EmpID":5,"EmpName":"Maria","EmpSalary":60000,"EmpDOJ":"2016-08-10"} ,{"EmpID":6,"EmpName":"Jenny","EmpSalary":40000,"EmpDOJ":"2016-11-05"} ] } |
Leave a Reply