在SQL Server中将查询输出转换为JSON格式 [英] convert query output into json format in sql server

问题描述

我想将以下查询输出显示为JSON格式(必需的输出格式)

I want to display the below query output into JSON Format(Required output format)

select ApplicationID ApplicationID ,Roleid Roles from UserRoles where userid=11 and applicationid is not null 

Output

ApplicationID  Roles
1                1
1                5
3                5

我想以以下格式显示我正在使用sql server 2016的

i want to display below format i am using sql server 2016

Required output Format:
[{"ApplicationID":1,"Roles":[1,5]},{"ApplicationID":3,"Roles":[5]}]

推荐答案

使用纯 T-SQL-JSON-support 获得的最接近的东西是:

The closest you get with pure T-SQL-JSON-support will be this:

DECLARE @tbl TABLE(ApplicationID INT,  Roles INT);
INSERT INTO @tbl VALUES
 (1,1)
,(1,5)
,(3,5);

SELECT t.ApplicationID
      ,Roles.Roles AS Roles  
FROM @tbl t
INNER JOIN @tbl Roles ON Roles.ApplicationID=t.ApplicationID
GROUP BY t.ApplicationID,Roles.Roles
FOR JSON AUTO;

结果

[{"ApplicationID":1
 ,"Roles":[{"Roles":1}
          ,{"Roles":5}]
 }
,{"ApplicationID":3
 ,"Roles":[{"Roles":5}]}
]

在AUTO模式下，引擎将查看"联接并将其打包到对象数组.

In AUTO mode the engine will "see" the join and pack this into an array of objects.

遗憾的是，FOR JSON不支持裸数组([1,2,3]).您将总是得到对象数组，如[{Prop:Val},{Prop:Val},{Prop:Val}] ...

Regretfully FOR JSON does not support naked arrays ([1,2,3]). You will always get arrays of objects like [{Prop:Val},{Prop:Val},{Prop:Val}]...

但是您可以通过相关的子查询和一些字符串聚合来解决这个问题(我们需要JSON_QUERY()以避免在数组周围加引号):

But you can out-trick this with a correlated sub-query and some string aggregation (We need JSON_QUERY() to avoid quotes around the array):

SELECT t.ApplicationID
      ,JSON_QUERY('[' + STUFF((
        SELECT CONCAT(',',Roles) 
        FROM @tbl t2
        WHERE t2.ApplicationID=t.ApplicationID
        FOR XML PATH('')
       ),1,1,'') + ']') AS Roles  
FROM @tbl t
GROUP BY t.ApplicationID
FOR JSON PATH;

结果

[{"ApplicationID":1,"Roles":[1,5]}
,{"ApplicationID":3,"Roles":[5]}]

您可以使用STRING_AGG()(v2017 +)吗?在这种情况下，您可以简化子选择.

Can you use STRING_AGG() (v2017+)? In this case you can simplify the sub-select.

这篇关于在SQL Server中将查询输出转换为JSON格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！