SQL SERVER: Export Query RESULT as JSON Object

Question

I am using Azure sql server and trying to export results of a query in the following format.

Required Query Result:

{ "results": [{...},{...}], "response": 0 }

From this example : https://msdn.microsoft.com/en-us/library/dn921894.aspx

I am using this sql but I am not sure how to add another response property as a sibling to the root property :"results".

Current Query:

SELECT name, surname
FROM emp
FOR JSON AUTO, ROOT('results')

Output of Query:

{ "results": [ { "name": "John", "surname": "Doe" }, { "name": "Jane", "surname": "Doe" } ] }

Answer 1

Use FOR JSON PATH instead of FOR JSON AUTO. See the Format Query Results as JSON with FOR JSON (SQL Server) page for several examples, including dot-separated column names and queries from SELECTS

Answer 2

There is no built-in option for this format, so maybe the easiest way would be to manually format response, something like:

declare @resp nvarchar(20) = '20'
SELECT '{"response":"' +
         (SELECT * FROM emp FOR JSON PATH) +
         '", "response": ' + @resp + ' }' 

FOR JSON will do harder part (formatting table) and you just need to wrap it.