SQL server table data into JSON

Question

I have a data table stored in my sql server and i wanna convert it into JSON for creating a chart from the JSON data(using D3JS). but for now I only want to convert sql server data into JSON. suppose my table name is salary (EmpName, Salary) now my query is how to parse this data table into JSON for further use.

You would be much better off doing this in your client layer than trying to do it in T-SQL — Alex K.
– Alex K., Commented Aug 18, 2014 at 11:55
I agree with @AlexK. this could possibly be done with some horrible select queries or some custom sql library but it'd be far more sensible to do it in your BLL. Perhaps some more context would help here? — Liath
– Liath, Commented Aug 18, 2014 at 11:57
you can convert the data in your client to JSON by using json library james.newtonking.com/json — krishna_v
– krishna_v, Commented Aug 18, 2014 at 12:10

np_6 · Accepted Answer · 2021-04-26 03:23:48Z

1

One of the top feature of MSSQL server 2016 is built in support of JSON. You can write a very simple query now to Format Query Results as JSON in MSSQL by using FOR JSON in the select statement. Checkout the below example.

Using PATH mode with the FOR JSON Clause

SELECT SalesOrderNumber AS 'Order.Number',
       OrderDate AS 'Order.Date',
       UnitPrice AS 'Product.Price',
       OrderQty AS 'Product.Quantity'
FROM Sales.SalesOrderHeader H
  INNER JOIN Sales.SalesOrderDetail D
    ON H.SalesOrderID = D.SalesOrderID
FOR JSON PATH

Output of FOR JSON with Path mode

[
  {
    "Order":{
        "Number":"SO43659",
        "Date":"2011-05-31T00:00:00"
      },
    "Product":{
         "Price":2024.9940,
         "Quantity":1
     }
  },
  {
    "Order":{ "Number":"SO43659" },
    "Product":{"Price":2024.9940}
  }
]

edited Apr 26, 2021 at 3:23

np_6

5081 gold badge7 silver badges20 bronze badges

answered Dec 5, 2015 at 20:22

user5642061

Sign up to request clarification or add additional context in comments.

Comments

Eralper · Accepted Answer · 2015-09-07 08:54:54Z

0

SQL Server 2016 has support for JSON for programmers, convert data from SQL to JSON using FOR JSON AUTO and FOR JSON PATH syntax SQL support from JSON to SQL data will be available with release of CTP3

answered Sep 7, 2015 at 8:54

Eralper

6,6402 gold badges23 silver badges28 bronze badges

Comments

Jovan MSFT · Accepted Answer · 2015-09-23 21:38:08Z

0

FOR JSON is already available in Sql Server 2016 CTP2 see https://msdn.microsoft.com/en-us/library/dn921882(v=sql.130).aspx

answered Sep 23, 2015 at 21:38

Jovan MSFT

14.8k4 gold badges44 silver badges59 bronze badges

Comments

Jens Frandsen · Accepted Answer · 2016-05-28 00:34:26Z

0

Using MSSLQ SERVER 2016

select
  EmpName as empName, 
  Salary as salary,
from 
 salary
for json path, root('salaries')

returns

{"salaries":[
  {"empName":"John","salary":50000},
  {"empName":"Mary","salary":60000}
]}

If you had to do it useing pre-2016, there are a variety of methods, none of them very good in my opinion.

answered May 28, 2016 at 0:34

Jens Frandsen

8629 silver badges13 bronze badges

Collectives™ on Stack Overflow

SQL server table data into JSON

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related