From: | Steve Baldwin <steve(dot)baldwin(at)gmail(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com>, Rob Sargent <robjsargent(at)gmail(dot)com>, "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Extracting data from jsonb array? |
Date: | 2020-12-08 03:12:07 |
Message-ID: | CAKE1Aibh8ekYEVpCZyCQyt0L9-75G7D=PV_b8Wht02DsQHZR5Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)g(dot)johnston(at)gmail(dot)com>
wrote:
> On Monday, December 7, 2020, Ken Tanzer <ken(dot)tanzer(at)gmail(dot)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.
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Ken Tanzer | 2020-12-08 03:33:22 | Re: Extracting data from jsonb array? |
Previous Message | David G. Johnston | 2020-12-08 01:59:52 | Re: Extracting data from jsonb array? |