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?

You could use a correlated subquery in the select clause. But then you would get NULL instead of an empty array. — Paul Spiegel
– Paul Spiegel, Commented Apr 21, 2018 at 21:47
Saved my life with an if after my hours of digging. I tried to do a subquery to first select those item, and embed those inside the array with an outter select, but no luck. Yours is just clean and easy. — Yoho
– Yoho, Commented May 1, 2021 at 22:47

Barmar · Accepted Answer · 2018-04-21 21:23:52Z

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.

edited Apr 21, 2018 at 21:23

answered Apr 21, 2018 at 21:09

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MKroeders Over a year ago

Unfortunately that returns [null]

Barmar Over a year ago

Hmm, I guess that makes sense for the reason in my last paragraph. So your way looks like the right solution.

Nikhil Sharma · Accepted Answer · 2021-09-07 06:08:01Z

0

You can Use Having and check count of your record with JSON_ARRAYAGG() function in your query HAVING count(0) > 0

answered Sep 7, 2021 at 6:08

Nikhil Sharma

12 bronze badges

Collectives™ on Stack Overflow

MySQL json_arrayagg with left join without results

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related