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.
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,'') + ']'
[{"current":500,"target":1000},{"current":1500,"target":2000}]
+ CAST([target] AS VARCHAR(30)) + to + '"' + CAST([target] AS VARCHAR(30)) + '"'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
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).
