CODEWITHSUNDEEP
sqlpostgresql
Swarnim Kumar
Swarnim Kumar

Reputation: 375

How to inner join of array type in postgres?

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?

Upvotes: 0

Views: 1535

Answers (2)

user330315
user330315

Reputation:

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270873

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;

Upvotes: 0

Related Questions