如何从MS SQL 2014中的SQL查询制作JSON [英] How to make JSON from SQL query in MS SQL 2014

问题描述

问题:从MS SQL 2014中的SQL查询生成JSON的最佳解决方案是什么?我创建了一个过程，但是它很慢.

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.

我的例子:

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

推荐答案

只是为了好玩，我根据先前的回答创建了一个标量函数.

Just for fun, I created a scalar function based off of my prior answer.

除了显而易见的XML参数外，我还添加了两个附加功能:1)包括标头(如下所示)，以及2)ToLower大小写(我更喜欢使用小写的JSON字段名称来链接到我的类等).

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

如果查询不止一个记录，则将返回格式化的数组.

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','[e-mail]'),
(2,0,'Jane','Doe'  ,'[e-mail]')

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

返回

ID  Last_Name   First_Name  JSON
1   Smith       John        {"id":"1","active":"1","first_name":"John","last_name":"Smith","email":"[e-mail]"}
2   Doe         Jane        {"id":"2","active":"0","first_name":"Jane","last_name":"Doe","email":"[e-mail]"}

或更简单

Select JSON=[dbo].[udf-Str-JSON](0,1,(Select * From @Table for XML RAW))

在标题打开的情况下返回

{
    "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": "[e-mail]"
    }, {
        "id": "2",
        "active": "0",
        "first_name": "Jane",
        "last_name": "Doe",
        "email": "[e-mail]"
    }]
}

关闭标题后返回

[{
    "id": "1",
    "active": "1",
    "first_name": "John",
    "last_name": "Smith",
    "email": "[e-mail]"
}, {
    "id": "2",
    "active": "0",
    "first_name": "Jane",
    "last_name": "Doe",
    "email": "[e-mail]"
}]

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)

**编辑-修正错字

这篇关于如何从MS SQL 2014中的SQL查询制作JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！