sql - 如何聚合来自同一引用表的两个对象？

所以，我有一个属于成员表的表用户。
用户表是：

id, firstName, lastName

成员表是：其中creatorId也属于用户表。

id, userId, creatorId

我正在尝试使用aggregate获取包含用户详细信息的userId和包含用户详细信息的creatorId。

SELECT
member.id,
    json_agg(json_build_object('userId', user.id, 'firstName', user.firstName)) AS user,
    json_agg(json_build_object('userId', c.id, 'firstName', c.firstName)) AS creator

FROM
    member
    INNER JOIN user u ON member.userId = u.id
    INNER JOIN user c ON member.creatorId = user.id
GROUP BY
    am.id

我也想包括创作者的名字和姓氏，但我不知道如何做到这一点。请大家帮忙。

最佳答案

我想你想要：

SELECT m.id,
       json_agg(json_build_object('userId', mu.id, 'firstName', mu.firstName)) AS user,
       json_agg(json_build_object('creatorId', mc.id, 'firstName', mc.firstName)) AS creator
FROM member m INNER JOIN
     member mu
     ON m.userId = mu.id INNER JOIN
     member mc
     ON m.creatorId = mc.id
GROUP BY m.id;

也就是说，我怀疑是否需要聚合。大概，id是主键。另外，您可能需要LEFT JOIN，以防其中一个字段NULL。所以：

SELECT m.id,
       json_build_object('userId', mu.id, 'firstName', mu.firstName) AS user,
       json_build_object('creatorId', mc.id, 'firstName', mc.firstName) AS creator
FROM member m LEFT JOIN
     member mu
     ON m.userId = mu.id LEFT JOIN
     member mc
     ON m.creatorId = mc.id;