MySQL json_arrayagg 左连接没有结果

Question

使用 MySQL 8.0 json_arrayagg被引入，这使得聚合 json 结果成为可能。

现在我想用它来显示附加到消息的标签。

目前有这三个表(简化)

CREATE TABLE IF NOT EXISTS feed_message (
  id CHAR(36) PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS feed_tag (
  id CHAR(36) PRIMARY KEY,
  name VARCHAR(30) NOT NULL
);

CREATE TABLE IF NOT EXISTS feed_message_tag (
  message CHAR(36) NOT NULL,
  tag CHAR(36) NOT NULL,

  PRIMARY KEY (message, tag)
);

所以有一个表包含消息，一个表包含所有标签，还有一个表包含标签和消息之间的连接 (feed_message_tag)。

我正在寻找的响应是一个带有一列标签的消息列表，它是一个带有 id + 名称的对象数组。所以接下来的事情

[{"id": 1, "name": "Foo"}]

现在我找到的例子使用(内部)连接，这意味着消息必须有一个标签，但情况并非总是如此，所以使用左连接。这让我想到了我使用的以下查询

SELECT
  json_arrayagg(
      json_object(
          'id',
          ft.id,
          'name',
          ft.name
      )
  ) as 'tags'
FROM feed_message fm
LEFT JOIN feed_message_tag fmt ON fmt.message = fm.id
LEFT JOIN feed_tag ft ON fmt.tag = ft.id
GROUP BY fm.id

现在的问题是，如果一条消息没有标签，我会得到以下输出作为 tags。

[{"id": null, "name": null}]

经过一些搜索和调整后，我对 tags 列进行了以下更改

IF(
    fmt.message IS NULL,
    json_array(),
    json_arrayagg(
      json_object(
        'id',
        ft.id,
        'name',
        ft.name
      )
    )
  ) as 'tags'

这是预期的行为还是我做错了什么？

Answer 1

看来您的方法可能是唯一的方法。

原因是 NULL 是包含在 JSON 对象中的有效值。虽然大多数聚合函数会忽略空值，因此它们会正确地忽略来自 LEFT JOIN 的非匹配行，但这对 JSON_ARRAYAGG() 来说是个问题。它会阻止您在其他情况下包含空值。无法区分显式空值和 LEFT JOIN 空值。