Extracting data from jsonb array?

Try:

select _message_body->'Charges'->>'Name' from ...

Steve

On Tue, Dec 8, 2020 at 9:58 AM Ken Tanzer <ken.tanzer@gmail.com> wrote:

On Mon, Dec 7, 2020 at 3:12 PM Steve Baldwin <steve.baldwin@gmail.com>
wrote:

Try:

select _message_body->'Charges'->>'Name' from ...

Hi Steve. I tried that again, and that returns a NULL value for me. I
believe that is because Charges holds an array of two elements, each of
which has a Name element. Though my terminology might not be correct!

Cheers,
Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/&gt;*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@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/&gt;*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

On Mon, Dec 7, 2020 at 4:13 PM Steve Baldwin <steve.baldwin@gmail.com>
wrote:

Try:

select _message_body->'Charges'->>'Name' from ...

Not so much..."Charges" is an array so "->>" doesn't do anything useful.

The OP needs to use "json_array_elements" to navigate past the array and
get to the next layer of the json where ->>'Name' will then work.

For v12 and newer readers, SQL/JSON Path should probably be used instead.

David J.

On Mon, Dec 7, 2020 at 3:22 PM David G. Johnston <david.g.johnston@gmail.com>
wrote:

On Mon, Dec 7, 2020 at 4:13 PM Steve Baldwin <steve.baldwin@gmail.com>
wrote:

Try:

select _message_body->'Charges'->>'Name' from ...

Not so much..."Charges" is an array so "->>" doesn't do anything useful.

The OP needs to use "json_array_elements" to navigate past the array and
get to the next layer of the json where ->>'Name' will then work.

Thank you David. I had tried that function without much luck. But with
your inspiration, I made progress and got to this:

select
_message_exchange_id,jsonb_array_elements(_message_body->'Charges')->>'Name'
FROM message_import_court_case WHERE _message_exchange_id = 1296;
_message_exchange_id | ?column?
----------------------+--------------------------------------------
1296 | Possession Of Burglary Tools
1296 | Burglary In The Second Degree (Commercial)
(2 rows)

But what I really want is one line per message, with the charges in an
array. I can't seem to find the right syntax to make this work:

