sql - 如何将数组聚合并加入或联合到平面 Json 对象中？

Question

我的数据如下所示:

客户表

CustomerId  CustomerName CustomerEmail  
------------------------------------------
1           Ben          Ben@gmail.com
2           Robert       Robert@gmail.com
3           Paul         Paul@gmail.com

CustomerContacts 表

CustomerContactId  CustomerId  ContactName   ContactEmail
----------------------------------------------------------
99                 1           Lisa          Lisa@msn.com
98                 3           Jane          Jane@msn.com
97                 3           Wendy         Wendy@msn.com

这是我正在寻找的结果:

[
    {
        "CustomerId": 1,
        "Names": [ "Ben","Lisa" ],
        "Emails": [ "Ben@gmail.com","Lisa@msn.com" ]
    },
    {
        "CustomerId": 2,
        "Names": [ "Robert" ],
        "Emails": [ "Robert@gmail.com" ]
    },
    {
        "CustomerId": 3,
        "Names": [ "Paul","Jane","Wendy" ],
        "Emails": [ "Paul@gmail.com","Jane@msn.com","Wendy@msn.com" ]
    }
]

我尝试过的:我很尴尬地说我什至没有接近:

SELECT 
    Customers.CustomerId,
     STUFF( ISNULL(',' + Customers.CustomerName, '') + ISNULL(',' + CustomerContacts.ContactName, ''),1,1,'') as Names
FROM Customers
FULL JOIN CustomerContacts
ON Customers.CustomerId = CustomerContacts.CustomerId
GROUP BY Customers.CustomerId;

Answer 1

不幸的是，SQL Server 在游戏中加入 JSON 的时间有点晚(仅在 2016 版本中开始内置支持)，这意味着它对 JSON 的支持仍然不是很好(尽管它确实知道如何做它确实很棒).
就个人而言，我不知道有任何内置方法可以根据查询创建 JSON 值数组
({"Name":["Value1", "Value2"...]}) 尽管生成键值对数组非常容易
(["Name":"Value1", "Name":"Value2"...]) - 至少不是通过使用 FOR JSON 子句。

但是，由于您使用的是 2017 和 Azure 版本，因此使用 string_agg 自己生成此类数组非常容易(在早期版本中有点麻烦 - 使用 for xml 路径 和 stuff 聚合字符串)。
请注意 WITHIN GROUP (ORDER BY CustomerContactId) 的用法，它强制对聚合字符串执行特定顺序。没有它，联系人的姓名和电子邮件的顺序将是任意的(尽管我很确定他们可能仍会在一次查询运行中以相同的方式排序)。

话虽这么说 - 这是我提出的解决方案:

首先，创建并填充示例表(请在您以后的问题中保存此步骤):

CREATE TABLE Customers (
    [CustomerId] int, 
    [CustomerName] varchar(6), 
    [CustomerEmail] varchar(16)
);
    
INSERT INTO Customers ([CustomerId], [CustomerName], [CustomerEmail]) VALUES
(1, 'Ben', 'Ben@gmail.com'),
(2, 'Robert', 'Robert@gmail.com'),
(3, 'Paul', 'Paul@gmail.com');

CREATE TABLE CustomerContacts (
    [CustomerContactId] int, 
    [CustomerId] int, 
    [ContactName] varchar(5), 
    [ContactEmail] varchar(13)
);
    
INSERT INTO CustomerContacts ([CustomerContactId], [CustomerId], [ContactName], [ContactEmail]) VALUES
(99, 1, 'Lisa', 'Lisa@msn.com'),
(98, 3, 'Jane', 'Jane@msn.com'),
(97, 3, 'Wendy', 'Wendy@msn.com');

然后，使用带有 FOR JSON PATH 的查询来获取 json 输出。
这里的技巧是通过将 CustomerName/CustomerEmail 与 STRING_AGG 中相关列的子查询的结果连接起来生成内部数组 CustomerContacts 表。
请注意这些列周围的 JSON_QUERY 包装器。需要它们来防止 SQL Server 转义 json 输出中的 " 字符 - 通过告诉它内容是正确的 JSON。
另外，请注意 ISNULL 用作 LEFT JOIN 的用法 - 您将获得所有客户，即使他们在 中没有相应的记录>CustomerContacts 表。

SELECT  C.CustomerId, 
        JSON_QUERY(
            '["' + C.CustomerName + ISNULL('","'+ 
            (
                SELECT STRING_AGG(CC.ContactName, '","') WITHIN GROUP (ORDER BY CustomerContactId)
                FROM CustomerContacts As CC
                WHERE CC.CustomerId = C.CustomerId
            ), '') + '"]'
        ) As Names,
        JSON_QUERY(
            '["' + C.CustomerEmail + ISNULL('","'+ 
            (
                SELECT STRING_AGG(CC.ContactEmail, '","') WITHIN GROUP (ORDER BY CustomerContactId)
                FROM CustomerContacts As CC
                WHERE CC.CustomerId = C.CustomerId
            ), '') + '"]'
        ) As Emails
FROM Customers AS C
FOR JSON PATH

结果:

[
    {
        "CustomerId": 1,
        "Names": ["Ben", "Lisa"],
        "Emails": ["Ben@gmail.com", "Lisa@msn.com"]
    }, {
        "CustomerId": 2,
        "Names": ["Robert"],
        "Emails": ["Robert@gmail.com"]
    }, {
        "CustomerId": 3,
        "Names": ["Paul", "Wendy", "Jane"],
        "Emails": ["Paul@gmail.com", "Wendy@msn.com", "Jane@msn.com"]
    }
]

您可以在 DB<>Fiddle 上看到现场演示
(不幸的是，json 输出没有很好地缩进，但它仍然有效)