postgres JSON data

Question

I am having trouble grasping how to use JSON in PG. I have a fairly large table (~4M rows) with a JSONB column containing an array of anywhere from zero to a couple hundred "rows", each with several attributes. I believe this is a reasonable approximation of the data:

drop table temp_jd;
create table temp_jd (id serial,d jsonb);
insert into temp_jd (d) values 
    (
        '[
            {"thing":"v1","a1":"bla"},
            {"thing":"v2","a1":"blaugh"},
            {"otherthing":"v1","a1":"something"}
        ]'
    ),
    (
        '[
            {"thing":"v12","a12":"bla"},
            {"thing":"v2","a1":"blaugh"},
            {"morething":"v1","a1":"whatever"}
        ]'
    )
;

I'd ultimately like to query by various bits-n-pieces, and extract aggregated text so I can pretend I have "columns" eg

thing with values (v1; v2) and (v12; v2), or
thing_a1 with values (bla; blaugh) and (bla; blaugh)

I can extract values

select id,jsonb_array_elements(d)->'thing' as thingval from temp_jd;
id | thingval 
----+----------
  1 | "v1"
  1 | "v2"
  1 | 
  2 | "v12"
  2 | "v2"
  2 |

but I can't figure out how to aggregate them as strings.

I can perform basic query operations

select id, jsonb_array_elements(d)->>'morething' from temp_jd where d @> '[{"morething":"v1"}]';

 id | ?column? 
----+----------
  2 | 
  2 | 
  2 | v1

but I'm not sure how to get to key='thing' and thing.a1='blaugh' or key='thing' and thing.a1 LIKE 'blaugh%'

Any help in better understanding this would be greatly appreciated.

user_13808319 · Answer

See if this fiddle helps shed some light on what you can do.

This query blows out your jsonb object to where you can reach the keys and values of your objects as columns in separate rows:

select t.id, a.element, a.ind - 1 as array_index, o.key, o.value
  from temp_jd t
 cross join lateral jsonb_array_elements(t.d) 
                      with ordinality as a(element, ind)
 cross join lateral jsonb_each(a.element) as o(key, value)
 order by t.id, a.ind, o.key;

To perform an aggregation on this similar to your example:

with blowup as (
  select t.id, a.element, a.ind - 1 as array_index, o.key, o.value
    from temp_jd t
   cross join lateral jsonb_array_elements(t.d) 
                        with ordinality as a(element, ind)
   cross join lateral jsonb_each(a.element) as o(key, value)
   order by t.id, a.ind, o.key
)
select id, 
       array_agg(value order by array_index) as things
  from blowup 
 where key = 'thing'
 group by id;

postgres JSON data

1 Answers