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

Question

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?

question from:https://stackoverflow.com/questions/65835448/postgres-how-to-do-a-left-join-on-jsonb-array-integer

Answer 1

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.