=> select
_message_exchange_id,array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name')
FROM message_import_court_case WHERE _message_exchange_id = 1296;
ERROR: column "message_import_court_case._message_exchange_id" must appear
in the GROUP BY clause or be used in an aggregate function
LINE 1: select _message_exchange_id,array_agg(jsonb_array_elements(_...
^

=> select
_message_exchange_id,array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name')
FROM message_import_court_case WHERE _message_exchange_id = 1296 GROUP BY 1;
ERROR: set-valued function called in context that cannot accept a set

=> select _message_exchange_id,(SELECT
array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name')) FROM
message_import_court_case WHERE _message_exchange_id = 1296 GROUP BY 1;
ERROR: set-valued function called in context that cannot accept a set

=> select _message_exchange_id,(SELECT
array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name')) FROM
message_import_court_case WHERE _message_exchange_id = 1296;
ERROR: column "message_import_court_case._message_exchange_id" must appear
in the GROUP BY clause or be used in an aggregate function
LINE 1: select _message_exchange_id,(SELECT array_agg(jsonb_array_el...

Thanks!

Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/&gt;*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

On Mon, Dec 7, 2020 at 4:49 PM Ken Tanzer <ken.tanzer@gmail.com> wrote:

On Mon, Dec 7, 2020 at 3:22 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Mon, Dec 7, 2020 at 4:13 PM Steve Baldwin <steve.baldwin@gmail.com>
wrote:

Try:

select _message_body->'Charges'->>'Name' from ...

Not so much..."Charges" is an array so "->>" doesn't do anything useful.

The OP needs to use "json_array_elements" to navigate past the array and
get to the next layer of the json where ->>'Name' will then work.

Thank you David. I had tried that function without much luck. But with
your inspiration, I made progress and got to this:

select
_message_exchange_id,jsonb_array_elements(_message_body->'Charges')->>'Name'
FROM message_import_court_case WHERE _message_exchange_id = 1296;
_message_exchange_id | ?column?
----------------------+--------------------------------------------
1296 | Possession Of Burglary Tools
1296 | Burglary In The Second Degree (Commercial)
(2 rows)

But what I really want is one line per message, with the charges in an
array. I can't seem to find the right syntax to make this work:

=> select
_message_exchange_id,array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name')
FROM message_import_court_case WHERE _message_exchange_id = 1296;
ERROR: column "message_import_court_case._message_exchange_id" must
appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select _message_exchange_id,array_agg(jsonb_array_elements(_...
^

=> select
_message_exchange_id,array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name')
FROM message_import_court_case WHERE _message_exchange_id = 1296 GROUP BY 1;
ERROR: set-valued function called in context that cannot accept a set

=> select _message_exchange_id,(SELECT
array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name')) FROM
message_import_court_case WHERE _message_exchange_id = 1296 GROUP BY 1;
ERROR: set-valued function called in context that cannot accept a set

=> select _message_exchange_id,(SELECT
array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name')) FROM
message_import_court_case WHERE _message_exchange_id = 1296;
ERROR: column "message_import_court_case._message_exchange_id" must
appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select _message_exchange_id,(SELECT array_agg(jsonb_array_el...

Sub-queries are a simple solution to get around the "set-valued function"
restriction.

The more direct way is to place the set-valued function in the FROM clause
where it wants to be, by using LATERAL (the keyword itself can be implied
when dealing with functions)

select array_agg(e->>'key') from (values
('[{"key":"val"},{"key":"val2"}]'::jsonb)) val (v), jsonb_array_elements(v)
jae (e)

David J.

On Mon, Dec 7, 2020 at 4:00 PM David G. Johnston <david.g.johnston@gmail.com>
wrote:

On Mon, Dec 7, 2020 at 4:49 PM Ken Tanzer <ken.tanzer@gmail.com> wrote:

On Mon, Dec 7, 2020 at 3:22 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Mon, Dec 7, 2020 at 4:13 PM Steve Baldwin <steve.baldwin@gmail.com>
wrote:

Try:

select _message_body->'Charges'->>'Name' from ...

Not so much..."Charges" is an array so "->>" doesn't do anything useful.

The OP needs to use "json_array_elements" to navigate past the array and
get to the next layer of the json where ->>'Name' will then work.

Thank you David. I had tried that function without much luck. But with
your inspiration, I made progress and got to this:

select
_message_exchange_id,jsonb_array_elements(_message_body->'Charges')->>'Name'
FROM message_import_court_case WHERE _message_exchange_id = 1296;
_message_exchange_id | ?column?
----------------------+--------------------------------------------
1296 | Possession Of Burglary Tools
1296 | Burglary In The Second Degree (Commercial)
(2 rows)

But what I really want is one line per message, with the charges in an
array. I can't seem to find the right syntax to make this work:

=> select
_message_exchange_id,array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name')
FROM message_import_court_case WHERE _message_exchange_id = 1296;
ERROR: column "message_import_court_case._message_exchange_id" must
appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select _message_exchange_id,array_agg(jsonb_array_elements(_...
^

=> select
_message_exchange_id,array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name')
FROM message_import_court_case WHERE _message_exchange_id = 1296 GROUP BY 1;
ERROR: set-valued function called in context that cannot accept a set

=> select _message_exchange_id,(SELECT
array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name')) FROM
message_import_court_case WHERE _message_exchange_id = 1296 GROUP BY 1;
ERROR: set-valued function called in context that cannot accept a set

=> select _message_exchange_id,(SELECT
array_agg(jsonb_array_elements(_message_body->'Charges')->>'Name')) FROM
message_import_court_case WHERE _message_exchange_id = 1296;
ERROR: column "message_import_court_case._message_exchange_id" must
appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select _message_exchange_id,(SELECT array_agg(jsonb_array_el...

Sub-queries are a simple solution to get around the "set-valued function"
restriction.

The more direct way is to place the set-valued function in the FROM clause
where it wants to be, by using LATERAL (the keyword itself can be implied
when dealing with functions)

select array_agg(e->>'key') from (values
('[{"key":"val"},{"key":"val2"}]'::jsonb)) val (v), jsonb_array_elements(v)
jae (e)

David J.

OK, let me try asking again. (I'm trying to actually get something that
works.) So given an example like this:

CREATE TEMP TABLE foo (
id INTEGER,
js JSONB
);

INSERT INTO foo (id,js) VALUES (1,
'[
{"key":"r1kval","key2":"r1k2val"},
{"key":"r1kval2","key2":"r1k2val2"}
]');
INSERT INTO foo (id,js) VALUES (2,
'[
{"key":"r2kval","key2":"r2k2val"},
{"key":"r2kval2","key2":"r2k2val2"}
]');

Can anyone help me with a working query (preferably with the least
cumbersome syntax possible!) that would return these values (the key2
values) as array text elements:

id Agg_val
---- ------------------------
1 {r1k2val,r1k2val2}
2 {r2k2val,r2k2val}

(2 rows)

Thank you!

Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/&gt;*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

OK, let me try asking again.  (I'm trying to actually get something
that works.)  So given an example like this:

CREATE TEMP TABLE foo (
  id INTEGER,
  js  JSONB
);

INSERT INTO foo (id,js) VALUES (1,
'[
{"key":"r1kval","key2":"r1k2val"},
{"key":"r1kval2","key2":"r1k2val2"}
]');
INSERT INTO foo (id,js) VALUES (2,
'[
{"key":"r2kval","key2":"r2k2val"},
{"key":"r2kval2","key2":"r2k2val2"}
]');

Can anyone help me with a working query (preferably with the least
cumbersome syntax possible!) that would return these values (the key2
values) as array text elements:

id     Agg_val
----  ------------------------
1     {r1k2val,r1k2val2}
2     {r2k2val,r2k2val}

(2 rows)

postgres=# select id, array_agg(fa) from (select id,
(jsonb_array_elements(js)->'key') as fa from foo) g group by id order by id;
 id |          array_agg
----+------------------------------
  1 | {"\"r1kval\"","\"r1kval2\""}
  2 | {"\"r2kval\"","\"r2kval2\""}
(2 rows)

I think the quotes are a fault of example data?

Cumbersome is in the eyes of the beholder ;)

On Mon, Dec 7, 2020 at 6:13 PM Rob Sargent <robjsargent@gmail.com> wrote:

postgres=# select id, array_agg(fa) from (select id,
(jsonb_array_elements(js)->'key') as fa from foo) g group by id order by id;
id | array_agg
----+------------------------------
1 | {"\"r1kval\"","\"r1kval2\""}
2 | {"\"r2kval\"","\"r2kval2\""}
(2 rows)

I think the quotes are a fault of example data?

The quotes are the fault of the query author choosing the "->" operator
instead of "->>".

David J.

On 12/7/20 6:17 PM, David G. Johnston wrote:

On Mon, Dec 7, 2020 at 6:13 PM Rob Sargent <robjsargent@gmail.com
<mailto:robjsargent@gmail.com>> wrote:

postgres=# select id, array_agg(fa) from (select id,
(jsonb_array_elements(js)->'key') as fa from foo) g group by id
order by id;
 id |          array_agg
----+------------------------------
  1 | {"\"r1kval\"","\"r1kval2\""}
  2 | {"\"r2kval\"","\"r2kval2\""}
(2 rows)

I think the quotes are a fault of example data?

The quotes are the fault of the query author choosing the "->"
operator instead of "->>".

David J.

With that correction OP might have an answer?

On Mon, Dec 7, 2020 at 5:20 PM Rob Sargent <robjsargent@gmail.com> wrote:

On 12/7/20 6:17 PM, David G. Johnston wrote:

On Mon, Dec 7, 2020 at 6:13 PM Rob Sargent <robjsargent@gmail.com> wrote:

postgres=# select id, array_agg(fa) from (select id,
(jsonb_array_elements(js)->'key') as fa from foo) g group by id order by id;
id | array_agg
----+------------------------------
1 | {"\"r1kval\"","\"r1kval2\""}
2 | {"\"r2kval\"","\"r2kval2\""}
(2 rows)

I think the quotes are a fault of example data?

The quotes are the fault of the query author choosing the "->" operator
instead of "->>".

David J.

With that correction OP might have an answer?

Thank you Rob! I would say yes, except I fear I over-simplified my
example. What if there are other fields in the table, and I want to treat
this array_agg as just another field? So here's the query you had (with
the ->> change):

=> select id, array_agg(fa) from (select id,
(jsonb_array_elements(js)->>'key') as fa from foo) g group by id;
id | array_agg
----+------------------
1 | {r1kval,r1kval2}
2 | {r2kval,r2kval2}
(2 rows)

And here's the table/data with two other fields added, f1 & f2:

CREATE TEMP TABLE foo (
id INTEGER PRIMARY KEY,
f1 TEXT,
f2 TEXT,
js JSONB
);
INSERT INTO foo (id,f1,f2,js) VALUES (1,'My Text 1','My Text 1a',
'[
{"key":"r1kval","key2":"r1k2val"},
{"key":"r1kval2","key2":"r1k2val2"}
]');
INSERT INTO foo (id,f1,f2,js) VALUES (2,'My Text 2','My Text 2a',
'[
{"key":"r2kval","key2":"r2k2val"},
{"key":"r2kval2","key2":"r2k2val2"}
]');

If I want all 4 of my fields, all I can think to do is join your query back
to the table. Something like this:

=> SELECT id,f1,f2,array_agg AS vals FROM foo LEFT JOIN (select id,
array_agg(fa) from (select id, (jsonb_array_elements(js)->>'key') as fa
from foo) g group by id) foo2 USING (id);

id | f1 | f2 | vals
----+-----------+------------+------------------
1 | My Text 1 | My Text 1a | {r1kval,r1kval2}
2 | My Text 2 | My Text 2a | {r2kval,r2kval2}
(2 rows)

That seems to work, but is there any other way to streamline or simplify
that?

Cumbersome is in the eyes of the beholder ;)

Maybe. There's probably an aesthetic component, but also an aspect that
can be quantified, likely in character counts. :)

I'm of course very glad Postgresql has the ability to work with JSON at
all, but as I dig into it I'm kinda surprised at the level of complexity
needed to extract data in relatively simple ways. Hopefully eventually it
will seem simple to me, as it seems to appear to others.

Cheers,
Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/&gt;*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

On Monday, December 7, 2020, Ken Tanzer <ken.tanzer@gmail.com> wrote:

I'm of course very glad Postgresql has the ability to work with JSON at
all, but as I dig into it I'm kinda surprised at the level of complexity
needed to extract data in relatively simple ways. Hopefully eventually it
will seem simple to me, as it seems to appear to others.

Upgrade to v12+ for access to simpler/cleaner. Composing various unnesting
and key extraction operations works but, yes, it gets ugly proportional to
the extent you need to dig into complex json structures. That said lateral
joining reduces nesting which is measurably cleaner.

David J.

How about this:

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

Steve

On Tue, Dec 8, 2020 at 1:00 PM David G. Johnston <david.g.johnston@gmail.com>
wrote:

On Mon, Dec 7, 2020 at 7:12 PM Steve Baldwin <steve.baldwin@gmail.com>
wrote:

How about this:

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

Oh I like that, and thanks! It seems a little clearer to me, but maybe
that's because records still seem more familiar than json. Applying the
quantitative cumbersome-syntax test, this clocks in 8 characters shorter
than the other one (99 vs. 107). 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)

That clocks in at 109 characters, compared to 178 for the similar query we
previously had:

SELECT id,f1,f2,array_agg AS vals FROM foo LEFT JOIN (select id,
array_agg(fa) from (select id, (jsonb_array_elements(js)->>'key') as fa
from foo) g group by id) foo2 USING (id);

Upgrade to v12+ for access to simpler/cleaner.

I can't upgrade just yet, but that is something to look forward to. Out of
curiosity, what would an equivalent query look like in V12?

Cheers,
Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/&gt;*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

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:

=> 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/&gt;*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@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/&gt;*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

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:

Ken Tanzer <ken.tanzer@gmail.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 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 foo.id isn't a primary key, then I'm confused too. Can we see the
full declaration of the table?

regards, tom lane

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.baldwin@gmail.com>
wrote:

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

Ken Tanzer <ken.tanzer@gmail.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/&gt;*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

Ken Tanzer <ken.tanzer@gmail.com> writes:

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

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?

Sorry I was vague there, it's the parse analysis phase that understands
that "GROUP BY a primary key" should be treated as allowing any column of
that pkey's table to be referenced without also explicitly grouping by
that other column. If you then join to some other table, the free pass
doesn't extend to the other table.

Or more specifically, is this some specialized case
because of something related to use of the jsonb_recordset function?

Nope, unrelated to that.

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.

We did not use to have this bit of logic, so maybe your habits were
formed a few years ago. But as I said, I think the SQL spec says
this should be OK. Definitely, there are other DBMSes that also
allow it.

regards, tom lane