BUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL
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>
Дата:
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.
Re: BUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL
От:
shihao zhong <zhong950419@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%3DiQkQMj77cQyoNkqtf%3D2Q@mail.gmail.com&source=gmail&ust=1791228842559000&sa=E
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%3DiQkQMj77cQyoNkqtf%3D2Q@mail.gmail.com&source=gmail&ust=1791228842559000&sa=E
Re: BUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL
От:
Dmitry Dolgov <9erthalion6@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.
Re: BUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL
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
Re: BUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL
От:
Dmitry Dolgov <9erthalion6@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.
Re: BUG #19735: `jsonb_object_agg_unique_strict` drops a JSONB `null` value as if it were SQL NULL
От:
shihao zhong <zhong950419@gmail.com>
Дата:
Hi Dmitry,
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.
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.
> 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