Re: Extracting data from jsonb array?

This article might help understanding the reason -
https://dba.stackexchange.com/questions/158015/why-can-i-select-all-fields-when-grouping-by-primary-key-but-not-when-grouping-b

From the postgres docs:

"When GROUP BY is present, or any aggregate functions are present, it is
not valid for the SELECT list expressions to refer to ungrouped columns
except within aggregate functions or when the ungrouped column is
functionally dependent on the grouped columns, since there would otherwise
be more than one possible value to return for an ungrouped column. A
functional dependency exists if the grouped columns (or a subset thereof)
are the primary key of the table containing the ungrouped column."

If you come from an Oracle background (as I do), this behaviour may
surprise you, since Oracle definitely doesn't allow this.

I much prefer Postgres. 😁

Steve

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

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