在sql server中将查询输出转换为json格式

会是这样的:

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 模式引擎将“看到”连接并将其打包到 对象数组 .

FOR JSON 不支持 裸体的 数组( [1,2,3] 对象数组 [{Prop:Val},{Prop:Val},{Prop:Val}] ...

但是您可以通过相关子查询和一些字符串聚合(我们需要 JSON_QUERY() 要避免在数组中使用引号,请执行以下操作:

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+)?在这种情况下,可以简化子选择。

我记得:

select ApplicationID ApplicationID ,Roleid Roles from UserRoles where userid=11 and applicationid is not null 
FOR JSON PATH, ROOT("UserRoles")

FOR JSON 陈述

SELECT
    ApplicationID ApplicationID,
    Roleid Roles 
FROM UserRoles 
WHERE userid = 11 AND applicationid IS NOT NULL
FOR JSON AUTO

看看这个: Format Query Results as JSON with FOR JSON