CODEWITHSUNDEEP
postgresqljsonb
Philiz
Philiz

Reputation: 333

Postgres, How to do a LEFT JOIN on JSONB array integer

I have a table "Photos" that references a list of persons shown in the photo. The persons are referenced in a JSONB column named "persons" containing a json like in this format [1,2,3,4,5]

CREATE TABLE Photos (
  id INTEGER PRIMARY KEY,
  name TEXT,
  persons JSONB
);

CREATE TABLE Person(
  id INTEGER PRIMARY KEY,
  name TEXT
);

I want to do a LEFT JOIN but I can't figure out how. Something like this

SELECT * FROM photos p LEFT JOIN person ON p.persons = person.id

Any idea how to do it?

Upvotes: 1

Views: 1719

Answers (1)

user330315
user330315

Reputation:

Unnest the array then you can join:

select ph.id, ph.name, pe.*
from photos ph
  left join jsonb_array_elements(ph.persons) as p(pid) on true
  left join person pe on pe.id = p.pid::int

The first left join expands all array elements to rows. A left join is required, otherwise photos with an empty persons array would not show up. Then you can join against the person table.

Upvotes: 2

Related Questions