In PostgreSQL: how to transform an array field containing FKs to array of names from referenced table?

Question

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.

Possible duplicate of Postgres JOIN with unnest. The accepted answer in the duplicate link should work for you. — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 27, 2018 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
– Jim Jones, Commented Aug 27, 2018 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
– workerjoe, Commented Aug 27, 2018 at 13:57

Cetin Basoz · Accepted Answer · 2018-08-27 15:18:11Z

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;

edited Aug 27, 2018 at 15:18

answered Aug 27, 2018 at 14:09

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

workerjoe Over a year ago

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?

Cetin Basoz Over a year ago

You can then use the first one with left join. It would return {NULL}.

workerjoe Over a year ago

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.

Collectives™ on Stack Overflow

In PostgreSQL: how to transform an array field containing FKs to array of names from referenced table?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related