0

My database has a table of Products, like so:

PRODUCTS
----------------------------
 id | name      | suppliers
----------------------------
  1 | widget    | {1,2}
  2 | gizmo     | {1}
  3 | geegaw    | {3}
  4 | tchotchke | null

The suppliers column contains an array (numeric[]) of IDs belonging to a table of suppliers:

SUPPLIERS
------------
 id | name 
------------
  1 | alpha
  2 | beta
  3 | gamma

How can I write a query that will return the contents of PRODUCTS except with an array of supplier names instead of supplier ID numbers? Result should look like this:

-----------------------------------
 id | name      | suppliers
-----------------------------------
  1 | widget    | {'alpha','beta'}
  2 | gizmo     | {'alpha'}
  3 | geegaw    | {'gamma'}
  4 | tchotchke | null

Succinct and efficient methods would be preferred, but readability/understandability are also nice.

Edit: This isn't a duplicate of the linked question, although that question does involve the unnest operation, it doesn't re-aggregate the result. The answer to this question makes a new contribution to the site.

workerjoe
  • 2,421
  • 1
  • 26
  • 49
  • 5
    Possible duplicate of [Postgres JOIN with unnest](https://stackoverflow.com/questions/33466823/postgres-join-with-unnest). The accepted answer in the duplicate link should work for you. – Tim Biegeleisen Aug 27 '18 at 13:52
  • how do you maintain the consistence of the column `suppliers`? I would recommend to create a satellite table for `suppliers` and create a constraint to the `products` table (1 to N relationship). – Jim Jones Aug 27 '18 at 13:54
  • @JimJones these tables are actually two dimensions in a star-schema datamart, so they are not normalized according to 3NF. Referential integrity is managed in the source system from which the data is ETL'd. – workerjoe Aug 27 '18 at 13:57

1 Answers1

1
select t1.id, t1.name, array_agg(s.Name) as Suppliers 
from Products t1 
left join lateral (
select unnest(suppliers) as supplierId from myProducts t2 
where t1.id = t2.id) as t on true
left join Suppliers s on s.Id = t.supplierID
group by t1.id, t1.name;

What was I thinking. Here is a better version:

select p.id, p.name, array_agg(s.Name) as Suppliers
from (select *,unnest(suppliers) as supplierId from Products) p 
left join Suppliers s on s.Id = p.supplierID
group by p.id, p.name;
Cetin Basoz
  • 22,495
  • 3
  • 31
  • 39
  • This looks like it's on the right track. I just realized my data also contains null rows (no suppliers) and have edited the question accordingly. Would your query work in that case if we just change `inner join` to a `left join`? Would the result be a null column or an empty array? – workerjoe Aug 27 '18 at 14:31
  • You can then use the first one with left join. It would return {NULL}. – Cetin Basoz Aug 27 '18 at 14:47
  • Second one works with left join also. If you want to update your answer with that, it would then match the question and I can mark it accepted. – workerjoe Aug 27 '18 at 15:03