pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL
6+ messages / 3 participants
[nested] [flat]
* BUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL
@ 2026-10-02 22:03 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 6+ messages in thread
From: PG Bug reporting form @ 2026-10-02 22:03 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: theshallow27@gmail.com
The following bug has been logged on the website:
Bug reference: 19735
Logged by: Shallow
Email address: theshallow27@gmail.com
PostgreSQL version: 18.6
Operating system: Linux
Description:
The strict JSONB object aggregate omits a non-SQL-NULL `jsonb` datum whose
JSON
value is `null`. The corresponding non-strict JSONB aggregate and strict
JSON
aggregate preserve the key with a JSON null value. The aggregate
documentation
says that strict mode skips a value when the value is NULL; a JSONB `null`
value
is distinct from SQL NULL.
**Reproduction:**
```sql
SELECT jsonb_object_agg_unique_strict('a', 'null'::jsonb)::text,
jsonb_object_agg_unique('a', 'null'::jsonb)::text,
json_object_agg_unique_strict('a', 'null'::jsonb)::text;
```
**Actual result:**
```text
{} | {"a": null} | { "a" : null }
```
**Expected result:** The strict JSONB aggregate should skip SQL NULL values
but
preserve a JSONB datum containing JSON `null`, consistent with the other two
aggregate results.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL
@ 2026-10-04 19:38 shihao zhong <zhong950419@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 6+ messages in thread
From: shihao zhong @ 2026-10-04 19:38 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: theshallow27@gmail.com
Hi,
I can reproduce this on master. The same happens with all the
skip_nulls paths, such as jsonb_object_agg_strict(), and JSON_OBJECT()
and JSON_OBJECTAGG() using ABSENT ON NULL RETURNING jsonb. The jsonb
array versions keep the value, jsonb_agg_strict('null'::jsonb) returns
[null].
Is dropping JSON nulls here intended? I looked at the old threads
where skip_nulls was discussed [1][2], they are only about SQL NULLs.
The docs just say "null".
If people want this changed I can draft a patch.
[1]
https://www.google.com/url?q=https://postgr.es/m/CAJA4AWQ_XbSmsNbW226UqNyRLJ%2Bwb%3DiQkQMj77cQyoNkqt...
[2]
https://www.google.com/url?q=https://postgr.es/m/CA%2BHiwqGAJb%2BmQKFPf6Vov9HsdzCSq21Guy4uwerZDAyWWZ...
Thanks,
Shihao
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL
@ 2026-10-07 09:58 Dmitry Dolgov <9erthalion6@gmail.com>
parent: shihao zhong <zhong950419@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Dmitry Dolgov @ 2026-10-07 09:58 UTC (permalink / raw)
To: shihao zhong <zhong950419@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org; theshallow27@gmail.com
> On Sun, Oct 04, 2026 at 03:38:11PM -0400, shihao zhong wrote:
>
> I can reproduce this on master. The same happens with all the
> skip_nulls paths, such as jsonb_object_agg_strict(), and JSON_OBJECT()
> and JSON_OBJECTAGG() using ABSENT ON NULL RETURNING jsonb. The jsonb
> array versions keep the value, jsonb_agg_strict('null'::jsonb) returns
> [null].
>
> Is dropping JSON nulls here intended? I looked at the old threads
> where skip_nulls was discussed [1][2], they are only about SQL NULLs.
> The docs just say "null".
Hm, that's annoying. My gut feeling is that for jsonb it was intended to
skip JSON nulls -- the aggregation functions prepares a state first time
to use it on the subsequent rounds, and JsonbParseState.skip_nulls
inside it clearly refers to JSON nulls:
bool skip_nulls; /* Skip null object fields */
It also makes sense from the user perspective, since applications that
use jsonb more often have to deal with JSON nulls rather than SQL NULLs.
So it's reasonable to have functionality to deal with a frequent case,
instead of requiring to call jsonb_typeof on every element.
Now, for json version it seems to be different, as it's state
JsonAggState does not have anything like that, and consequently refers
to the problem as "NULL values".
In the end we have two problems at hand:
* The aggregation functions documentation needs to be more clear which
nulls are meant there. Currently it's JSON null and SQL NULL for jsonb
functions, and SQL NULL for json functions.
* It would be great to unify the experience for both jsonb and json
functions. Unfortunately it looks like independently from the decision
(whether to make all functions skip JSON nulls and SQL NULLs or SQL
NULLs only), the solution probably would not be pretty, as there will
be need to categorize every incoming element. I'm inclined to make
both sets of functions to skip both JSON null and SQL NULL.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL
@ 2026-10-08 02:30 shihao zhong <zhong950419@gmail.com>
parent: Dmitry Dolgov <9erthalion6@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: shihao zhong @ 2026-10-08 02:30 UTC (permalink / raw)
To: Dmitry Dolgov <9erthalion6@gmail.com>; Álvaro Herrera <alvherre@kurilemu.de>; Andrew Dunstan <andrew@dunslane.net>; +Cc: pgsql-bugs@lists.postgresql.org; theshallow27@gmail.com
Hi Dmitry,
I agree the docs should say which nulls are meant.
I am not sure it was intended though. The jsonb array functions keep
a JSON null, and JSON_OBJECT() drops the key only with RETURNING
jsonb:
SELECT JSON_OBJECT('a': 'null'::jsonb ABSENT ON NULL RETURNING jsonb),
JSON_OBJECT('a': 'null'::jsonb ABSENT ON NULL RETURNING json);
{} | {"a" : null}
Skipping only SQL NULLs needs no check per element in jsonb, the
caller knows which values it skipped. So I lean to SQL NULL only for
both. I would like a committer to pick, and the doc patch is attached.
Thanks,
Shihao
Attachments:
v1-0001-doc-Say-which-nulls-the-strict-JSON-object-functi.patch (application/octet-stream, 2.6K) download
From 375baddd9ec7f297c2087f65879c67eaa3c81a86 Mon Sep 17 00:00:00 2001
From: shihao zhong <zhong950419@gmail.com>
Date: Wed, 7 Oct 2026 22:28:07 -0400
Subject: [PATCH v1] doc: Say which nulls the strict JSON object functions skip
The json functions skip only SQL NULL values. The jsonb ones, and
JSON_OBJECT() and JSON_OBJECTAGG() with ABSENT ON NULL RETURNING
jsonb, also skip a JSON null. The docs only said "null".
Discussion: https://postgr.es/m/19735-488819822dc79bca@postgresql.org
---
doc/src/sgml/func/func-aggregate.sgml | 6 +++++-
doc/src/sgml/func/func-json.sgml | 3 +++
2 files changed, 8 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/func/func-aggregate.sgml b/doc/src/sgml/func/func-aggregate.sgml
index 6a6a4d7e32e..a16d797541a 100644
--- a/doc/src/sgml/func/func-aggregate.sgml
+++ b/doc/src/sgml/func/func-aggregate.sgml
@@ -432,7 +432,9 @@
are coerced to text; value arguments are converted as per
<function>to_json</function> or <function>to_jsonb</function>.
The <parameter>key</parameter> cannot be null. If the
- <parameter>value</parameter> is null then the entry is skipped,
+ <parameter>value</parameter> is null then the entry is skipped.
+ <function>jsonb_object_agg_strict</function> also skips an entry
+ whose value is a JSON <literal>null</literal>.
</para></entry>
<entry>No</entry>
</row>
@@ -491,6 +493,8 @@
<function>to_json</function> or <function>to_jsonb</function>.
The <parameter>key</parameter> cannot be null. If the
<parameter>value</parameter> is null then the entry is skipped.
+ <function>jsonb_object_agg_unique_strict</function> also skips an
+ entry whose value is a JSON <literal>null</literal>.
If there is a duplicate key an error is thrown.
</para></entry>
<entry>No</entry>
diff --git a/doc/src/sgml/func/func-json.sgml b/doc/src/sgml/func/func-json.sgml
index 0763ec81ed8..bb939e792f5 100644
--- a/doc/src/sgml/func/func-json.sgml
+++ b/doc/src/sgml/func/func-json.sgml
@@ -731,6 +731,9 @@
if <literal>ABSENT ON NULL</literal> is specified;
if <literal>NULL ON NULL</literal> is specified or the clause
omitted, the key is included with value <literal>NULL</literal>.
+ With <literal>ABSENT ON NULL</literal> and
+ <literal>RETURNING jsonb</literal>, a pair whose value is a JSON
+ <literal>null</literal> is omitted as well.
</para>
<para>
<literal>json_object('code' VALUE 'P123', 'title': 'Jaws')</literal>
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL
@ 2026-10-08 08:09 Dmitry Dolgov <9erthalion6@gmail.com>
parent: shihao zhong <zhong950419@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Dmitry Dolgov @ 2026-10-08 08:09 UTC (permalink / raw)
To: shihao zhong <zhong950419@gmail.com>; +Cc: Álvaro Herrera <alvherre@kurilemu.de>; Andrew Dunstan <andrew@dunslane.net>; pgsql-bugs@lists.postgresql.org; theshallow27@gmail.com
> On Wed, Oct 07, 2026 at 10:30:48PM -0400, shihao zhong wrote:
> Hi Dmitry,
>
> I agree the docs should say which nulls are meant.
>
> I am not sure it was intended though. The jsonb array functions keep
> a JSON null
This is the one difference from most of the other functions,
jsonb_agg_transfn_worker for some reason does not set skip_nulls, even
though it has absent_on_null available. jsonb_object_agg_transfn_worker
does it like:
result->parseState->skip_nulls = absent_on_null;
I presume jsonb_agg_transfn_worker have to be unified with the others.
> , and JSON_OBJECT() drops the key only with RETURNING jsonb:
From what I see it drops nulls due to ABSENT ON NULL, e.g. ABSENT ON
NULL WITHOUT UNIQUE KEYS will do the same. Otherwise ditto,
jsonb_build_object_worker sets skip_nulls like above if ABSENT ON NULL
is specified.
> Skipping only SQL NULLs needs no check per element in jsonb, the
> caller knows which values it skipped.
I mean to check per every element in the to be aggregated set.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL
@ 2026-10-09 03:30 shihao zhong <zhong950419@gmail.com>
parent: Dmitry Dolgov <9erthalion6@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: shihao zhong @ 2026-10-09 03:30 UTC (permalink / raw)
To: Dmitry Dolgov <9erthalion6@gmail.com>; +Cc: Álvaro Herrera <alvherre@kurilemu.de>; Andrew Dunstan <andrew@dunslane.net>; pgsql-bugs@lists.postgresql.org; theshallow27@gmail.com
Hi Dmitry,
> I presume jsonb_agg_transfn_worker have to be unified with the others.
I don't think the array code forgot it. skip_nulls is only used when
an object is finished, in uniqueifyJsonbObject(). Arrays never get
there, so setting it in jsonb_agg_transfn_worker() would change
nothing.
> From what I see it drops nulls due to ABSENT ON NULL
Right, ABSENT ON NULL is what drops it. What I meant is that the same
call keeps the key when it returns json:
ABSENT ON NULL RETURNING jsonb {}
ABSENT ON NULL RETURNING json {"a" : null}
So ABSENT ON NULL means two different things, depending on the result type.
> I mean to check per every element in the to be aggregated set.
For SQL NULL that check is PG_ARGISNULL(), which is already there.
Thanks,
Shihao
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-10-09 03:30 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-02 22:03 BUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL PG Bug reporting form <noreply@postgresql.org>
2026-10-04 19:38 ` shihao zhong <zhong950419@gmail.com>
2026-10-07 09:58 ` Dmitry Dolgov <9erthalion6@gmail.com>
2026-10-08 02:30 ` shihao zhong <zhong950419@gmail.com>
2026-10-08 08:09 ` Dmitry Dolgov <9erthalion6@gmail.com>
2026-10-09 03:30 ` shihao zhong <zhong950419@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox