b2bcreditonline=# select f.id, array_agg(t.key2) from foo as f, jsonb_to_recordset(js) as t(key2 text) group by f.id; id | array_agg ----+-------------------- 2 | {r2k2val,r2k2val2} 1 | {r1k2val,r1k2val2} (2 rows)
I'm of course very glad Postgresql has the ability to work with JSON at all, but as I dig into it I'm kinda surprised at the level of complexity needed to extract data in relatively simple ways. Hopefully eventually it will seem simple to me, as it seems to appear to others.
Upgrade to v12+ for access to simpler/cleaner. Composing various unnesting and key extraction operations works but, yes, it gets ugly proportional to the extent you need to dig into complex json structures. That said lateral joining reduces nesting which is measurably cleaner.