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)
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.baldwin@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.tanzer@gmail.com> wrote:On Mon, Dec 7, 2020 at 7:33 PM Ken Tanzer <ken.tanzer@gmail.com> wrote:But this has a big advantage in that you can just add other fields to the query, thusly: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 SoftwareA Free Software data systemBy and for non-profits(253) 245-3801learn more about AGENCY orfollow the discussion.--AGENCY SoftwareA Free Software data systemBy and for non-profits(253) 245-3801learn more about AGENCY orfollow the discussion.
В списке pgsql-general по дате отправления: