将选择查询结果转换为 Json

Question

我想在表上执行选择查询，我需要 JSON 格式或 excel 表 中的结果。我只想使用查询来执行此操作，并且我正在使用 SQL Server 2014 。

这是表模式

CREATE TABLE TestTable
(

   Id int primary key identity(1,1),
   Name nvarchar(200),
   About nvarchar(2000),
   Age int,
   AddressId int
 )

我需要从 Id、Name、About 和 Age 中获取值到 JSON 列表中

原文由 Sajith v 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

JSON AUTO 可以快速解决此问题，但 JSON 支持仅在 SQL Server 2016 及更高版本中可用，包括 Azure SQL 数据库。对于 SQL 2014 及更早版本中的 T-SQL 解决方案，您需要自己构建 JSON 字符串。

下面是一个使用 FOR XML 子查询将结果连接为 JSON 格式并为数组添加最外层的 [ 和 ] 的示例。请注意，这不处理必须在 JSON 中转义的字符（ \ 和 " ）所以你需要使用 REPLACE 来转义那些如果包含在你的数据。

 SELECT '[' + STUFF((
    SELECT
          ',{'
        + '"Id":'+CAST(Id AS varchar(10)) + ','
        + COALESCE('"Name":"' + Name + '",','')
        + COALESCE('"About":"' + About + '",','')
        + COALESCE('"Age":'+CAST(Age AS varchar(10)) + ',','')
        + COALESCE('"AddressId":'+CAST(AddressId AS varchar(10)), '')
        + '}'
    FROM TestTable
    FOR XML PATH(''), TYPE).value('.', 'varchar(MAX)'),1,1,'')
    + ']';

原文由 Dan Guzman 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

对于 SQL 服务器 2017

 CREATE TABLE mytable(
  ID int PRIMARY KEY,
  Name varchar(50),
  teamName varchar(255),
  Create_Date DATETIME
);

INSERT INTO mytable VALUES (1,NULL,'TEST1','2017-01-02');
INSERT INTO mytable VALUES (2,NULL,'TEST2',NULL);
INSERT INTO mytable VALUES (3,'KK','TEST3','2017-01-02');
INSERT INTO mytable VALUES (4,NULL,NULL,NULL);

试试下面的方法我提供一个例子

SELECT
       ID,
       Name,
       teamName,
       Create_Date
   FROM mytable
   FOR JSON AUTO

http://www.sqlfiddle.com/#!18/81350/1

 JSON_F52E2B61-18A1-11d1-B105-00805F49916B
[{"ID":1,"teamName":"TEST1","Create_Date":"2017-01-02T00:00:00"},{"ID":2,"teamName":"TEST2"},{"ID":3,"Name":"KK","teamName":"TEST3","Create_Date":"2017-01-02T00:00:00"},{"ID":4}]

对于以下版本的 sql 2017 服务器：

1st创建一个缩放功能

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

然后使用这个函数json转换下面查询就是一个例子

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

原文由 Zaynul Abadin Tuhin 发布，翻译遵循 CC BY-SA 4.0 许可协议