How to inner join of array type in postgres?

Question

employees table with columns:
id (pk) BIGINT, name TEXT, email TEXT, work_locations BIGINT[].

work_locations columns contains the location table ids.

location table with columns:
id (pk) BIGINT, lat DECIMAL(12,9), long DECIMAL(12,9).

I want a result like

id  name  email            lat,      long   
1   name  [email protected]  23.345   54.3678

I am not able to join two table on work_locations and ids.
How to join these two tables?

What if an employee has more than one work location? What output do you expect then? One row for each location? — user330315
– user330315, Commented Mar 25, 2021 at 12:08
It is very bad design to store references to data in an array. The query will be inefficient. You should normalize the table. — Laurenz Albe
– Laurenz Albe, Commented Mar 25, 2021 at 13:31

user330315 · Accepted Answer · 2021-03-25 12:12:56Z

2

You can join using the ANY operator:

select e.*, 
       l.lat, 
       l.long
from employees e
  join locations l on l.id = any(e.work_locations)
order by e.id;

In general I would recommend to not store foreign keys in arrays like that. You should think about a properly normalized model with a many-to-many link table between employees and locations

answered Mar 25, 2021 at 12:12

user330315

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

Comments

Gordon Linoff · Accepted Answer · 2021-03-25 12:09:52Z

0

You can unnest():

select e.*, l.*
from employees e cross join lateral
     unnest(work_locations) wl(work_location) left join
     locations l
     on wl.work_location = l.id;

answered Mar 25, 2021 at 12:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

How to inner join of array type in postgres?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related