How to JOIN tables without extra duplicates in multiple one-to-many relationship
Use subqueries instead of joins:
SELECT
c.id,
c.name,
(SELECT
jsonb_agg(c_user.name)
FROM c_user
WHERE c_user.computer_id = c.id
) AS user_names,
(SELECT
jsonb_agg(c_accessories.name)
FROM c_accessories
WHERE c_accessories.computer_id = c.id
) AS accessory_names
FROM
computer c
Join the users to a derived table that does the joining and aggregation of computers and accessories and aggregate again.
SELECT ca.id,
jsonb_agg(u.name) AS users,
ca.accessories
FROM (SELECT c.id,
jsonb_agg(a.name) AS accessories
FROM computer AS c
LEFT JOIN c_accessories AS a
ON a.computer_id = c.id
GROUP BY c.id) AS ca
INNER JOIN c_user AS u
ON u.computer_id = ca.id
GROUP BY ca.id,
ca.accessories;
You could also first aggregate including the IDs of users and accessories, so that you can use DISTINCT
in the aggregation function, for example into arrays of records. Reaggrete to JSON in subqueries.
SELECT c.id,
(SELECT jsonb_agg(x.name)
FROM unnest(array_agg(DISTINCT row(u.id, u.name))) AS x
(id integer,
name text)) AS users,
(SELECT jsonb_agg(x.name)
FROM unnest(array_agg(DISTINCT row(a.id, a.name))) AS x
(id integer,
name text)) AS accessories
FROM computer AS c
LEFT JOIN c_accessories AS a
ON a.computer_id = c.id
INNER JOIN c_user AS u
ON u.computer_id = c.id
GROUP BY c.id;
db<>fiddle