Remove Duplicates On Mysql Join Query On Json_arrayagg With Inner Join

I have an app with users; the users can create bookmarks, and each bookmark can have multiple tags. So, if I'm not mistaken in the notation: user =* bookmark *=* tag Now I try to r

Solution 1:

This answers the original question.

You need neither the tags nor the users table for your query, so it can be much simplified.

DISTINCT is not allowed with JSON_AGG(). But you can use a subquery:

SELECT b.user_id, JSON_ARRAYAGG( b.tag_id) AS tags
 FROM (SELECTDISTINCT b.user_id, bt.tag_id
       FROM bookmark_tag bt JOIN
            bookmark b 
            ON b.id = bt.bookmark_id
      ) b
 GROUPBY b.user_id;

Here is a db<>fiddle.

Solution 2:

Ended up doing this. This way I can add more JSON objects to the main select,

SELECT
  `user`.`id`,
  (
    SELECTJSON_ARRAYAGG(
        JSON_OBJECT(
          'id', subQuery.id,
          'name', subQuery.name
        )
      )
      FROM (
        SELECTDISTINCT
        bookmark.user_id,
          tag.id,
          tag.name
        FROM tag
        INNERJOIN bookmark_tag ON bookmark_tag.tag_id = tag.id
        INNERJOIN bookmark ON bookmark.id = bookmark_tag.bookmark_id
        INNERJOIN `user` ON user.id = bookmark.user_id
      ) subQuery
      WHERE user.id = subQuery.user_id
  ) AS tags,
  (
    SELECTJSON_ARRAYAGG(
        JSON_OBJECT(
          'id', bookmark.id
        )
      )
    FROM bookmark
    WHERE user.id = bookmark.user_id
  ) AS bookmarks
  FROM `user`
  GROUPBY `user`.`id`
;

Please let me know if this is really wrong.

Here is a fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=abedb69ae4db65dd344f81a7dadb565a