CODEWITHSUNDEEP
postgresql
Danny Sandi
Danny Sandi

Reputation: 707

How to use DISTINCT ON in ARRAY_AGG()?

I have the following query:

SELECT array_agg(DISTINCT p.id) AS price_ids,
       array_agg(p.name)        AS price_names
FROM items 
         LEFT JOIN prices p on p.item_id = id
         LEFT JOIN third_table t3 on third_table.item_id = id
WHERE id = 1;

When I LEFT JOIN the third_table all my prices are duplicated.

I'm using DISTINCT inside ARRAY_AGG() to get the ids without dups, but I want the names without dups aswell.

If I use array_agg(DISTINCT p.name) AS price_names, it will return distinct values based on the name, not the id.

I want to do something similar to array_agg(DISTINCT ON (p.id) p.name) AS price_names, but it is invalid.

How can I use DISTINCT ON inside ARRAY_AGG()?

Upvotes: 6

Views: 4388

Answers (1)

user330315
user330315

Reputation:

Aggregate first, then join:

SELECT p.price_ids,
       p.price_names,
       t3.*
FROM items
  LEFT JOIN (
     SELECT pr.item_id, 
            array_agg(pr.id)    AS price_ids,
            array_agg(pr.name)  AS price_names
     FROM prices pr
     GROUP BY pr.item_id
  ) p on p.item_id = items.id
  LEFT JOIN third_table t3 on third_table.item_id = id
WHERE items.id = 1;

Using a lateral join might be faster if you only pick a single item:

SELECT p.price_ids,
       p.price_names,
       t3.*
FROM items
  LEFT JOIN LATERAL (
     SELECT array_agg(pr.id)    AS price_ids,
            array_agg(pr.name)  AS price_names
     FROM prices pr
     WHERE pr.item_id = items.id
  ) p on true
  LEFT JOIN third_table t3 on third_table.item_id = id
WHERE items.id = 1;

Upvotes: 4

Related Questions