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.

• Greg: Thank you, in my project, each playlist is editable with some media and each playlist must be played on different screens. For example in my room 1 only the 3 media from the first playlist in another room are displayed the four media from the second playlist. how to put a media list in each playlist without using JSON or Array? knowing that each is playlist is unique, independent and can contain 1 or 100 media Your querry working great but is display all media of all playlist. I have try to put an WHERE in "from playlist p" but not working ^^ thx
• user1822: @Greg: see my edit