SQL SERVER：将查询结果导出为JSON对象

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

我正在使用Azure sql server并尝试以下列格式导出查询结果。

Required Query Result:

必需的查询结果：

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

{“results”：[{...}，{...}]，“回复”：0}

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

从这个例子：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".

我正在使用这个SQL，但我不知道如何将另一个响应属性添加为root属性的兄弟：“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" } ] }

{“结果”：[{“name”：“John”，“surname”：“Doe”}，{“name”：“Jane”，“surname”：“Doe”}}}

2 个解决方案

#1

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.

FOR JSON将做更难的部分（格式化表），你只需要包装它。

#2

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

使用FOR JSON PATH而不是FOR JSON AUTO。有关几个示例，请参阅格式化查询结果为JSON和FOR JSON（SQL Server）页面，包括点分隔列名称和来自SELECTS的查询

#1