Re: Extracting data from jsonb array?

What am I missing?

b2bcreditonline=# select * from foo;
id | js
| f1 | f2
----+--------------------------------------------------------------------------------+-----------+------------
1 | [{"key": "r1kval", "key2": "r1k2val"}, {"key": "r1kval2", "key2":
"r1k2val2"}] | My text 1 | My text 1a
2 | [{"key": "r2kval", "key2": "r2k2val"}, {"key": "r2kval2", "key2":
"r2k2val2"}] | My text 2 | My text 2a
(2 rows)

b2bcreditonline=# 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;
ERROR: column "f.f1" must appear in the GROUP BY clause or be used in an
aggregate function
LINE 1: select f.id, f.f1, f.f2, array_agg(t.key2) from foo as f, js...
^

On Tue, Dec 8, 2020 at 2:57 PM Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> wrote:

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