From: | Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> |
---|---|
To: | Steve Baldwin <steve(dot)baldwin(at)gmail(dot)com> |
Cc: | "David G(dot) Johnston" <david(dot)g(dot)johnston(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:33:22 |
Message-ID: | CAD3a31V_1fGMGUx=v7CqrHE0kho+GpfhYtxfyEOV+Q4J-MuP2g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Dec 7, 2020 at 7:12 PM Steve Baldwin <steve(dot)baldwin(at)gmail(dot)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(dot)tanzer(at)agency-software(dot)org
(253) 245-3801
Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
From | Date | Subject | |
---|---|---|---|
Next Message | Ken Tanzer | 2020-12-08 03:57:06 | Re: Extracting data from jsonb array? |
Previous Message | Steve Baldwin | 2020-12-08 03:12:07 | Re: Extracting data from jsonb array? |