CODEWITHSUNDEEP
sql-serverjsont-sql
Arpita
Arpita

Reputation: 455

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

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.

Upvotes: 5

Views: 9370

Answers (3)

Xenoranger
Xenoranger

Reputation: 518

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).

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453057

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

Upvotes: 5

M.Ali
M.Ali

Reputation: 69514

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}]

Upvotes: 5

Related Questions