MySQL json_arrayagg与左联接没有结果 [英] MySQL json_arrayagg with left join without results

问题描述

在MySQL 8.0中， json_arrayagg 被引入，这使得聚合json结果成为可能.

With MySQL 8.0 the json_arrayagg was introduced, this made it possible to aggregate json results.

现在，我想用它来显示邮件中附加的标签.

Now I want to use it to show the tags attached to a message.

目前有3个表格(已简化)

Currently there are three tables for this (simplefied)

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).

So there is one table contain the message, one that holds all the tags and a table that hold the connections between tags and messages (feed_message_tag).

我正在寻找的响应是带有一列标签的消息列表，该标签列是具有ID +名称的对象数组.如下所述

The response I am seeking is a list of messages with a column of tags which is an array of objects with there id + name. So something as followed

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

现在，我发现示例使用(内部)联接，这意味着消息必须具有标签，但并非总是如此，因此使用左联接.这使我进入以下我使用的查询

Now the examples I find use (inner) joins which means a message must have a tag, but that is not always the case so left join is used. Which brings me to the following query I use

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.

The problem now is that if one message has no tags I get the following output as tags.

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

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

After some searching and tweaking I came to the following change for the tags column

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

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

Is this the intended behaviour or am I doing something wrong?

推荐答案

似乎您的方法可能是唯一的方法.

Seems like your method may be the only way to do this.

原因是NULL是要包含在JSON对象中的有效值.尽管大多数聚合函数都忽略空值，所以它们正确地忽略了来自LEFT JOIN的不匹配行，但对于JSON_ARRAYAGG()来说将是一个问题.这将防止您在其他情况下包括空值.无法将显式null和LEFT JOIN null区分开.

The reason is that NULL is a valid value to include in JSON objects. While most aggregation functions ignore nulls, so they properly ignore non-matching rows that come from LEFT JOIN, it would be a problem for JSON_ARRAYAGG(). It would prevent you from including null values in other situations. There's no way to distinguish explicit nulls from LEFT JOIN nulls.

这篇关于MySQL json_arrayagg与左联接没有结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！