On Mon, Dec 7, 2020 at 7:12 PM Steve Baldwin <steve.bald...@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); Upgrade to v12+ for access to simpler/cleaner. I can't upgrade just yet, but that is something to look forward to. Out of curiosity, what would an equivalent query look like in V12? Cheers, Ken -- AGENCY Software A Free Software data system By and for non-profits *http://agency-software.org/ <http://agency-software.org/>* *https://demo.agency-software.org/client <https://demo.agency-software.org/client>* ken.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing list <agency-general-requ...@lists.sourceforge.net?body=subscribe> to learn more about AGENCY or follow the discussion.