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), orthing_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.
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;