Postgres select join json query 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
...
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 = ? --&lt;&lt; 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.