BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t254041psql -h localhost -U postgresBuilt from patchset v7 (message #7), October 24, 2026 at 10:57 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t254041_7 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t254041_7 && git checkout t254041_7Patchset v7 (message #7) is on t254041_7
The following bug has been logged on the website:
Bug reference: 19736
Logged by: Shallow
Email address: theshallow27@gmail.com
PostgreSQL version: 18.6
Operating system: Linux
Description:
When `ABSENT ON NULL` removes a pair because its value is SQL NULL,
`JSON_OBJECT` also skips validation of that pair's key. The documented
restrictions say that a key cannot be NULL and must be a scalar expression
without a cast to JSON. Those restrictions should still apply when the value
pair will be omitted.
**Reproduction:**
```sql
SELECT JSON_OBJECT((NULL::text) VALUE NULL::text
ABSENT ON NULL RETURNING json);
SELECT JSON_OBJECT((ARRAY[1,2]) VALUE NULL::text
ABSENT ON NULL RETURNING json);
```
**Actual result:** Both statements return `{}`.
**Expected result:** Both statements should reject the invalid key, as the
key
restrictions apply regardless of whether the value is omitted.
Hi,
I reproduced both examples on master at 9e7d152488f1.
In json_build_object_worker(), the ABSENT ON NULL skip happens before
NULL-key validation. Both json and jsonb paths also skip the scalar-key
checks when omitting an entry without a uniqueness check.
The attached patch moves the json NULL-key check before the skip and
validates the key type before omitting an entry in either output path.
It avoids calling output or cast functions for omitted keys and preserves
the existing uniqueness checks.
Regression tests cover NULL, array, composite, json and jsonb keys with
NULL values. The new tests fail without the fix; with the patch, make
check passes all 239 tests on macOS/arm64 with assertions enabled.
Comments are welcome.
Thanks,
Yeongjae Min
On Fri, 02 Oct 2026 22:35:57 +0000, PG Bug reporting form
<noreply@postgresql.org> wrote:
Show quoted text
The following bug has been logged on the website:
Bug reference: 19736
Logged by: Shallow
Email address: theshallow27@gmail.com
PostgreSQL version: 18.6
Operating system: Linux
Description:When `ABSENT ON NULL` removes a pair because its value is SQL NULL,
`JSON_OBJECT` also skips validation of that pair's key. The documented
restrictions say that a key cannot be NULL and must be a scalar expression
without a cast to JSON. Those restrictions should still apply when the value
pair will be omitted.**Reproduction:**
```sql
SELECT JSON_OBJECT((NULL::text) VALUE NULL::text
ABSENT ON NULL RETURNING json);SELECT JSON_OBJECT((ARRAY[1,2]) VALUE NULL::text
ABSENT ON NULL RETURNING json);
```**Actual result:** Both statements return `{}`.
**Expected result:** Both statements should reject the invalid key, as the
key
restrictions apply regardless of whether the value is omitted.
Hi,
The attached patch addresses the SQL NULL-key part of this report. It was prepared with AI assistance, including the local build and test execution.
In json_build_object_worker(), the NULL-key check currently follows the ABSENT ON NULL skip. Moving that check before the skip makes JSON_OBJECT(NULL: NULL ABSENT ON NULL) reject the NULL key, as the RETURNING jsonb path already does. The existing error messages and SQLSTATEs are preserved.
The patch adds regression coverage for NULL keys with ABSENT ON NULL, for both json and jsonb output, with and without WITH UNIQUE KEYS. It does not address the separate array, composite, or JSON-key validation cases in this report.
The patch was built and tested against PostgreSQL master commit 9e7d152488f197e96f171f0714459c227c3f7693. With only the new regression tests applied, sqljson failed at the new NULL-key/ABSENT/json case. With the code change applied, sqljson and all nine JSON-family regression tests passed. Existing coverage for legitimate NULL-value omission and duplicate keys with omitted values continued to pass. git diff --check also passed.
Those are the results from the earlier local test run, not a new test run today. The environment was Linux x86_64, GCC 14.2.0, with --enable-cassert --without-readline --without-zlib --without-icu and CFLAGS='-O0 -g0'. This was focused regression testing, not the full PostgreSQL test suite or cross-platform validation.
Before sending, I rechecked that master still points to the same commit and verified that the attached patch passes git apply --check against freshly retrieved source files at that commit.
Comments on this narrowly scoped fix are welcome.
Hi,
Your patch covers the non-scalar-key cases as well as the NULL-key case addressed by my earlier patch. Please consider your patch in place of mine.
With AI assistance, I reviewed and tested your attached v1 patch against 9e7d152488f197e96f171f0714459c227c3f7693 on Linux x86_64 with GCC 14.2.0 and assertions enabled. All 239 core regression tests passed via make installcheck.
An additional 260-case matrix passed, covering both output types, NULL and non-NULL values, uniqueness checks, scalar keys, arrays, composites, json/jsonb keys, domains, and a user-defined cast to json. Comparing against the unpatched build, 19 previously accepted invalid-key cases were rejected; the other 241 cases retained their results and error behavior.
I also checked omitted keys using a custom type whose output function raises an error. The output function remained uncalled without uniqueness checking, while the existing conversion behavior with WITH UNIQUE KEYS was preserved. Volatile key/value expressions and value-expression errors retained their observed evaluation behavior.
I did not find a correctness issue in these checks.
Hi,
Thanks for reviewing the patch and for the additional testing,
especially the Linux/x86_64 coverage and evaluation behavior checks.
Regards,
Yeongjae Min
On Sun, Oct 11, 2026 at 07:26:02PM +0900, 민영재 wrote:
I reproduced both examples on master at 9e7d152488f1.In json_build_object_worker(), the ABSENT ON NULL skip happens before
NULL-key validation. Both json and jsonb paths also skip the scalar-key
checks when omitting an entry without a uniqueness check.The attached patch moves the json NULL-key check before the skip and
validates the key type before omitting an entry in either output path.
It avoids calling output or cast functions for omitted keys and preserves
the existing uniqueness checks.Regression tests cover NULL, array, composite, json and jsonb keys with
NULL values. The new tests fail without the fix; with the patch, make
check passes all 239 tests on macOS/arm64 with assertions enabled.
Thanks for investigating. I think it's worth to clarify the
documentation a bit -- one can argue that we don't need to verify keys
if the pair is going to be skipped, and only the wording happen to
insist on it. Currently it says:
<replaceable>key_expression</replaceable> is a scalar expression
defining the <acronym>JSON</acronym> key, which is
converted to the <type>text</type> type.
It cannot be <literal>NULL</literal> nor can it
belong to a type that has a cast to the <type>json</type> type.
Maybe extending it to "It cannot be ..., even if the pair is going to be
eventually omitted due to ABSENT ON NULL".
It also probably makes sense to verify if there are more such examples.
E.g. looks like jsonb_object_agg_strict exhibit something similar:
-- jsonb_object_agg_transfn_worker tests if the key is NULL
select jsonb_object_agg_strict(NULL, NULL);
ERROR: 22023: field name must not be null
-- it also checks if the key is not a composite or json, if the
-- value is not NULL
select jsonb_object_agg_strict('{"a": 1}'::jsonb, 'value');
ERROR: 22023: key value must be scalar, not array, composite, or json
-- but it doesn't do so if the value is NULL
select jsonb_object_agg_strict('{"a": 1}'::jsonb, NULL);
jsonb_object_agg_strict
-------------------------
{}
(1 row)
Regarding the proposed patch, maybe the condition on tcategory could be
factored out to not repeat it few times (including one in the
datum_to_jsonb_internal). It's also not clear to me why JSONTYPE_JSONB
is not included in json_build_object_worker, shouldn't it be included
since it has a cast to json?
Hi Dmitry,
Thanks for the suggestions. V2 is attached.
I found the same issue in json_object_agg_strict() and
jsonb_object_agg_strict() and extended the fix to those workers,
also covering JSON_OBJECTAGG with ABSENT ON NULL.
V2 shares the category checks through json_validate_key_category()
and clarifies that key restrictions apply even to omitted pairs.
For JSONTYPE_JSONB, json_categorize_type(..., false, ...) maps jsonb
to JSONTYPE_JSON, so v1 already covered it in the json path.
The shared helper explicitly handles both categories.
All 239 core regression tests pass on macOS/arm64 with assertions
enabled, on master at 7787aec8ab00, including the added aggregate tests.
I also verified that omitted keys still avoid output conversion without
uniqueness checking, while WITH UNIQUE KEYS retains its behavior.
Thanks,
Yeongjae Min
Show quoted text
On Fri, 16 Oct 2026 22:41:27 +0200, Dmitry Dolgov <9erthalion6@gmail.com> wrote:
On Sun, Oct 11, 2026 at 07:26:02PM +0900, 민영재 wrote:
I reproduced both examples on master at 9e7d152488f1.In json_build_object_worker(), the ABSENT ON NULL skip happens before
NULL-key validation. Both json and jsonb paths also skip the scalar-key
checks when omitting an entry without a uniqueness check.The attached patch moves the json NULL-key check before the skip and
validates the key type before omitting an entry in either output path.
It avoids calling output or cast functions for omitted keys and preserves
the existing uniqueness checks.Regression tests cover NULL, array, composite, json and jsonb keys with
NULL values. The new tests fail without the fix; with the patch, make
check passes all 239 tests on macOS/arm64 with assertions enabled.Thanks for investigating. I think it's worth to clarify the
documentation a bit -- one can argue that we don't need to verify keys
if the pair is going to be skipped, and only the wording happen to
insist on it. Currently it says:<replaceable>key_expression</replaceable> is a scalar expression
defining the <acronym>JSON</acronym> key, which is
converted to the <type>text</type> type.
It cannot be <literal>NULL</literal> nor can it
belong to a type that has a cast to the <type>json</type> type.Maybe extending it to "It cannot be ..., even if the pair is going to be
eventually omitted due to ABSENT ON NULL".It also probably makes sense to verify if there are more such examples.
E.g. looks like jsonb_object_agg_strict exhibit something similar:-- jsonb_object_agg_transfn_worker tests if the key is NULL
select jsonb_object_agg_strict(NULL, NULL);
ERROR: 22023: field name must not be null-- it also checks if the key is not a composite or json, if the
-- value is not NULL
select jsonb_object_agg_strict('{"a": 1}'::jsonb, 'value');
ERROR: 22023: key value must be scalar, not array, composite, or json-- but it doesn't do so if the value is NULL
select jsonb_object_agg_strict('{"a": 1}'::jsonb, NULL);
jsonb_object_agg_strict
-------------------------
{}
(1 row)Regarding the proposed patch, maybe the condition on tcategory could be
factored out to not repeat it few times (including one in the
datum_to_jsonb_internal). It's also not clear to me why JSONTYPE_JSONB
is not included in json_build_object_worker, shouldn't it be included
since it has a cast to json?