Re: Extracting data from jsonb array?

On Mon, Dec 7, 2020 at 7:33 PM Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> wrote:

>
> 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)
>

After a little more thought and experimenting, I'm not so sure about this
part. In particular, I'm not clear why Postgres isn't complaining about
the f1 and f2 fields. (It's not giving the "must appear in the GROUP BY
clause or be used in an aggregate function" error that I would expect, and
that I am getting when I try to apply this to my real query.)

Can anyone explain to me why those fields don't need to be grouped? Thanks.

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.
>

--
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.