0

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
... 
2

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.

  • 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 – Greg Mar 5 '19 at 15:58
  • @Greg: see my edit – a_horse_with_no_name Mar 5 '19 at 16:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.