pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL
10+ messages / 5 participants
[nested] [flat]
* BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL
@ 2026-10-02 22:35 PG Bug reporting form <noreply@postgresql.org>
2026-10-11 10:26 ` Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL 민영재 <dudwo990413@gmail.com>
2026-10-12 03:42 ` Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL QQ Mail <781408517@qq.com>
0 siblings, 2 replies; 10+ messages in thread
From: PG Bug reporting form @ 2026-10-02 22:35 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: theshallow27@gmail.com
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL
2026-10-02 22:35 BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL PG Bug reporting form <noreply@postgresql.org>
@ 2026-10-11 10:26 ` 민영재 <dudwo990413@gmail.com>
2026-10-12 06:35 ` Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL QQ Mail <781408517@qq.com>
2026-10-16 20:41 ` Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL Dmitry Dolgov <9erthalion6@gmail.com>
1 sibling, 2 replies; 10+ messages in thread
From: 민영재 @ 2026-10-11 10:26 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: theshallow27@gmail.com
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:
> 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.
Attachments:
v1-0001-Validate-JSON-object-keys-before-omitting-null-values.patch (application/octet-stream, 8.4K) download
Subject: [PATCH] Validate JSON object keys before omitting null values
JSON_OBJECT with ABSENT ON NULL can skip key validation when it omits
an entry with a null value. In the json path this accepts null keys;
both json and jsonb paths can accept non-scalar keys. The same keys are
rejected when the value is non-null or key uniqueness is requested.
Move the json null-key check before the skip decision. Before skipping
an entry without a uniqueness check, classify the key type and reject
the same categories rejected by the existing conversion routines. This
avoids invoking output or cast functions for omitted keys.
Add regression coverage for null, array, composite, json and jsonb keys
with null values, including keys with a cast to json and the existing
null-key checks with unique keys.
Reported in BUG #19736:
https://www.postgresql.org/message-id/19736-3a44617ea5534c77@postgresql.org
diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c
index 28e5f3c..ae4f57d 100644
--- a/src/backend/utils/adt/json.c
+++ b/src/backend/utils/adt/json.c
@@ -1220,6 +1220,12 @@ json_build_object_worker(int nargs, const Datum *args, const bool *nulls, const
bool skip;
int key_offset;
+ /* Keys must not be null, even if their values will be omitted. */
+ if (nulls[i])
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("null value not allowed for object key")));
+
/* Skip null values if absent_on_null */
skip = absent_on_null && nulls[i + 1];
@@ -1227,7 +1233,21 @@ json_build_object_worker(int nargs, const Datum *args, const bool *nulls, const
{
/* If key uniqueness check is needed we must save skipped keys */
if (!unique_keys)
+ {
+ JsonTypeCategory tcategory;
+ Oid outfuncoid;
+
+ /* Validate the key type without converting the omitted key. */
+ json_categorize_type(types[i], false, &tcategory, &outfuncoid);
+ if (tcategory == JSONTYPE_ARRAY ||
+ tcategory == JSONTYPE_COMPOSITE ||
+ tcategory == JSONTYPE_JSON ||
+ tcategory == JSONTYPE_CAST)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("key value must be scalar, not array, composite, or json")));
continue;
+ }
out = json_unique_builder_get_throwawaybuf(&unique_check);
}
@@ -1238,12 +1258,6 @@ json_build_object_worker(int nargs, const Datum *args, const bool *nulls, const
out = result;
}
- /* process key */
- if (nulls[i])
- ereport(ERROR,
- (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
- errmsg("null value not allowed for object key")));
-
/* save key offset before appending it */
key_offset = out->len;
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index da0061b..c8f1252 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -1162,7 +1162,22 @@ jsonb_build_object_worker(int nargs, const Datum *args, const bool *nulls, const
/* we need to save skipped keys for the key uniqueness check */
if (skip && !unique_keys)
+ {
+ JsonTypeCategory tcategory;
+ Oid outfuncoid;
+
+ /* Validate the key type without converting the omitted key. */
+ json_categorize_type(types[i], true, &tcategory, &outfuncoid);
+ if (tcategory == JSONTYPE_ARRAY ||
+ tcategory == JSONTYPE_COMPOSITE ||
+ tcategory == JSONTYPE_JSON ||
+ tcategory == JSONTYPE_JSONB ||
+ tcategory == JSONTYPE_CAST)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("key value must be scalar, not array, composite, or json")));
continue;
+ }
add_jsonb(args[i], false, &result, types[i], true);
diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out
index 30f9e10..29e2ee8 100644
--- a/src/test/regress/expected/sqljson.out
+++ b/src/test/regress/expected/sqljson.out
@@ -539,6 +539,31 @@ SELECT JSON_OBJECT('a': '1', 'b': NULL, 'c': 2 ABSENT ON NULL);
{"a" : "1", "c" : 2}
(1 row)
+-- Keys must be valid even when ABSENT ON NULL omits their values.
+SELECT JSON_OBJECT((NULL::text) VALUE NULL::text ABSENT ON NULL RETURNING json);
+ERROR: null value not allowed for object key
+SELECT JSON_OBJECT((NULL::text) VALUE NULL::text ABSENT ON NULL WITH UNIQUE KEYS RETURNING json);
+ERROR: null value not allowed for object key
+SELECT JSON_OBJECT((ARRAY[1,2]) VALUE NULL::text ABSENT ON NULL RETURNING json);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECT((ROW(1,2)) VALUE NULL::text ABSENT ON NULL RETURNING json);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECT((json '1') VALUE NULL::text ABSENT ON NULL RETURNING json);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECT((jsonb '1') VALUE NULL::text ABSENT ON NULL RETURNING json);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECT((NULL::text) VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+ERROR: argument 1: key must not be null
+SELECT JSON_OBJECT((NULL::text) VALUE NULL::text ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb);
+ERROR: argument 1: key must not be null
+SELECT JSON_OBJECT((ARRAY[1,2]) VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECT((ROW(1,2)) VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECT((json '1') VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECT((jsonb '1') VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+ERROR: key value must be scalar, not array, composite, or json
SELECT JSON_OBJECT(1: 1, '2': NULL, '3': 1, repeat('x', 1000): 1, 2: repeat('a', 100) WITH UNIQUE);
ERROR: duplicate JSON object key value: "2"
SELECT JSON_OBJECT(1: 1, '1': NULL WITH UNIQUE);
@@ -607,6 +632,10 @@ $$ LANGUAGE sql IMMUTABLE;
CREATE CAST (mood AS json) WITH FUNCTION mood_to_json(mood) AS IMPLICIT;
SELECT JSON_OBJECT('happy'::mood: '123'::jsonb);
ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECT('happy'::mood: NULL ABSENT ON NULL RETURNING json);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECT('happy'::mood: NULL ABSENT ON NULL RETURNING jsonb);
+ERROR: key value must be scalar, not array, composite, or json
DROP CAST (mood AS json);
DROP FUNCTION mood_to_json;
DROP TYPE mood;
diff --git a/src/test/regress/sql/sqljson.sql b/src/test/regress/sql/sqljson.sql
index 9fa203c..030685b 100644
--- a/src/test/regress/sql/sqljson.sql
+++ b/src/test/regress/sql/sqljson.sql
@@ -138,6 +138,20 @@ SELECT JSON_OBJECT('a': '1', 'b': NULL, 'c': 2);
SELECT JSON_OBJECT('a': '1', 'b': NULL, 'c': 2 NULL ON NULL);
SELECT JSON_OBJECT('a': '1', 'b': NULL, 'c': 2 ABSENT ON NULL);
+-- Keys must be valid even when ABSENT ON NULL omits their values.
+SELECT JSON_OBJECT((NULL::text) VALUE NULL::text ABSENT ON NULL RETURNING json);
+SELECT JSON_OBJECT((NULL::text) VALUE NULL::text ABSENT ON NULL WITH UNIQUE KEYS RETURNING json);
+SELECT JSON_OBJECT((ARRAY[1,2]) VALUE NULL::text ABSENT ON NULL RETURNING json);
+SELECT JSON_OBJECT((ROW(1,2)) VALUE NULL::text ABSENT ON NULL RETURNING json);
+SELECT JSON_OBJECT((json '1') VALUE NULL::text ABSENT ON NULL RETURNING json);
+SELECT JSON_OBJECT((jsonb '1') VALUE NULL::text ABSENT ON NULL RETURNING json);
+SELECT JSON_OBJECT((NULL::text) VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+SELECT JSON_OBJECT((NULL::text) VALUE NULL::text ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb);
+SELECT JSON_OBJECT((ARRAY[1,2]) VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+SELECT JSON_OBJECT((ROW(1,2)) VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+SELECT JSON_OBJECT((json '1') VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+SELECT JSON_OBJECT((jsonb '1') VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+
SELECT JSON_OBJECT(1: 1, '2': NULL, '3': 1, repeat('x', 1000): 1, 2: repeat('a', 100) WITH UNIQUE);
SELECT JSON_OBJECT(1: 1, '1': NULL WITH UNIQUE);
@@ -171,6 +185,8 @@ CREATE FUNCTION mood_to_json(mood) RETURNS json AS $$
$$ LANGUAGE sql IMMUTABLE;
CREATE CAST (mood AS json) WITH FUNCTION mood_to_json(mood) AS IMPLICIT;
SELECT JSON_OBJECT('happy'::mood: '123'::jsonb);
+SELECT JSON_OBJECT('happy'::mood: NULL ABSENT ON NULL RETURNING json);
+SELECT JSON_OBJECT('happy'::mood: NULL ABSENT ON NULL RETURNING jsonb);
DROP CAST (mood AS json);
DROP FUNCTION mood_to_json;
DROP TYPE mood;
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL
2026-10-02 22:35 BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL PG Bug reporting form <noreply@postgresql.org>
2026-10-11 10:26 ` Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL 민영재 <dudwo990413@gmail.com>
@ 2026-10-12 06:35 ` QQ Mail <781408517@qq.com>
2026-10-12 07:15 ` Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL 민영재 <dudwo990413@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: QQ Mail @ 2026-10-12 06:35 UTC (permalink / raw)
To: dudwo990413@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL
2026-10-02 22:35 BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL PG Bug reporting form <noreply@postgresql.org>
2026-10-11 10:26 ` Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL 민영재 <dudwo990413@gmail.com>
2026-10-12 06:35 ` Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL QQ Mail <781408517@qq.com>
@ 2026-10-12 07:15 ` 민영재 <dudwo990413@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: 민영재 @ 2026-10-12 07:15 UTC (permalink / raw)
To: QQ Mail <781408517@qq.com>; +Cc: pgsql-bugs@lists.postgresql.org
Hi,
Thanks for reviewing the patch and for the additional testing,
especially the Linux/x86_64 coverage and evaluation behavior checks.
Regards,
Yeongjae Min
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL
2026-10-02 22:35 BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL PG Bug reporting form <noreply@postgresql.org>
2026-10-11 10:26 ` Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL 민영재 <dudwo990413@gmail.com>
@ 2026-10-16 20:41 ` Dmitry Dolgov <9erthalion6@gmail.com>
2026-10-17 14:53 ` Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL 민영재 <dudwo990413@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Dmitry Dolgov @ 2026-10-16 20:41 UTC (permalink / raw)
To: 민영재 <dudwo990413@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org; theshallow27@gmail.com
> 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?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL
2026-10-02 22:35 BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL PG Bug reporting form <noreply@postgresql.org>
2026-10-11 10:26 ` Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL 민영재 <dudwo990413@gmail.com>
2026-10-16 20:41 ` Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL Dmitry Dolgov <9erthalion6@gmail.com>
@ 2026-10-17 14:53 ` 민영재 <dudwo990413@gmail.com>
2026-11-09 07:45 ` Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL 민영재 <dudwo990413@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: 민영재 @ 2026-10-17 14:53 UTC (permalink / raw)
To: Dmitry Dolgov <9erthalion6@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org; theshallow27@gmail.com
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
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?
Attachments:
v2-0001-Validate-JSON-object-keys-before-omitting-null-values.patch (text/x-patch, 16.5K) download
Subject: [PATCH v2] Validate JSON object keys before omitting null values
JSON_OBJECT with ABSENT ON NULL can skip key validation when it omits
an entry with a null value. The json path accepts null keys, and both
json and jsonb paths can accept non-scalar keys. The strict object
aggregate workers have the same non-scalar-key validation gap.
Move the json constructor's null-key check before omission, and validate
key categories in the skipped-entry paths of constructors and aggregates.
Factor the existing category checks into json_validate_key_category(),
shared with datum_to_json_internal() and datum_to_jsonb_internal(). This
preserves the existing error messages and SQLSTATEs and avoids invoking
output or cast functions for omitted entries without uniqueness checks.
Clarify that key restrictions apply even when ABSENT ON NULL omits the
pair, and document the restrictions for strict object aggregates.
Add regression coverage for constructors and aggregates, including
array, composite, json/jsonb and user-defined JSON-cast keys.
Reported in BUG #19736:
https://www.postgresql.org/message-id/19736-3a44617ea5534c77@postgresql.org
diff --git a/doc/src/sgml/func/func-aggregate.sgml b/doc/src/sgml/func/func-aggregate.sgml
index c366989..462f720 100644
--- a/doc/src/sgml/func/func-aggregate.sgml
+++ b/doc/src/sgml/func/func-aggregate.sgml
@@ -434,8 +434,10 @@
Collects all the key/value pairs into a JSON object. Key arguments
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 pair is skipped.
+ The <parameter>key</parameter> must be a non-null scalar and cannot
+ belong to a type that has a cast to <type>json</type>, even if the
+ pair is skipped. If the <parameter>value</parameter> is null then
+ the pair is skipped.
Duplicate keys are handled as for <function>json_object_agg</function>
and <function>jsonb_object_agg</function>, after removing any pairs
with null values.
@@ -495,8 +497,10 @@
Collects all the key/value pairs into a JSON object. Key arguments
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 pair is skipped.
+ The <parameter>key</parameter> must be a non-null scalar and cannot
+ belong to a type that has a cast to <type>json</type>, even if the
+ pair is skipped. If the <parameter>value</parameter> is null then
+ the pair is skipped.
If there is a duplicate key (including pairs with null values)
an error is thrown.
</para></entry>
diff --git a/doc/src/sgml/func/func-json.sgml b/doc/src/sgml/func/func-json.sgml
index 0763ec8..2d2c92e 100644
--- a/doc/src/sgml/func/func-json.sgml
+++ b/doc/src/sgml/func/func-json.sgml
@@ -724,6 +724,8 @@
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.
+ These restrictions apply even if the pair is omitted from the output
+ due to <literal>ABSENT ON NULL</literal>.
If <literal>WITH UNIQUE KEYS</literal> is specified, there must not
be any duplicate <replaceable>key_expression</replaceable>.
Any pair for which the <replaceable>value_expression</replaceable>
diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c
index 28e5f3c..e234e7c 100644
--- a/src/backend/utils/adt/json.c
+++ b/src/backend/utils/adt/json.c
@@ -193,14 +193,8 @@ datum_to_json_internal(Datum val, bool is_null, StringInfo result,
return;
}
- if (key_scalar &&
- (tcategory == JSONTYPE_ARRAY ||
- tcategory == JSONTYPE_COMPOSITE ||
- tcategory == JSONTYPE_JSON ||
- tcategory == JSONTYPE_CAST))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("key value must be scalar, not array, composite, or json")));
+ if (key_scalar)
+ json_validate_key_category(tcategory);
switch (tcategory)
{
@@ -1056,7 +1050,10 @@ json_object_agg_transfn_worker(FunctionCallInfo fcinfo,
* buffer to store the key name so that we can check it.
*/
if (!unique_keys)
+ {
+ json_validate_key_category(state->key_category);
PG_RETURN_POINTER(state);
+ }
out = json_unique_builder_get_throwawaybuf(&state->unique_check);
}
@@ -1220,6 +1217,12 @@ json_build_object_worker(int nargs, const Datum *args, const bool *nulls, const
bool skip;
int key_offset;
+ /* Keys must not be null, even if their values will be omitted. */
+ if (nulls[i])
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("null value not allowed for object key")));
+
/* Skip null values if absent_on_null */
skip = absent_on_null && nulls[i + 1];
@@ -1227,7 +1230,15 @@ json_build_object_worker(int nargs, const Datum *args, const bool *nulls, const
{
/* If key uniqueness check is needed we must save skipped keys */
if (!unique_keys)
+ {
+ JsonTypeCategory tcategory;
+ Oid outfuncoid;
+
+ /* Validate the key type without converting the omitted key. */
+ json_categorize_type(types[i], false, &tcategory, &outfuncoid);
+ json_validate_key_category(tcategory);
continue;
+ }
out = json_unique_builder_get_throwawaybuf(&unique_check);
}
@@ -1238,12 +1249,6 @@ json_build_object_worker(int nargs, const Datum *args, const bool *nulls, const
out = result;
}
- /* process key */
- if (nulls[i])
- ereport(ERROR,
- (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
- errmsg("null value not allowed for object key")));
-
/* save key offset before appending it */
key_offset = out->len;
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 29efd58..6e5adb4 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -645,19 +645,11 @@ datum_to_jsonb_internal(Datum val, bool is_null, JsonbInState *result,
Assert(!key_scalar);
jb.type = jbvNull;
}
- else if (key_scalar &&
- (tcategory == JSONTYPE_ARRAY ||
- tcategory == JSONTYPE_COMPOSITE ||
- tcategory == JSONTYPE_JSON ||
- tcategory == JSONTYPE_JSONB ||
- tcategory == JSONTYPE_CAST))
- {
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("key value must be scalar, not array, composite, or json")));
- }
else
{
+ if (key_scalar)
+ json_validate_key_category(tcategory);
+
switch (tcategory)
{
case JSONTYPE_ARRAY:
@@ -1162,7 +1154,15 @@ jsonb_build_object_worker(int nargs, const Datum *args, const bool *nulls, const
/* we need to save skipped keys for the key uniqueness check */
if (skip && !unique_keys)
+ {
+ JsonTypeCategory tcategory;
+ Oid outfuncoid;
+
+ /* Validate the key type without converting the omitted key. */
+ json_categorize_type(types[i], true, &tcategory, &outfuncoid);
+ json_validate_key_category(tcategory);
continue;
+ }
add_jsonb(args[i], false, &result, types[i], true);
@@ -1645,7 +1645,10 @@ jsonb_object_agg_transfn_worker(FunctionCallInfo fcinfo,
skip = absent_on_null && PG_ARGISNULL(2);
if (skip && !unique_keys)
+ {
+ json_validate_key_category(state->key_category);
PG_RETURN_POINTER(state);
+ }
/*
* We run this code in the normal function context, so that we don't leak
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index adbafd0..7560aa3 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -6065,6 +6065,24 @@ json_categorize_type(Oid typoid, bool is_jsonb,
}
}
+/*
+ * Check that a JSON type category is valid for an object key.
+ * This must also be checked when a NULL value causes the pair to be omitted.
+ * The category must have been obtained from json_categorize_type().
+ */
+void
+json_validate_key_category(JsonTypeCategory tcategory)
+{
+ if (tcategory == JSONTYPE_ARRAY ||
+ tcategory == JSONTYPE_COMPOSITE ||
+ tcategory == JSONTYPE_JSON ||
+ tcategory == JSONTYPE_JSONB ||
+ tcategory == JSONTYPE_CAST)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("key value must be scalar, not array, composite, or json")));
+}
+
/*
* Check whether a type conversion to JSON or JSONB involves any mutable
* functions. This recurses into container types (arrays, composites,
diff --git a/src/include/utils/jsonfuncs.h b/src/include/utils/jsonfuncs.h
index 27713be..1beecd9 100644
--- a/src/include/utils/jsonfuncs.h
+++ b/src/include/utils/jsonfuncs.h
@@ -83,6 +83,7 @@ typedef enum
extern void json_categorize_type(Oid typoid, bool is_jsonb,
JsonTypeCategory *tcategory, Oid *outfuncoid);
+extern void json_validate_key_category(JsonTypeCategory tcategory);
extern void json_check_mutability(Oid typoid, bool is_jsonb,
bool *has_mutable);
extern Datum datum_to_json(Datum val, JsonTypeCategory tcategory,
diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out
index 30f9e10..4e495c2 100644
--- a/src/test/regress/expected/sqljson.out
+++ b/src/test/regress/expected/sqljson.out
@@ -539,6 +539,31 @@ SELECT JSON_OBJECT('a': '1', 'b': NULL, 'c': 2 ABSENT ON NULL);
{"a" : "1", "c" : 2}
(1 row)
+-- Keys must be valid even when ABSENT ON NULL omits their values.
+SELECT JSON_OBJECT((NULL::text) VALUE NULL::text ABSENT ON NULL RETURNING json);
+ERROR: null value not allowed for object key
+SELECT JSON_OBJECT((NULL::text) VALUE NULL::text ABSENT ON NULL WITH UNIQUE KEYS RETURNING json);
+ERROR: null value not allowed for object key
+SELECT JSON_OBJECT((ARRAY[1,2]) VALUE NULL::text ABSENT ON NULL RETURNING json);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECT((ROW(1,2)) VALUE NULL::text ABSENT ON NULL RETURNING json);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECT((json '1') VALUE NULL::text ABSENT ON NULL RETURNING json);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECT((jsonb '1') VALUE NULL::text ABSENT ON NULL RETURNING json);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECT((NULL::text) VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+ERROR: argument 1: key must not be null
+SELECT JSON_OBJECT((NULL::text) VALUE NULL::text ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb);
+ERROR: argument 1: key must not be null
+SELECT JSON_OBJECT((ARRAY[1,2]) VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECT((ROW(1,2)) VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECT((json '1') VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECT((jsonb '1') VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+ERROR: key value must be scalar, not array, composite, or json
SELECT JSON_OBJECT(1: 1, '2': NULL, '3': 1, repeat('x', 1000): 1, 2: repeat('a', 100) WITH UNIQUE);
ERROR: duplicate JSON object key value: "2"
SELECT JSON_OBJECT(1: 1, '1': NULL WITH UNIQUE);
@@ -607,6 +632,14 @@ $$ LANGUAGE sql IMMUTABLE;
CREATE CAST (mood AS json) WITH FUNCTION mood_to_json(mood) AS IMPLICIT;
SELECT JSON_OBJECT('happy'::mood: '123'::jsonb);
ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECT('happy'::mood: NULL ABSENT ON NULL RETURNING json);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECT('happy'::mood: NULL ABSENT ON NULL RETURNING jsonb);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT json_object_agg_strict('happy'::mood, NULL);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT jsonb_object_agg_strict('happy'::mood, NULL);
+ERROR: key value must be scalar, not array, composite, or json
DROP CAST (mood AS json);
DROP FUNCTION mood_to_json;
DROP TYPE mood;
@@ -963,6 +996,27 @@ SELECT JSON_OBJECTAGG(NULL: 1);
ERROR: null value not allowed for object key
SELECT JSON_OBJECTAGG(NULL: 1 RETURNING jsonb);
ERROR: field name must not be null
+-- Aggregate keys must also be valid when NULL values are omitted.
+SELECT json_object_agg_strict(ARRAY[1,2], NULL);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT json_object_agg_strict(ROW(1,2), NULL);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT json_object_agg_strict(json '1', NULL);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT json_object_agg_strict(jsonb '1', NULL);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECTAGG((jsonb '1'): NULL ABSENT ON NULL RETURNING json);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT jsonb_object_agg_strict(ARRAY[1,2], NULL);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT jsonb_object_agg_strict(ROW(1,2), NULL);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT jsonb_object_agg_strict(json '1', NULL);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT jsonb_object_agg_strict(jsonb '1', NULL);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECTAGG((jsonb '1'): NULL ABSENT ON NULL RETURNING jsonb);
+ERROR: key value must be scalar, not array, composite, or json
SELECT
JSON_OBJECTAGG(i: i),
-- JSON_OBJECTAGG(i VALUE i),
diff --git a/src/test/regress/sql/sqljson.sql b/src/test/regress/sql/sqljson.sql
index 9fa203c..b1b5205 100644
--- a/src/test/regress/sql/sqljson.sql
+++ b/src/test/regress/sql/sqljson.sql
@@ -138,6 +138,20 @@ SELECT JSON_OBJECT('a': '1', 'b': NULL, 'c': 2);
SELECT JSON_OBJECT('a': '1', 'b': NULL, 'c': 2 NULL ON NULL);
SELECT JSON_OBJECT('a': '1', 'b': NULL, 'c': 2 ABSENT ON NULL);
+-- Keys must be valid even when ABSENT ON NULL omits their values.
+SELECT JSON_OBJECT((NULL::text) VALUE NULL::text ABSENT ON NULL RETURNING json);
+SELECT JSON_OBJECT((NULL::text) VALUE NULL::text ABSENT ON NULL WITH UNIQUE KEYS RETURNING json);
+SELECT JSON_OBJECT((ARRAY[1,2]) VALUE NULL::text ABSENT ON NULL RETURNING json);
+SELECT JSON_OBJECT((ROW(1,2)) VALUE NULL::text ABSENT ON NULL RETURNING json);
+SELECT JSON_OBJECT((json '1') VALUE NULL::text ABSENT ON NULL RETURNING json);
+SELECT JSON_OBJECT((jsonb '1') VALUE NULL::text ABSENT ON NULL RETURNING json);
+SELECT JSON_OBJECT((NULL::text) VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+SELECT JSON_OBJECT((NULL::text) VALUE NULL::text ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb);
+SELECT JSON_OBJECT((ARRAY[1,2]) VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+SELECT JSON_OBJECT((ROW(1,2)) VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+SELECT JSON_OBJECT((json '1') VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+SELECT JSON_OBJECT((jsonb '1') VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+
SELECT JSON_OBJECT(1: 1, '2': NULL, '3': 1, repeat('x', 1000): 1, 2: repeat('a', 100) WITH UNIQUE);
SELECT JSON_OBJECT(1: 1, '1': NULL WITH UNIQUE);
@@ -171,6 +185,10 @@ CREATE FUNCTION mood_to_json(mood) RETURNS json AS $$
$$ LANGUAGE sql IMMUTABLE;
CREATE CAST (mood AS json) WITH FUNCTION mood_to_json(mood) AS IMPLICIT;
SELECT JSON_OBJECT('happy'::mood: '123'::jsonb);
+SELECT JSON_OBJECT('happy'::mood: NULL ABSENT ON NULL RETURNING json);
+SELECT JSON_OBJECT('happy'::mood: NULL ABSENT ON NULL RETURNING jsonb);
+SELECT json_object_agg_strict('happy'::mood, NULL);
+SELECT jsonb_object_agg_strict('happy'::mood, NULL);
DROP CAST (mood AS json);
DROP FUNCTION mood_to_json;
DROP TYPE mood;
@@ -293,6 +311,18 @@ SELECT JSON_OBJECTAGG(NULL: 1);
SELECT JSON_OBJECTAGG(NULL: 1 RETURNING jsonb);
+-- Aggregate keys must also be valid when NULL values are omitted.
+SELECT json_object_agg_strict(ARRAY[1,2], NULL);
+SELECT json_object_agg_strict(ROW(1,2), NULL);
+SELECT json_object_agg_strict(json '1', NULL);
+SELECT json_object_agg_strict(jsonb '1', NULL);
+SELECT JSON_OBJECTAGG((jsonb '1'): NULL ABSENT ON NULL RETURNING json);
+SELECT jsonb_object_agg_strict(ARRAY[1,2], NULL);
+SELECT jsonb_object_agg_strict(ROW(1,2), NULL);
+SELECT jsonb_object_agg_strict(json '1', NULL);
+SELECT jsonb_object_agg_strict(jsonb '1', NULL);
+SELECT JSON_OBJECTAGG((jsonb '1'): NULL ABSENT ON NULL RETURNING jsonb);
+
SELECT
JSON_OBJECTAGG(i: i),
-- JSON_OBJECTAGG(i VALUE i),
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL
2026-10-02 22:35 BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL PG Bug reporting form <noreply@postgresql.org>
2026-10-11 10:26 ` Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL 민영재 <dudwo990413@gmail.com>
2026-10-16 20:41 ` Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL Dmitry Dolgov <9erthalion6@gmail.com>
2026-10-17 14:53 ` Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL 민영재 <dudwo990413@gmail.com>
@ 2026-11-09 07:45 ` 민영재 <dudwo990413@gmail.com>
2026-11-15 08:46 ` Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL Joao Rito <joaovictor.rito@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: 민영재 @ 2026-11-09 07:45 UTC (permalink / raw)
To: Dmitry Dolgov <9erthalion6@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org; theshallow27@gmail.com
Hi,
CFbot reported that v2 needs a rebase after b58a9a97fe reverted the
json_check_mutability() changes. V3 is rebased onto master at 553262aff40d.
It updates the insertion context for the shared key validation helper
without restoring the reverted function; there are no behavior changes from
v2.
I also verified that the attached patch applies with git am to a clean
checkout of the stated master commit.
Thanks,
Yeongjae Min
On Sat, 17 Oct 2026 23:53:49 +0900, 민영재 <dudwo990413@gmail.com> wrote:
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 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?
Attachments:
v3-0001-Validate-JSON-object-keys-before-omitting-null-values.patch (text/x-patch, 17.4K) download
From cf269d1a02ac35d7dc674c2ef0a7271b4f10dd74 Mon Sep 17 00:00:00 2001
From: Yeongjae Min <dudwo990413@gmail.com>
Date: Mon, 9 Nov 2026 16:05:09 +0900
Subject: [PATCH v3] Validate JSON object keys before omitting null values
JSON_OBJECT with ABSENT ON NULL can skip key validation when it omits
an entry with a null value. The json path accepts null keys, and both
json and jsonb paths can accept non-scalar keys. The strict object
aggregate workers have the same non-scalar-key validation gap.
Move the json constructor's null-key check before omission, and validate
key categories in the skipped-entry paths of constructors and aggregates.
Factor the existing category checks into json_validate_key_category(),
shared with datum_to_json_internal() and datum_to_jsonb_internal(). This
preserves the existing error messages and SQLSTATEs and avoids invoking
output or cast functions for omitted entries without uniqueness checks.
Clarify that key restrictions apply even when ABSENT ON NULL omits the
pair, and document the restrictions for strict object aggregates.
Add regression coverage for constructors and aggregates, including
array, composite, json/jsonb and user-defined JSON-cast keys.
Reported in BUG #19736:
https://www.postgresql.org/message-id/19736-3a44617ea5534c77@postgresql.org
---
v3: Rebase onto master at 553262aff40d after b58a9a97fe removed
json_check_mutability(). Update the shared-helper insertion context
without restoring the reverted function. No behavior changes from v2.
Validation: 239 core regression tests and 660 supplemental behavior
assertions pass on macOS/arm64 with assertions enabled.
doc/src/sgml/func/func-aggregate.sgml | 12 ++++--
doc/src/sgml/func/func-json.sgml | 2 +
src/backend/utils/adt/json.c | 33 +++++++++-------
src/backend/utils/adt/jsonb.c | 25 +++++++------
src/backend/utils/adt/jsonfuncs.c | 18 +++++++++
src/include/utils/jsonfuncs.h | 1 +
src/test/regress/expected/sqljson.out | 54 +++++++++++++++++++++++++++
src/test/regress/sql/sqljson.sql | 30 +++++++++++++++
8 files changed, 146 insertions(+), 29 deletions(-)
diff --git a/doc/src/sgml/func/func-aggregate.sgml b/doc/src/sgml/func/func-aggregate.sgml
index c3669898f0..462f720e2c 100644
--- a/doc/src/sgml/func/func-aggregate.sgml
+++ b/doc/src/sgml/func/func-aggregate.sgml
@@ -434,8 +434,10 @@
Collects all the key/value pairs into a JSON object. Key arguments
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 pair is skipped.
+ The <parameter>key</parameter> must be a non-null scalar and cannot
+ belong to a type that has a cast to <type>json</type>, even if the
+ pair is skipped. If the <parameter>value</parameter> is null then
+ the pair is skipped.
Duplicate keys are handled as for <function>json_object_agg</function>
and <function>jsonb_object_agg</function>, after removing any pairs
with null values.
@@ -495,8 +497,10 @@
Collects all the key/value pairs into a JSON object. Key arguments
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 pair is skipped.
+ The <parameter>key</parameter> must be a non-null scalar and cannot
+ belong to a type that has a cast to <type>json</type>, even if the
+ pair is skipped. If the <parameter>value</parameter> is null then
+ the pair is skipped.
If there is a duplicate key (including pairs with null values)
an error is thrown.
</para></entry>
diff --git a/doc/src/sgml/func/func-json.sgml b/doc/src/sgml/func/func-json.sgml
index ede39e7d02..28623adfad 100644
--- a/doc/src/sgml/func/func-json.sgml
+++ b/doc/src/sgml/func/func-json.sgml
@@ -724,6 +724,8 @@
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.
+ These restrictions apply even if the pair is omitted from the output
+ due to <literal>ABSENT ON NULL</literal>.
If <literal>WITH UNIQUE KEYS</literal> is specified, there must not
be any duplicate <replaceable>key_expression</replaceable>.
Any pair for which the <replaceable>value_expression</replaceable>
diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c
index 125fb0c120..3de7c5da2a 100644
--- a/src/backend/utils/adt/json.c
+++ b/src/backend/utils/adt/json.c
@@ -194,14 +194,8 @@ datum_to_json_internal(Datum val, bool is_null, StringInfo result,
return;
}
- if (key_scalar &&
- (tcategory == JSONTYPE_ARRAY ||
- tcategory == JSONTYPE_COMPOSITE ||
- tcategory == JSONTYPE_JSON ||
- tcategory == JSONTYPE_CAST))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("key value must be scalar, not array, composite, or json")));
+ if (key_scalar)
+ json_validate_key_category(tcategory);
switch (tcategory)
{
@@ -1088,7 +1082,10 @@ json_object_agg_transfn_worker(FunctionCallInfo fcinfo,
* buffer to store the key name so that we can check it.
*/
if (!unique_keys)
+ {
+ json_validate_key_category(state->key_category);
PG_RETURN_POINTER(state);
+ }
out = json_unique_builder_get_throwawaybuf(&state->unique_check);
}
@@ -1252,6 +1249,12 @@ json_build_object_worker(int nargs, const Datum *args, const bool *nulls, const
bool skip;
int key_offset;
+ /* Keys must not be null, even if their values will be omitted. */
+ if (nulls[i])
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("null value not allowed for object key")));
+
/* Skip null values if absent_on_null */
skip = absent_on_null && nulls[i + 1];
@@ -1259,7 +1262,15 @@ json_build_object_worker(int nargs, const Datum *args, const bool *nulls, const
{
/* If key uniqueness check is needed we must save skipped keys */
if (!unique_keys)
+ {
+ JsonTypeCategory tcategory;
+ Oid outfuncoid;
+
+ /* Validate the key type without converting the omitted key. */
+ json_categorize_type(types[i], false, &tcategory, &outfuncoid);
+ json_validate_key_category(tcategory);
continue;
+ }
out = json_unique_builder_get_throwawaybuf(&unique_check);
}
@@ -1270,12 +1281,6 @@ json_build_object_worker(int nargs, const Datum *args, const bool *nulls, const
out = result;
}
- /* process key */
- if (nulls[i])
- ereport(ERROR,
- (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
- errmsg("null value not allowed for object key")));
-
/* save key offset before appending it */
key_offset = out->len;
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index c3cbbe2791..29550f38e8 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -646,19 +646,11 @@ datum_to_jsonb_internal(Datum val, bool is_null, JsonbInState *result,
Assert(!key_scalar);
jb.type = jbvNull;
}
- else if (key_scalar &&
- (tcategory == JSONTYPE_ARRAY ||
- tcategory == JSONTYPE_COMPOSITE ||
- tcategory == JSONTYPE_JSON ||
- tcategory == JSONTYPE_JSONB ||
- tcategory == JSONTYPE_CAST))
- {
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("key value must be scalar, not array, composite, or json")));
- }
else
{
+ if (key_scalar)
+ json_validate_key_category(tcategory);
+
switch (tcategory)
{
case JSONTYPE_ARRAY:
@@ -1194,7 +1186,15 @@ jsonb_build_object_worker(int nargs, const Datum *args, const bool *nulls, const
/* we need to save skipped keys for the key uniqueness check */
if (skip && !unique_keys)
+ {
+ JsonTypeCategory tcategory;
+ Oid outfuncoid;
+
+ /* Validate the key type without converting the omitted key. */
+ json_categorize_type(types[i], true, &tcategory, &outfuncoid);
+ json_validate_key_category(tcategory);
continue;
+ }
add_jsonb(args[i], false, &result, types[i], true);
@@ -1677,7 +1677,10 @@ jsonb_object_agg_transfn_worker(FunctionCallInfo fcinfo,
skip = absent_on_null && PG_ARGISNULL(2);
if (skip && !unique_keys)
+ {
+ json_validate_key_category(state->key_category);
PG_RETURN_POINTER(state);
+ }
/*
* We run this code in the normal function context, so that we don't leak
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index 4e149d21d4..d8a1d34df0 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -6083,3 +6083,21 @@ json_categorize_type(Oid typoid, bool is_jsonb,
break;
}
}
+
+/*
+ * Check that a JSON type category is valid for an object key.
+ * This must also be checked when a NULL value causes the pair to be omitted.
+ * The category must have been obtained from json_categorize_type().
+ */
+void
+json_validate_key_category(JsonTypeCategory tcategory)
+{
+ if (tcategory == JSONTYPE_ARRAY ||
+ tcategory == JSONTYPE_COMPOSITE ||
+ tcategory == JSONTYPE_JSON ||
+ tcategory == JSONTYPE_JSONB ||
+ tcategory == JSONTYPE_CAST)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("key value must be scalar, not array, composite, or json")));
+}
diff --git a/src/include/utils/jsonfuncs.h b/src/include/utils/jsonfuncs.h
index 636f0f5584..6f502e9df6 100644
--- a/src/include/utils/jsonfuncs.h
+++ b/src/include/utils/jsonfuncs.h
@@ -83,6 +83,7 @@ typedef enum
extern void json_categorize_type(Oid typoid, bool is_jsonb,
JsonTypeCategory *tcategory, Oid *outfuncoid);
+extern void json_validate_key_category(JsonTypeCategory tcategory);
extern Datum datum_to_json(Datum val, JsonTypeCategory tcategory,
Oid outfuncoid);
extern Datum datum_to_jsonb(Datum val, JsonTypeCategory tcategory,
diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out
index 1085475541..90aed84a15 100644
--- a/src/test/regress/expected/sqljson.out
+++ b/src/test/regress/expected/sqljson.out
@@ -539,6 +539,31 @@ SELECT JSON_OBJECT('a': '1', 'b': NULL, 'c': 2 ABSENT ON NULL);
{"a" : "1", "c" : 2}
(1 row)
+-- Keys must be valid even when ABSENT ON NULL omits their values.
+SELECT JSON_OBJECT((NULL::text) VALUE NULL::text ABSENT ON NULL RETURNING json);
+ERROR: null value not allowed for object key
+SELECT JSON_OBJECT((NULL::text) VALUE NULL::text ABSENT ON NULL WITH UNIQUE KEYS RETURNING json);
+ERROR: null value not allowed for object key
+SELECT JSON_OBJECT((ARRAY[1,2]) VALUE NULL::text ABSENT ON NULL RETURNING json);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECT((ROW(1,2)) VALUE NULL::text ABSENT ON NULL RETURNING json);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECT((json '1') VALUE NULL::text ABSENT ON NULL RETURNING json);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECT((jsonb '1') VALUE NULL::text ABSENT ON NULL RETURNING json);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECT((NULL::text) VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+ERROR: argument 1: key must not be null
+SELECT JSON_OBJECT((NULL::text) VALUE NULL::text ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb);
+ERROR: argument 1: key must not be null
+SELECT JSON_OBJECT((ARRAY[1,2]) VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECT((ROW(1,2)) VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECT((json '1') VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECT((jsonb '1') VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+ERROR: key value must be scalar, not array, composite, or json
SELECT JSON_OBJECT(1: 1, '2': NULL, '3': 1, repeat('x', 1000): 1, 2: repeat('a', 100) WITH UNIQUE);
ERROR: duplicate JSON object key value: "2"
SELECT JSON_OBJECT(1: 1, '1': NULL WITH UNIQUE);
@@ -607,6 +632,14 @@ $$ LANGUAGE sql IMMUTABLE;
CREATE CAST (mood AS json) WITH FUNCTION mood_to_json(mood) AS IMPLICIT;
SELECT JSON_OBJECT('happy'::mood: '123'::jsonb);
ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECT('happy'::mood: NULL ABSENT ON NULL RETURNING json);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECT('happy'::mood: NULL ABSENT ON NULL RETURNING jsonb);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT json_object_agg_strict('happy'::mood, NULL);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT jsonb_object_agg_strict('happy'::mood, NULL);
+ERROR: key value must be scalar, not array, composite, or json
DROP CAST (mood AS json);
DROP FUNCTION mood_to_json;
DROP TYPE mood;
@@ -963,6 +996,27 @@ SELECT JSON_OBJECTAGG(NULL: 1);
ERROR: null value not allowed for object key
SELECT JSON_OBJECTAGG(NULL: 1 RETURNING jsonb);
ERROR: field name must not be null
+-- Aggregate keys must also be valid when NULL values are omitted.
+SELECT json_object_agg_strict(ARRAY[1,2], NULL);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT json_object_agg_strict(ROW(1,2), NULL);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT json_object_agg_strict(json '1', NULL);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT json_object_agg_strict(jsonb '1', NULL);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECTAGG((jsonb '1'): NULL ABSENT ON NULL RETURNING json);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT jsonb_object_agg_strict(ARRAY[1,2], NULL);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT jsonb_object_agg_strict(ROW(1,2), NULL);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT jsonb_object_agg_strict(json '1', NULL);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT jsonb_object_agg_strict(jsonb '1', NULL);
+ERROR: key value must be scalar, not array, composite, or json
+SELECT JSON_OBJECTAGG((jsonb '1'): NULL ABSENT ON NULL RETURNING jsonb);
+ERROR: key value must be scalar, not array, composite, or json
SELECT
JSON_OBJECTAGG(i: i),
-- JSON_OBJECTAGG(i VALUE i),
diff --git a/src/test/regress/sql/sqljson.sql b/src/test/regress/sql/sqljson.sql
index 43106fdda9..f329691b7f 100644
--- a/src/test/regress/sql/sqljson.sql
+++ b/src/test/regress/sql/sqljson.sql
@@ -138,6 +138,20 @@ SELECT JSON_OBJECT('a': '1', 'b': NULL, 'c': 2);
SELECT JSON_OBJECT('a': '1', 'b': NULL, 'c': 2 NULL ON NULL);
SELECT JSON_OBJECT('a': '1', 'b': NULL, 'c': 2 ABSENT ON NULL);
+-- Keys must be valid even when ABSENT ON NULL omits their values.
+SELECT JSON_OBJECT((NULL::text) VALUE NULL::text ABSENT ON NULL RETURNING json);
+SELECT JSON_OBJECT((NULL::text) VALUE NULL::text ABSENT ON NULL WITH UNIQUE KEYS RETURNING json);
+SELECT JSON_OBJECT((ARRAY[1,2]) VALUE NULL::text ABSENT ON NULL RETURNING json);
+SELECT JSON_OBJECT((ROW(1,2)) VALUE NULL::text ABSENT ON NULL RETURNING json);
+SELECT JSON_OBJECT((json '1') VALUE NULL::text ABSENT ON NULL RETURNING json);
+SELECT JSON_OBJECT((jsonb '1') VALUE NULL::text ABSENT ON NULL RETURNING json);
+SELECT JSON_OBJECT((NULL::text) VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+SELECT JSON_OBJECT((NULL::text) VALUE NULL::text ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb);
+SELECT JSON_OBJECT((ARRAY[1,2]) VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+SELECT JSON_OBJECT((ROW(1,2)) VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+SELECT JSON_OBJECT((json '1') VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+SELECT JSON_OBJECT((jsonb '1') VALUE NULL::text ABSENT ON NULL RETURNING jsonb);
+
SELECT JSON_OBJECT(1: 1, '2': NULL, '3': 1, repeat('x', 1000): 1, 2: repeat('a', 100) WITH UNIQUE);
SELECT JSON_OBJECT(1: 1, '1': NULL WITH UNIQUE);
@@ -171,6 +185,10 @@ CREATE FUNCTION mood_to_json(mood) RETURNS json AS $$
$$ LANGUAGE sql IMMUTABLE;
CREATE CAST (mood AS json) WITH FUNCTION mood_to_json(mood) AS IMPLICIT;
SELECT JSON_OBJECT('happy'::mood: '123'::jsonb);
+SELECT JSON_OBJECT('happy'::mood: NULL ABSENT ON NULL RETURNING json);
+SELECT JSON_OBJECT('happy'::mood: NULL ABSENT ON NULL RETURNING jsonb);
+SELECT json_object_agg_strict('happy'::mood, NULL);
+SELECT jsonb_object_agg_strict('happy'::mood, NULL);
DROP CAST (mood AS json);
DROP FUNCTION mood_to_json;
DROP TYPE mood;
@@ -293,6 +311,18 @@ SELECT JSON_OBJECTAGG(NULL: 1);
SELECT JSON_OBJECTAGG(NULL: 1 RETURNING jsonb);
+-- Aggregate keys must also be valid when NULL values are omitted.
+SELECT json_object_agg_strict(ARRAY[1,2], NULL);
+SELECT json_object_agg_strict(ROW(1,2), NULL);
+SELECT json_object_agg_strict(json '1', NULL);
+SELECT json_object_agg_strict(jsonb '1', NULL);
+SELECT JSON_OBJECTAGG((jsonb '1'): NULL ABSENT ON NULL RETURNING json);
+SELECT jsonb_object_agg_strict(ARRAY[1,2], NULL);
+SELECT jsonb_object_agg_strict(ROW(1,2), NULL);
+SELECT jsonb_object_agg_strict(json '1', NULL);
+SELECT jsonb_object_agg_strict(jsonb '1', NULL);
+SELECT JSON_OBJECTAGG((jsonb '1'): NULL ABSENT ON NULL RETURNING jsonb);
+
SELECT
JSON_OBJECTAGG(i: i),
-- JSON_OBJECTAGG(i VALUE i),
base-commit: 553262aff40d34711e04e989e91d1da430589822
--
2.54.0 (Apple Git-157)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL
2026-10-02 22:35 BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL PG Bug reporting form <noreply@postgresql.org>
2026-10-11 10:26 ` Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL 민영재 <dudwo990413@gmail.com>
2026-10-16 20:41 ` Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL Dmitry Dolgov <9erthalion6@gmail.com>
2026-10-17 14:53 ` Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL 민영재 <dudwo990413@gmail.com>
2026-11-09 07:45 ` Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL 민영재 <dudwo990413@gmail.com>
@ 2026-11-15 08:46 ` Joao Rito <joaovictor.rito@gmail.com>
2026-11-15 16:22 ` Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL 민영재 <dudwo990413@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Joao Rito @ 2026-11-15 08:46 UTC (permalink / raw)
To: Yeongjae Min <dudwo990413@gmail.com>; +Cc: Dmitry Dolgov <9erthalion6@gmail.com>; pgsql-hackers@lists.postgresql.org; pgsql-bugs@lists.postgresql.org; theshallow27@gmail.com
Hi Yeongjae,
Thanks for the patch. I tested v3 on current master (81b2179b492, built
with cassert and tap tests) and it applies cleanly. On master, with a
NULL key and a NULL value, JSON_OBJECT ... ABSENT ON NULL RETURNING json
returns {} instead of an error. The same happens for an array key, a ROW
key, and json and jsonb keys, with RETURNING json and jsonb, and for a
domain over int[] with RETURNING json. A jsonb NULL key with a NULL
value already fails on master ("argument 1: key must not be null"), so
the NULL key problem is only in the json path. Two aggregate cases,
json_object_agg_strict(ARRAY[1], NULL::text) and
jsonb_object_agg_strict('{"a":1}'::jsonb, NULL::text), also returned an
empty object on master. Dmitry already raised the aggregate case. I only
confirm it on current master.
With the patch all of these raise an error: "null value not allowed for
object key" for the json path and "key value must be scalar, not array,
composite, or json" for non-scalar keys. A composite key with a cast to
json and a NULL value is now rejected, as it already was with a non-NULL
value. A valid key with a NULL value is still omitted (tested with text,
int, numeric, bool, timestamptz, name, bytea, int4range and enum keys).
A NULL key with a non-NULL value still fails as on master. With WITH
UNIQUE KEYS, duplicate keys with omitted values and a NULL key with a
NULL value (json) give the same result as master. json_build_object,
jsonb_build_object and json_object(text[]) also give the same output as
master. The regress suite passes (243 subtests) and the server log has
no TRAP or assertion lines.
What I did not test: check-world, the isolation and tap suites,
pgindent, the docs change and its build, the back branches, other
platforms (I ran one Linux build), planning or performance effects, and
a detailed read of the C code.
I found no problem with the patch from what I could test.
Regards,
Joao Rito
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL
2026-10-02 22:35 BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL PG Bug reporting form <noreply@postgresql.org>
2026-10-11 10:26 ` Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL 민영재 <dudwo990413@gmail.com>
2026-10-16 20:41 ` Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL Dmitry Dolgov <9erthalion6@gmail.com>
2026-10-17 14:53 ` Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL 민영재 <dudwo990413@gmail.com>
2026-11-09 07:45 ` Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL 민영재 <dudwo990413@gmail.com>
2026-11-15 08:46 ` Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL Joao Rito <joaovictor.rito@gmail.com>
@ 2026-11-15 16:22 ` 민영재 <dudwo990413@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: 민영재 @ 2026-11-15 16:22 UTC (permalink / raw)
To: Joao Rito <joaovictor.rito@gmail.com>; +Cc: Dmitry Dolgov <9erthalion6@gmail.com>; pgsql-hackers@lists.postgresql.org; pgsql-bugs@lists.postgresql.org; theshallow27@gmail.com
Hi Joao,
Thank you for testing the v3 patch and for the detailed feedback. I
appreciate you checking both the invalid-key cases and the existing
behavior, as well as running the regression tests on Linux.
Regards,
Yeongjae Min
On Sun, 15 Nov 2026 05:46:50 -0300, Joao Rito <joaovictor.rito@gmail.com>
wrote:
Hi Yeongjae, Thanks for the patch. I tested v3 on current master
(81b2179b492, built with cassert and tap tests) and it applies cleanly. On
master, with a NULL key and a NULL value, JSON_OBJECT ... ABSENT ON NULL
RETURNING json returns {} instead of an error. The same happens for an
array key, a ROW key, and json and jsonb keys, with RETURNING json and
jsonb, and for a domain over int[] with RETURNING json. A jsonb NULL key
with a NULL value already fails on master ("argument 1: key must not be
null"), so the NULL key problem is only in the json path. Two aggregate
cases, json_object_agg_strict(ARRAY[1], NULL::text) and
jsonb_object_agg_strict('{"a":1}'::jsonb, NULL::text), also returned an
empty object on master. Dmitry already raised the aggregate case. I only
confirm it on current master. With the patch all of these raise an error:
"null value not allowed for object key" for the json path and "key value
must be scalar, not array, composite, or json" for non-scalar keys. A
composite key with a cast to json and a NULL value is now rejected, as it
already was with a non-NULL value. A valid key with a NULL value is still
omitted (tested with text, int, numeric, bool, timestamptz, name, bytea,
int4range and enum keys). A NULL key with a non-NULL value still fails as
on master. With WITH UNIQUE KEYS, duplicate keys with omitted values and a
NULL key with a NULL value (json) give the same result as master.
json_build_object, jsonb_build_object and json_object(text[]) also give the
same output as master. The regress suite passes (243 subtests) and the
server log has no TRAP or assertion lines. What I did not test:
check-world, the isolation and tap suites, pgindent, the docs change and
its build, the back branches, other platforms (I ran one Linux build),
planning or performance effects, and a detailed read of the C code. I found
no problem with the patch from what I could test. Regards, Joao Rito
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL
2026-10-02 22:35 BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL PG Bug reporting form <noreply@postgresql.org>
@ 2026-10-12 03:42 ` QQ Mail <781408517@qq.com>
1 sibling, 0 replies; 10+ messages in thread
From: QQ Mail @ 2026-10-12 03:42 UTC (permalink / raw)
To: pgsql-hackers@lists.postgresql.org; +Cc: pgsql-bugs@lists.postgresql.org; theshallow27@gmail.com
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.
Attachments:
19736-null-key-validation.patch (text/x-diff, 2.7K) download
diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c
index 28e5f3c..b58b7ce 100644
--- a/src/backend/utils/adt/json.c
+++ b/src/backend/utils/adt/json.c
@@ -1220,6 +1220,12 @@ json_build_object_worker(int nargs, const Datum *args, const bool *nulls, const
bool skip;
int key_offset;
+ /* Keys must not be null, even if their values are skipped. */
+ if (nulls[i])
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("null value not allowed for object key")));
+
/* Skip null values if absent_on_null */
skip = absent_on_null && nulls[i + 1];
@@ -1239,11 +1245,6 @@ json_build_object_worker(int nargs, const Datum *args, const bool *nulls, const
}
/* process key */
- if (nulls[i])
- ereport(ERROR,
- (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
- errmsg("null value not allowed for object key")));
-
/* save key offset before appending it */
key_offset = out->len;
diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out
index 30f9e10..0881e18 100644
--- a/src/test/regress/expected/sqljson.out
+++ b/src/test/regress/expected/sqljson.out
@@ -397,6 +397,15 @@ LINE 1: SELECT JSON_OBJECT('foo': NULL::jsonb FORMAT JSON ENCODING U...
^
SELECT JSON_OBJECT(NULL: 1);
ERROR: null value not allowed for object key
+-- NULL keys are invalid even if their values would be omitted.
+SELECT JSON_OBJECT(NULL: NULL ABSENT ON NULL);
+ERROR: null value not allowed for object key
+SELECT JSON_OBJECT(NULL: NULL ABSENT ON NULL RETURNING jsonb);
+ERROR: argument 1: key must not be null
+SELECT JSON_OBJECT(NULL: NULL ABSENT ON NULL WITH UNIQUE KEYS);
+ERROR: null value not allowed for object key
+SELECT JSON_OBJECT(NULL: NULL ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb);
+ERROR: argument 1: key must not be null
SELECT JSON_OBJECT('a': 2 + 3);
json_object
-------------
diff --git a/src/test/regress/sql/sqljson.sql b/src/test/regress/sql/sqljson.sql
index 9fa203c..6dc52ed 100644
--- a/src/test/regress/sql/sqljson.sql
+++ b/src/test/regress/sql/sqljson.sql
@@ -86,6 +86,11 @@ SELECT JSON_OBJECT('foo': NULL::jsonb FORMAT JSON);
SELECT JSON_OBJECT('foo': NULL::jsonb FORMAT JSON ENCODING UTF8);
SELECT JSON_OBJECT(NULL: 1);
+-- NULL keys are invalid even if their values would be omitted.
+SELECT JSON_OBJECT(NULL: NULL ABSENT ON NULL);
+SELECT JSON_OBJECT(NULL: NULL ABSENT ON NULL RETURNING jsonb);
+SELECT JSON_OBJECT(NULL: NULL ABSENT ON NULL WITH UNIQUE KEYS);
+SELECT JSON_OBJECT(NULL: NULL ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb);
SELECT JSON_OBJECT('a': 2 + 3);
SELECT JSON_OBJECT('a' VALUE 2 + 3);
--SELECT JSON_OBJECT(KEY 'a' VALUE 2 + 3);
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2026-11-15 16:22 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-02 22:35 BUG #19736: `JSON_OBJECT ... ABSENT ON NULL` accepts invalid keys when the value is NULL PG Bug reporting form <noreply@postgresql.org>
2026-10-11 10:26 ` 민영재 <dudwo990413@gmail.com>
2026-10-12 06:35 ` QQ Mail <781408517@qq.com>
2026-10-12 07:15 ` 민영재 <dudwo990413@gmail.com>
2026-10-16 20:41 ` Dmitry Dolgov <9erthalion6@gmail.com>
2026-10-17 14:53 ` 민영재 <dudwo990413@gmail.com>
2026-11-09 07:45 ` 민영재 <dudwo990413@gmail.com>
2026-11-15 08:46 ` Joao Rito <joaovictor.rito@gmail.com>
2026-11-15 16:22 ` 민영재 <dudwo990413@gmail.com>
2026-10-12 03:42 ` QQ Mail <781408517@qq.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