如何从 MS SQL 2014 中的 SQL 查询生成 JSON
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39883243/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to make JSON from SQL query in MS SQL 2014
提问by Joachim Langezaal
Question: What is best solution to generate JSON from a SQL query in MS SQL 2014? I created a procedure, but it is very slow.
问题：从 MS SQL 2014 中的 SQL 查询生成 JSON 的最佳解决方案是什么？我创建了一个程序，但是速度很慢。
My Example:
我的例子：
DECLARE @customers xml;
DECLARE @json NVARCHAR(max);
SET @customers = (SELECT * FROM dbo.Customers FOR XML path, root)
EXEC [dbo].[HTTP_JSON] @customers, @json
EXEC [dbo].[HTTP_JSON](@Shopping)
Create PROCEDURE [dbo].[HTTP_JSON]
@parameters xml, @response NVARCHAR(max) OUTPUT
WITH EXEC AS CALLER
AS
set @response = (SELECT Stuff(
(SELECT * from
(SELECT ',
{'+
Stuff((SELECT ',"'+coalesce(b.c.value('local-name(.)', 'NVARCHAR(MAX)'),'')+'":"'+
b.c.value('text()[1]','NVARCHAR(MAX)') +'"'
from x.a.nodes('*') b(c)
for xml path(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)')
,1,1,'')+'}'
from @parameters.nodes('/root/*') x(a)
) JSON(theLine)
for xml path(''),TYPE).value('.','NVARCHAR(MAX)' )
,1,1,''))
GO
回答by John Cappelletti
Just for fun, I created a scalar function based off of my prior answer.
只是为了好玩，我根据我之前的答案创建了一个标量函数。
Aside from the obvious XML parameter, I added two additional: 1) Include Header (illustrated below), and 2) ToLower case (I prefer my JSON field names in lower case which links to my classes and such).
除了明显的 XML 参数之外，我还添加了两个额外的参数：1) Include Header（如下所示），以及 2) ToLower case（我更喜欢小写的 JSON 字段名称，它链接到我的类等）。
If the query is more than one record, a formatted array will be returned.
如果查询不止一条记录，则返回一个格式化的数组。
Declare @Table table (ID int,Active bit,First_Name varchar(50),Last_Name varchar(50),EMail varchar(50))
Insert into @Table values
(1,1,'John','Smith','[email protected]'),
(2,0,'Jane','Doe' ,'[email protected]')
Select A.ID
,A.Last_Name
,A.First_Name
,B.JSON
From @Table A
Cross Apply (Select JSON=[dbo].[udf-Str-JSON](0,1,(Select A.* For XML Raw)) ) B
Returns
退货
ID Last_Name First_Name JSON
1 Smith John {"id":"1","active":"1","first_name":"John","last_name":"Smith","email":"[email protected]"}
2 Doe Jane {"id":"2","active":"0","first_name":"Jane","last_name":"Doe","email":"[email protected]"}
Or even more simply
或者更简单
Select JSON=[dbo].[udf-Str-JSON](0,1,(Select * From @Table for XML RAW))
Returns with Header ON
返回标题打开
{
"status": {
"successful": "true",
"timestamp": "2016-10-09 06:08:16 GMT",
"rows": "2"
},
"results": [{
"id": "1",
"active": "1",
"first_name": "John",
"last_name": "Smith",
"email": "[email protected]"
}, {
"id": "2",
"active": "0",
"first_name": "Jane",
"last_name": "Doe",
"email": "[email protected]"
}]
}
Returns with Header Off
关闭标题返回
[{
"id": "1",
"active": "1",
"first_name": "John",
"last_name": "Smith",
"email": "[email protected]"
}, {
"id": "2",
"active": "0",
"first_name": "Jane",
"last_name": "Doe",
"email": "[email protected]"
}]
The UDF
UDF
ALTER FUNCTION [dbo].[udf-Str-JSON] (@IncludeHead int,@ToLowerCase int,@XML xml)
Returns varchar(max)
AS
Begin
Declare @Head varchar(max) = '',@JSON varchar(max) = ''
; with cteEAV as (Select RowNr=Row_Number() over (Order By (Select NULL))
,Entity = xRow.value('@*[1]','varchar(100)')
,Attribute = xAtt.value('local-name(.)','varchar(100)')
,Value = xAtt.value('.','varchar(max)')
From @XML.nodes('/row') As R(xRow)
Cross Apply R.xRow.nodes('./@*') As A(xAtt) )
,cteSum as (Select Records=count(Distinct Entity)
,Head = IIF(@IncludeHead=0,IIF(count(Distinct Entity)<=1,'[getResults]','[[getResults]]'),Concat('{"status":{"successful":"true","timestamp":"',Format(GetUTCDate(),'yyyy-MM-dd hh:mm:ss '),'GMT','","rows":"',count(Distinct Entity),'"},"results":[[getResults]]}') )
From cteEAV)
,cteBld as (Select *
,NewRow=IIF(Lag(Entity,1) over (Partition By Entity Order By (Select NULL))=Entity,'',',{')
,EndRow=IIF(Lead(Entity,1) over (Partition By Entity Order By (Select NULL))=Entity,',','}')
,JSON=Concat('"',IIF(@ToLowerCase=1,Lower(Attribute),Attribute),'":','"',Value,'"')
From cteEAV )
Select @JSON = @JSON+NewRow+JSON+EndRow,@Head = Head From cteBld, cteSum
Return Replace(@Head,'[getResults]',Stuff(@JSON,1,1,''))
End
-- Parameter 1: @IncludeHead 1/0
-- Parameter 2: @ToLowerCase 1/0 (converts field name to lowercase
-- Parameter 3: (Select * From ... for XML RAW)
**EDIT - Corrected Typo
**编辑 - 更正错别字
回答by John Cappelletti
The following should create the JSON array for just about any data set. However, I have not created a way to convert bit to true/false yet.
以下应该为几乎任何数据集创建 JSON 数组。但是，我还没有创建将位转换为真/假的方法。
Just one point to consider: The FIRST column in the initial SELECT has to be the Primary Key which is equates to the ENTITY field. In this case, Select * from @User for XML RAW... ID is the Entity and just so happens to be the first field in the table
只需要考虑一点：初始 SELECT 中的 FIRST 列必须是主键，它等同于 ENTITY 字段。在这种情况下，Select * from @User for XML RAW... ID 是实体，恰好是表中的第一个字段
As far as performance, 500 records with 19 fields creates a JSON string 191,987 bytes in 0.694 seconds (50 records in 0.098 seconds)
就性能而言，具有 19 个字段的 500 条记录在 0.694 秒内创建了一个 191,987 字节的 JSON 字符串（50 条记录在 0.098 秒内）
Consider the following:
考虑以下：
Declare @User table (ID int,Active bit,First_Name varchar(50),Last_Name varchar(50),EMail varchar(50),LastOn DateTime)
Insert into @User values
(1,1,'John','Smith','[email protected]','2016-10-05 17:32:41.903'),
(2,0,'Jane','Doe' ,'[email protected]','2016-10-05 08:25:18.203')
Declare @XML xml = (Select * From @User for XML RAW)
Declare @JSON varchar(max) = ''
;with cteEAV as (
Select RowNr = Row_Number() over (Order By (Select NULL))
,Entity = xRow.value('@*[1]','varchar(100)')
,Attribute = xAtt.value('local-name(.)','varchar(100)')
,Value = xAtt.value('.','varchar(max)')
From @XML.nodes('/row') As A(xRow)
Cross Apply A.xRow.nodes('./@*') As B(xAtt) )
,cteBld as (
Select *
,NewRow = IIF(Lag(Entity,1) over (Partition By Entity Order By (Select NULL))=Entity,'',',{')
,EndRow = IIF(Lead(Entity,1) over (Partition By Entity Order By (Select NULL))=Entity,',','}')
,JSON = Concat('"',Attribute,'":','"',Value,'"')
From cteEAV )
Select @JSON = @JSON+NewRow+JSON+EndRow
From cteBld
Select '['+Stuff(@JSON,1,1,'')+']'
Returns
退货
[{"ID":1, "Active":1, "First_Name":"John", "Last_Name":"Smith", "EMail":"[email protected]", "LastOn":"2016-10-05T17:32:41.903", "TotalSales":25569.0000} ,{"ID":2, "Active":0, "First_Name":"Jane", "Last_Name":"Doe", "EMail":"[email protected]", "LastOn":"2016-10-05T08:25:18.203", "TotalSales":22888.0000}]
A more readable version
更易读的版本
cteEAV will dynamically unpivot the data and generate the following:
cteEAV 将动态取消数据透视并生成以下内容：
cteBLD will extend and add flags New/End Row
cteBLD 将扩展并添加标志 New/End Row
The Final Select
最终选择
This will put it all together and generate one final string which can be wrapped or nested as you please.
这会将它们放在一起并生成一个最终字符串，可以根据需要包装或嵌套。
