How to produce JSON strings from SQL Server queries via TSQL?

Question

I wanted to know if there is any function or something to convert the SQL select query result to JSON string format?

For example, SQL select query result is,

current   target
-----------------
  500      1000
  1500     2000

JSON result:

[{"current":500,"target":1000},{"current":1500,"target":2000}]

Any ideas will be helpful.

Thanks.

Duplicate? http://stackoverflow.com/questions/2312982/from-datatable-in-c-sharp-net-to-json — GVashist, Dec 10 '14 at 21:18
have a look here https://www.simple-talk.com/sql/t-sql-programming/producing-json-documents-from-sql-server-queries-via-tsql/ — Giorgos Betsos, Dec 10 '14 at 21:19

score 5 · Accepted Answer · answered Dec 10 '14 at 21:37

5

SQL Fiddle

MS SQL Server 2008 Schema Setup:

Query 1:

DECLARE @TABLE TABLE ([current] INT, [target] INT)
INSERT INTO @TABLE VALUES 
(500   ,   1000),
(1500  ,   2000)


SELECT '[' +  STUFF((SELECT ',{"current":' + CAST([current] AS VARCHAR(30)) 
   + ',"target":' + CAST([target] AS VARCHAR(30)) + '}'
FROM @TABLE
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'') + ']'

Results:

[{"current":500,"target":1000},{"current":1500,"target":2000}]

answered Dec 10 '14 at 21:37

M.Ali

67,945
13
101
127

I would update code to include the inner quotes: ``+ CAST([target] AS VARCHAR(30)) +`` to ``+ '"' + CAST([target] AS VARCHAR(30)) + '"'`` – Ravi Ram Jun 15 '17 at 04:16

Martin Smith · Answer 2 · 2015-05-27T21:55:52.373

5

You don't specify version.

In SQL Server 2016 you will be able to do something like

SELECT [current], 
       target
FROM YourTable
ORDER BY [current]
FOR JSON AUTO;

More details here or in the official pre release documentation

edited May 27 '15 at 21:55

answered May 16 '15 at 22:34

Martin Smith

438,706
87
741
845

Xenoranger · Answer 3 · 2023-02-03T20:43:15.937

0

I use

SELECT
    JSON_QUERY(( SELECT
                  [current],target 
                  FROM YourTable
                  FOR JSON PATH
               ))

Works well with minimal effort. I generally convert the output to a List<Dictionary<string,dynamic>> in C#/.Net (if I don't have an existing model).

edited Feb 03 '23 at 20:43

answered Feb 03 '23 at 20:37

Xenoranger

421
5
22

How to produce JSON strings from SQL Server queries via TSQL?

3 Answers3