Re: Extracting data from jsonb array?

Поиск

Список

Период

Сортировка

От	Ken Tanzer
Тема	Re: Extracting data from jsonb array?
Дата	8 декабря 2020 г. 06:33:22
Msg-id	CAD3a31V_1fGMGUx=v7CqrHE0kho+GpfhYtxfyEOV+Q4J-MuP2g@mail.gmail.com обсуждение исходный текст
Ответ на	Re: Extracting data from jsonb array? (Steve Baldwin <steve.baldwin@gmail.com>)
Ответы	Re: Extracting data from jsonb array?
Список	pgsql-general

Дерево обсуждения

On Mon, Dec 7, 2020 at 7:12 PM Steve Baldwin <steve.baldwin@gmail.com> wrote:

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)

Oh I like that, and thanks! It seems a little clearer to me, but maybe that's because records still seem more familiar than json. Applying the quantitative cumbersome-syntax test, this clocks in 8 characters shorter than the other one (99 vs. 107). But this has a big advantage in that you can just add other fields to the query, thusly:

=> select f.id, f.f1,f.f2,array_agg(t.key2) from foo as f, jsonb_to_recordset(js) as t(key2 text) group by f.id;
id | f1 | f2 | array_agg
----+-----------+------------+--------------------
2 | My Text 2 | My Text 2a | {r2k2val,r2k2val2}
1 | My Text 1 | My Text 1a | {r1k2val,r1k2val2}
(2 rows)

That clocks in at 109 characters, compared to 178 for the similar query we previously had:

SELECT id,f1,f2,array_agg AS vals FROM foo LEFT JOIN (select id, array_agg(fa) from (select id, (jsonb_array_elements(js)->>'key') as fa from foo) g group by id) foo2 USING (id);