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.bald...@gmail.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.tan...@gmail.com> wrote: > >> >> >> On Mon, Dec 7, 2020 at 7:33 PM Ken Tanzer <ken.tan...@gmail.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.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. >>> >> >> >> -- >> 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. >> >