使用INNER JOIN删除JSON_ARRAYAGG上MySQL JOIN查询的重复项 [英] 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.

因此，如果我没记错符号:user =* bookmark *=* tag

So, if I'm not mistaken in the notation: user =* bookmark *=* tag

现在，我尝试使用JSON数组及其使用的标签检索所有用户.我需要一个JSON数组，因为可能需要从标签或书签中检索名称或其他数据:

Now I try to retrieve all users with a JSON array with the tags they used. I need a JSON array because I may need to retrieve the name or other data from the tags or the bookmarks:

SELECT
  `user`.id,
  JSON_ARRAYAGG(
    JSON_OBJECT(
      "id", tag.id
    )
  ) AS tags,
  JSON_ARRAYAGG(
    JSON_OBJECT(
      "id", bookmark.id
    )
  ) AS bookmarks
FROM tag
INNER JOIN bookmark_tag ON bookmark_tag.tag_id = tag.id
INNER JOIN bookmark ON bookmark.id = bookmark_tag.bookmark_id
INNER JOIN `user` ON user.id = bookmark.user_id
GROUP BY `user`.id;

但这会返回书签和标签的重复ID:

But this returns duplicated ids both for bookmarks and tags:

这里是一个数据库小提琴，可以玩: https://dbfiddle.uk /?rdbms=mysql_8.0&fiddle=56ac0313fe2a3bf7af32a3ed9a3ea78a

Here is a DB fiddle to play with: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=56ac0313fe2a3bf7af32a3ed9a3ea78a

我该怎么做?

推荐答案

最终完成了此操作.这样，我可以将更多JSON对象添加到主选择中，

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

SELECT
  `user`.`id`,
  (
    SELECT
      JSON_ARRAYAGG(
        JSON_OBJECT(
          'id', subQuery.id,
          'name', subQuery.name
        )
      )
      FROM (
        SELECT DISTINCT
        bookmark.user_id,
          tag.id,
          tag.name
        FROM tag
        INNER JOIN bookmark_tag ON bookmark_tag.tag_id = tag.id
        INNER JOIN bookmark ON bookmark.id = bookmark_tag.bookmark_id
        INNER JOIN `user` ON user.id = bookmark.user_id
      ) subQuery
      WHERE user.id = subQuery.user_id
  ) AS tags,
  (
    SELECT
      JSON_ARRAYAGG(
        JSON_OBJECT(
          'id', bookmark.id
        )
      )
    FROM bookmark
    WHERE user.id = bookmark.user_id
  ) AS bookmarks
  FROM `user`
  GROUP BY `user`.`id`
;

请让我知道这是否真的错误.

Please let me know if this is really wrong.

这是一个小提琴: https://dbfiddle.uk/?rdbms= mysql_8.0& fiddle = abedb69ae4db65dd344f81a7dadb565a

这篇关于使用INNER JOIN删除JSON_ARRAYAGG上MySQL JOIN查询的重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！