Hello.
I have a database column of type JSONB with an array field which i need map into rows. How can i accomplish this ?
Hey @ritz,
You can use jsonb_array_elements(<jsonb>)
Read more about our JSON support here
Let me know if this solves your question.
Thanks,
Matt
I’ve been looking at that function but wasn’t sure how I could apply it to my dataset. For example if my dataset look like this
create table commodity ( id varchar(10) , detail jsonb );
insert into commodity ( id, detail ) values ( ‘silver’ , ’ { “data” : [ { “12/01/2001” : 100.5 } , { “12/01/2002” : 11.5 } ]} ’ );
How can i turn my array into rows like
|id|date|value|
|silver|12/01/2001| 100.5|
|silver|12/01/2002| 11.5|
This is what your table would look like after that insert
id | detail
+--------+---------------------------------------------------------+
silver | {"data": [{"12/01/2001": 100.5}, {"12/01/2001": 11.5}]}
To turn your array into rows:
select id, jsonb_array_elements(commodity.detail->'data') from commodity;
It will return
id | jsonb_array_elements
+--------+-----------------------+
silver | {"12/01/2001": 100.5}
silver | {"12/01/2001": 11.5}
Let me know if this solves your question.
Thanks!
Matt