pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #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>
  2026-10-04 19:38 ` Re: BUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL shihao zhong <zhong950419@gmail.com>
  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-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   ` Re: BUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL Dmitry Dolgov <9erthalion6@gmail.com>
  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-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 ` Re: BUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL shihao zhong <zhong950419@gmail.com>
@ 2026-10-07 09:58   ` Dmitry Dolgov <9erthalion6@gmail.com>
  2026-10-08 02:30     ` Re: BUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL 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-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 ` Re: BUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL shihao zhong <zhong950419@gmail.com>
  2026-10-07 09:58   ` Re: BUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL Dmitry Dolgov <9erthalion6@gmail.com>
@ 2026-10-08 02:30     ` shihao zhong <zhong950419@gmail.com>
  2026-10-08 08:09       ` Re: BUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL 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-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 ` Re: BUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL shihao zhong <zhong950419@gmail.com>
  2026-10-07 09:58   ` Re: BUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL Dmitry Dolgov <9erthalion6@gmail.com>
  2026-10-08 02:30     ` Re: BUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL shihao zhong <zhong950419@gmail.com>
@ 2026-10-08 08:09       ` Dmitry Dolgov <9erthalion6@gmail.com>
  2026-10-09 03:30         ` Re: BUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL 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-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 ` Re: BUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL shihao zhong <zhong950419@gmail.com>
  2026-10-07 09:58   ` Re: BUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL Dmitry Dolgov <9erthalion6@gmail.com>
  2026-10-08 02:30     ` Re: BUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL shihao zhong <zhong950419@gmail.com>
  2026-10-08 08:09       ` Re: BUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL Dmitry Dolgov <9erthalion6@gmail.com>
@ 2026-10-09 03:30         ` shihao zhong <zhong950419@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