How about this:
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)
Steve
On Tue, Dec 8, 2020 at 1:00 PM David G. Johnston <david.g.johns...@gmail.com>
wrote:
> On Monday, December 7, 2020, Ken Tanzer <ken.tan...@gmail.com> wrote:
>
>>
>>
>> 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.
>
> David J.
>
>
