Re: Extracting data from jsonb array?

On Mon, Dec 7, 2020 at 8:16 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> writes:
> >> => 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;
>
> > 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.
>
>
> If foo.id isn't a primary key, then I'm confused too. Can we see the
> full declaration of the table?
>
>
So I created some confusion because the original version of the table in my
example did _not_ declare a primary key. A later example, and the one I
used, did have the primary key:

CREATE TEMP TABLE foo (
id INTEGER PRIMARY KEY,
f1 TEXT,
f2 TEXT,
js JSONB
);

If foo.id is a primary key, it knows that the "group by" doesn't really
> merge any rows of foo, so it lets you get away with that. I think this
> is actually required by spec, but am too lazy to go check right now.

If I do that without the Primary Key, it does indeed complain about f1 & f2
not being grouped. But what is the "It" in "it lets you get away with
that" referring to? Or more specifically, is this some specialized case
because of something related to use of the jsonb_recordset function? I've
gotten so used to having to group on every non-aggregate field that I
didn't realize there could be any exception to that.

Thanks!

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