MySQL json_arrayagg with left join without results

Question

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.

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

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

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

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

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

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?

Answer 1

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

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.