Re: Extracting data from jsonb array?

You can also do this:

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

On Tue, Dec 8, 2020 at 3:09 PM Steve Baldwin <steve(dot)baldwin(at)gmail(dot)com>
wrote:

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