Postgres select join json query

Question

I have a problem to make a postgres query with join from data of json.

I have 2 tables

The first one is media:

• id as integer
• path as string

and an other table playlist

• id as integer
• media as string (with my json inside)

For example

Media:

1   /img/a.jpg
2   /img/b.jpg
3   /img/c.jpg
4   /img/d.jpg
5   /img/e.jpg

Playlist:

1   ["1", "2", "5"]
2   ["4", "2", "3", "1"]

I want to make an SELECT on playlist and get back all path ideally as json or if not possible something like (example for playlist 1):

1   /img/a.jpg
2   /img/b.jpg
3   /img/e.jpg
... 

Answer 1

You need to "unnest" the elements of the JSON array and then join that to your media table:

The following query assumes that media is a column of type jsonb. If that is not the case (which it should be) you need to cast it media::jsonb.

select m.id, m.path
from media m
  join (  
    select jm.id::integer as media_id
    from playlist p
      cross join jsonb_array_elements_text(p.media) as jm(id)
    where p.id = ? --<< your playlist id here
) t on m.id = t.media_id;

jsonb_array_elements_text(p.media) returns all elements of the array as rows and as jm(id) defines a table alias for that result giving the column that is returned the name id. The expression jm.id::integer as media_id then converts the string value into a proper integer so it can be used in the join condition.

---

Unrelated, but: this is a case of unnecessary JSON. If you normalize your data model, queries like that will be a lot easier and more efficient. Plus: you can define proper foreign key constraints between playlist and media.