SQL Server 2019多对多关系生成指定结构JSON的实现方法

拥有如下多对多关联表结构及测试数据：

DROP TABLE IF EXISTS [dbo].[ItemOwner], [dbo].[Items], [dbo].[Owners]
GO

CREATE TABLE [dbo].[Items] 
(
    [Id] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    [Name] [varchar](max) NOT NULL
);

CREATE TABLE [dbo].[Owners] 
(
    [Id] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    [Name] [varchar](max) NOT NULL
);

CREATE TABLE [dbo].[ItemOwner] 
(
    [ItemId] [int] NOT NULL REFERENCES [dbo].[Items]([Id]),
    [Ownerd] [int] NOT NULL REFERENCES [dbo].[Owners]([Id]),
    UNIQUE([ItemId], [Ownerd])
);

INSERT INTO [dbo].[Items] ([Name]) 
VALUES ('item 1'), ('item 2'), ('item 3');

INSERT INTO [dbo].[Owners] ([Name]) 
VALUES ('owner 1'), ('owner 2');

INSERT INTO [dbo].[ItemOwner] 
VALUES (1, 1), (1, 2), -- 该物品属于两个所有者
       (2, 1), (2, 2), -- 该物品属于两个所有者 
       (3, 1); -- 该物品属于一个所有者

需要将所有者组合作为顶层对象，聚合对应的物品，生成如下结构的JSON：

[
    {
        "owners": [
            {
                "id": 1,
                "name": "owner 1"
            },
            {
                "id": 2,
                "name": "owner 2"
            }
        ],
        "own": [
            {
                "id": 1,
                "name": "item 1"
            },
            {
                "id": 2,
                "name": "item 2"
            }
        ]
    },
    {
        "owners": [
            {
                "id": 1,
                "name": "owner 1"
            }
        ],
        "own": [
            {
                "id": 3,
                "name": "item 3"
            }
        ]
    }
]

使用SQL Server 2019（无JSON_ARRAYAGG函数），需实现上述需求。

核心思路是先通过分组生成每个物品对应的排序后所有者标识字符串，以此作为分组依据聚合物品；再针对每个所有者组合，生成对应的所有者JSON数组和物品JSON数组，最后组合成目标结构。

WITH ItemOwnerGroups AS (
    -- 为每个物品生成排序后的所有者ID字符串，作为分组键
    SELECT 
        io.ItemId,
        STRING_AGG(io.Ownerd, ',') WITHIN GROUP (ORDER BY io.Ownerd) AS OwnerGroupKey
    FROM dbo.ItemOwner io
    GROUP BY io.ItemId
),
OwnerGroupDetails AS (
    -- 为每个所有者组合，获取对应的所有者列表和物品列表
    SELECT 
        og.OwnerGroupKey,
        -- 生成所有者的JSON数组
        (SELECT o.Id AS id, o.Name AS name 
         FROM dbo.ItemOwner io
         JOIN dbo.Owners o ON io.Ownerd = o.Id
         WHERE io.ItemId IN (SELECT ItemId FROM ItemOwnerGroups WHERE OwnerGroupKey = og.OwnerGroupKey)
         GROUP BY o.Id, o.Name
         ORDER BY o.Id
         FOR JSON PATH) AS OwnersJson,
        -- 生成物品的JSON数组
        (SELECT i.Id AS id, i.Name AS name 
         FROM dbo.Items i
         JOIN ItemOwnerGroups iog ON i.Id = iog.ItemId
         WHERE iog.OwnerGroupKey = og.OwnerGroupKey
         ORDER BY i.Id
         FOR JSON PATH) AS ItemsJson
    FROM ItemOwnerGroups og
    GROUP BY og.OwnerGroupKey
)
-- 组合成最终的JSON结构
SELECT 
    JSON_QUERY(OwnersJson) AS owners,
    JSON_QUERY(ItemsJson) AS own
FROM OwnerGroupDetails
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

关键说明： #

1. ItemOwnerGroups CTE：通过排序后拼接所有者ID，确保拥有完全相同所有者的物品会被分到同一组（比如物品1和2的所有者组合排序后字符串都是1,2）。
2. OwnerGroupDetails CTE：通过子查询结合FOR JSON PATH生成JSON数组，用GROUP BY o.Id, o.Name避免同一所有者在数组中重复出现。
3. 最终查询使用JSON_QUERY避免JSON字符串被转义，FOR JSON PATH, WITHOUT_ARRAY_WRAPPER生成符合要求的顶层数组结构。

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