SQL Server中按用户名分组构建指定结构JSON对象的实现方案

需求 #

现有存储用户账户信息的表，需要将同一用户的多条行数据合并为指定结构的JSON对象。

原表结构 #

预期输出 #

原有写法的问题 #

你之前编写的查询存在两处核心问题：

• 没有先解析AccessKeys字段中存储的JSON字符串，直接关联条件错误导致无法正确聚合
• 未将同一用户的多个账户数据封装为数组，也没有拼接Marker字段到最终JSON中

SELECT
     Username
    ,Accounts = (
        SELECT
             Account
            ,Checking
            ,Loan
            ,Savings
        FROM dbo.Accounts A1
        WHERE A1.Account= A2.Account
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER 
     )
FROM
    dbo.Accounts A2
GROUP BY
     Accounts
    ,Username
;

最终解决方案 #

SELECT
  Username
 ,(
      SELECT
           Accounts = JSON_QUERY((
           SELECT AK.*
           FROM
                dbo.Accounts A2
                CROSS APPLY OPENJSON(AccessKeys) WITH (
                      Account nvarchar(10)
                     ,Checking nvarchar(10)
                     ,Loan nvarchar(10)
                     ,Savings int
                ) AK
           WHERE A2.Username = A1.Username
           FOR JSON PATH
           ))
           ,'New' Marker
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
 ) JSON
FROM dbo.Accounts A1
GROUP BY Username
;

实现逻辑说明 #

• 用OPENJSON配合WITH子句，将AccessKeys字段存储的JSON字符串解析为可直接查询的结构化字段
• 按Username分组，同一用户的所有账户数据通过FOR JSON PATH生成数组格式
• 用JSON_QUERY包裹账户数组，避免外层JSON序列化时将数组转义为字符串
• 最后拼接Marker固定值，通过WITHOUT_ARRAY_WRAPPER参数去掉外层多余的方括号，得到符合要求的JSON结构

内容的提问来源于stack exchange，提问作者Don Knisley