Getting JSON Array elements as records

I’m trying to extract elements from a simple JSON array in a PostgreSQL table like:

id(int)  vtags(jsonb)
-------- -----------------------
1        {"tags": ["a","b","c"]}
2        {"tags": ["x","y"]}

I would like to devise a SELECT statement to produce an output like:

id  tags
--- -----------------------
1   a
1   b
1   c
2   x 
2   y

Answer

Use jsonb_array_elements() to unnest the elements of the array:

select t.id, 
       jt.tag
from the_table t
  cross join jsonb_array_elements_text(jt.vtags -> 'tags') as jt(tag)
order by t.id;