sql-server - 当右表中没有数据时，使用 LEFT JOIN 为 JSON 选择返回单个元素数组

Question

我正在将一些数据从 MS SQL 服务器数据库导出为 JSON。

从A表到B表有两个外键表，是0-many关系，即。表 A 中的一条记录可能在表 B 中有一些数据，但并非必须如此。表 A 包含人员记录，表 B 是他们当前和以前地址的列表。

我正在使用此语句获取 JSON 数据:

SELECT
    A.Firstname,
    A.LastName,
    HistoricAddresses.AddressLine1
    HistoricAddresses.AddressLine2
FROM
    People A
LEFT JOIN
    HistoricAddresses ON HistoricAddresses.PeopleId = A.Id
FOR JSON AUTO

这会产生如下所示的 JSON 结构:

[
    {
        "Firstname": "xyz",
        "Lastname": "xyz",
        "HistoricAddresses": [
            {
                "AddressLine1": "abc",
                "AddressLine2": "abc"
            },
            // more historic address records follow
        ]
    },
    // more records follow
]

这一切都很好，直到 A 表中的一条记录在 B 表中没有任何记录。在这种情况下，JSON 看起来像这样:

[
    {
        "Firstname": "xyz",
        "Lastname": "xyz",
        "HistoricAddresses": [
            {}
        ]
    },
    // more records follow
]

HistoricAddresses 属性的内容是一个包含单个空元素的数组。它应该只是一个空数组。

有没有办法做到这一点？

Answer 1

我通过不使用 LEFT JOIN 而是使用子选择解决了这个问题。

代替

SELECT
    A.Firstname,
    A.LastName,
    HistoricAddresses.AddressLine1
    HistoricAddresses.AddressLine2
FROM
    People A
LEFT JOIN
    HistoricAddresses ON HistoricAddresses.PeopleId = A.Id
FOR JSON AUTO

我正在使用

SELECT
    A.Firstname,
    A.LastName,
    (SELECT 
        HistoricAddress.AddressLine1,
        HistoricAddress.AddressLine2
     FROM
        HistoricAddress
     WHERE
        HistoricAddress.PeopleId = A.Id
     FOR JSON AUTO) AS HistoricAddresses
FROM
    People A
FOR JSON AUTO

如果没有 HistoricAddresses JSON 属性，这将完全省略。我的解析器可以处理它。

不过，我还没有对其进行基准测试，因此绝对欢迎其他解决方案。