Re: Extracting data from jsonb array?
От
Steve Baldwin
Тема
Re: Extracting data from jsonb array?
Дата
Msg-id
CAKE1Aibh8ekYEVpCZyCQyt0L9-75G7D=PV_b8Wht02DsQHZR5Q@mail.gmail.com
Ответ на
Re: Extracting data from jsonb array? (David G. Johnston)
Список
Дерево обсуждения
Extracting data from jsonb array? Ken Tanzer <ken.tanzer@gmail.com>
Re: Extracting data from jsonb array? Steve Baldwin <steve.baldwin@gmail.com>
Re: Extracting data from jsonb array? Ken Tanzer <ken.tanzer@gmail.com>
Re: Extracting data from jsonb array? "David G. Johnston" <david.g.johnston@gmail.com>
Re: Extracting data from jsonb array? Ken Tanzer <ken.tanzer@gmail.com>
Re: Extracting data from jsonb array? "David G. Johnston" <david.g.johnston@gmail.com>
Re: Extracting data from jsonb array? Ken Tanzer <ken.tanzer@gmail.com>
Re: Extracting data from jsonb array? Rob Sargent <robjsargent@gmail.com>
Re: Extracting data from jsonb array? "David G. Johnston" <david.g.johnston@gmail.com>
Re: Extracting data from jsonb array? Rob Sargent <robjsargent@gmail.com>
Re: Extracting data from jsonb array? Ken Tanzer <ken.tanzer@gmail.com>
Re: Extracting data from jsonb array? "David G. Johnston" <david.g.johnston@gmail.com>
Re: Extracting data from jsonb array? Steve Baldwin <steve.baldwin@gmail.com>
Re: Extracting data from jsonb array? Ken Tanzer <ken.tanzer@gmail.com>
Re: Extracting data from jsonb array? Ken Tanzer <ken.tanzer@gmail.com>
Re: Extracting data from jsonb array? Tom Lane <tgl@sss.pgh.pa.us>
Re: Extracting data from jsonb array? Ken Tanzer <ken.tanzer@gmail.com>
Re: Extracting data from jsonb array? Tom Lane <tgl@sss.pgh.pa.us>
Re: Extracting data from jsonb array? Ken Tanzer <ken.tanzer@gmail.com>
Re: Extracting data from jsonb array? Tom Lane <tgl@sss.pgh.pa.us>
Re: Extracting data from jsonb array? Ken Tanzer <ken.tanzer@gmail.com>
Re: Extracting data from jsonb array? "David G. Johnston" <david.g.johnston@gmail.com>
Re: Extracting data from jsonb array? Steve Baldwin <steve.baldwin@gmail.com>
Re: Extracting data from jsonb array? Steve Baldwin <steve.baldwin@gmail.com>
Re: Extracting data from jsonb array? Steve Baldwin <steve.baldwin@gmail.com>
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)
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.johnston@gmail.com> wrote:
On Monday, December 7, 2020, Ken Tanzer <ken.tanzer@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.
В списке pgsql-general по дате отправления
