How to JOIN tables without extra duplicates in multiple on-to-many relationship-CodePudding

I have two child tables with a one-to-many relationship to parent table. I want to join them without extra duplication.

In the actual schema, there are more one-to-many relationships to this parent and to child tables. I'm sharing a simplified schema to make the root of the problem to be easy to be seen.

Any suggestion is highly appreciated.

CREATE TABLE computer (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE c_user (
    id SERIAL PRIMARY KEY,
    computer_id INT REFERENCES computer,
    name TEXT
);

CREATE TABLE c_accessories (
    id SERIAL PRIMARY KEY,
    computer_id INT REFERENCES computer,
    name TEXT
);

INSERT INTO computer (name) VALUES ('HP'), ('Toshiba'), ('Dell');
INSERT INTO c_user (computer_id, name) VALUES (1, 'John'), (1, 'Elton'), (1, 'David'), (2, 'Ali');
INSERT INTO c_accessories (computer_id, name) VALUES (1, 'mouse'), (1, 'keyboard'), (1, 'mouse'), (2, 'mouse'), (2, 'printer'), (2, 'monitor'), (3, 'speaker');

This is my query:

SELECT 
    c.id
    ,c.name
    ,jsonb_agg(c_user.name)
    ,jsonb_agg(c_accessories.name)
FROM 
    computer c
JOIN 
    c_user ON c_user.computer_id = c.id
JOIN 
    c_accessories ON c_accessories.computer_id = c.id
GROUP BY c.id

I'm getting this result:

1   "HP"    ["John", "John", "John", "Elton", "Elton", "Elton", "David", "David", "David"]  ["mouse", "keyboard", "mouse", "mouse", "keyboard", "mouse", "mouse", "keyboard", "mouse"]
2   "Toshiba"   ["Ali", "Ali", ""Ali"]  ["monitor", "printer", "mouse"]

I want to get this result (by preserving duplicates if exist in database):

1 "HP" ["John", "Elton", "David"] ["keyboard", "mouse", "mouse"]
2 "Toshiba" "Ali" ["monitor", "printer", "mouse"]

CodePudding user response：

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

CodePudding user response：

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