How do i map a json array into rows?

#1

Hello.
I have a database column of type JSONB with an array field which i need map into rows. How can i accomplish this ?

#2

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

#3

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|

#4

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