BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses

On Sun, Oct 4, 2026 at 5:16 AM PG Bug reporting form <noreply@postgresql.org>
wrote:

The documented syntax makes the key/value pair list optional and says that
an
empty pair list constructs an empty object. However, adding any of the
optional
clauses without a pair causes a syntax error.

**Reproduction:**

```sql
SELECT JSON_OBJECT(); -- returns {}
SELECT JSON_OBJECT(NULL ON NULL); -- syntax error, SQLSTATE 42601

JSON_ARRAY seems have the same situation.
The attached doc patch can this JSON_OBJECT doc issue.
the idea is to make json_object have 2 entries:

json_object ( { key_expression { VALUE | ':' } value_expression [ FORMAT
JSON [ ENCODING UTF8 ] ] }[, ...] [ { NULL | ABSENT } ON NULL ] [ { WITH |
WITHOUT } UNIQUE [ KEYS ] ] [ RETURNING data_type [ FORMAT JSON [ ENCODING
UTF8 ] ] ])
json_object ( [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ])

It feels ugly with too much text crammed together; perhaps we need another
way to solve this problem.

--
jian
https://www.enterprisedb.com/

Hi,

On Sun, Oct 4, 2026 at 2:46 AM PG Bug reporting form
<noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference: 19737
Logged by: Shallow
Email address: theshallow27@gmail.com
PostgreSQL version: 18.6
Operating system: Linux
Description:

The documented syntax makes the key/value pair list optional and says that
an
empty pair list constructs an empty object. However, adding any of the
optional
clauses without a pair causes a syntax error.

**Reproduction:**

```sql
SELECT JSON_OBJECT(); -- returns {}
SELECT JSON_OBJECT(NULL ON NULL); -- syntax error, SQLSTATE 42601
SELECT JSON_OBJECT(ABSENT ON NULL); -- syntax error, SQLSTATE 42601
SELECT JSON_OBJECT(WITH UNIQUE KEYS); -- syntax error, SQLSTATE 42601
```

I am able to reproduce this issue on master

postgres=# SELECT JSON_OBJECT();
json_object
-------------
{}
(1 row)

postgres=# SELECT JSON_OBJECT(RETURNING jsonb);
json_object
-------------
{}
(1 row)

postgres=# SELECT JSON_OBJECT(NULL ON NULL);
ERROR: syntax error at or near "ON"
LINE 1: SELECT JSON_OBJECT(NULL ON NULL);
^
postgres=# SELECT JSON_OBJECT(ABSENT ON NULL);
ERROR: syntax error at or near "ON"
LINE 1: SELECT JSON_OBJECT(ABSENT ON NULL);
^
postgres=# SELECT JSON_OBJECT(WITH UNIQUE KEYS);
ERROR: syntax error at or near "WITH"
LINE 1: SELECT JSON_OBJECT(WITH UNIQUE KEYS);
^
postgres=# SELECT JSON_OBJECT(ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb);
ERROR: syntax error at or near "ON"
LINE 1: SELECT JSON_OBJECT(ABSENT ON NULL WITH UNIQUE KEYS RETURNING...

**Expected result:** Each form with an empty pair list should construct
`{}`;

From the Documentation
(https://www.postgresql.org/docs/current/functions-json.html) this is
the correct expected result

json_object ( [ { key_expression { VALUE | ':' } value_expression [
FORMAT JSON [ ENCODING UTF8 ] ] }[, ...] ] [ { NULL | ABSENT } ON NULL
] [ { WITH | WITHOUT } UNIQUE [ KEYS ] ] [ RETURNING data_type [
FORMAT JSON [ ENCODING UTF8 ] ] ])

Constructs a JSON object of all the key/value pairs given, or an empty
object if none are given.

the clauses do not change the result when there are no pairs.

NOTE:

The problems seems to occur only when there are no key value pairs,
the following works,

CASE 1: Standard with values

postgres=# SELECT JSON_OBJECT('id': 007, 'name': 'VN');
json_object
---------------------------
{"id" : 7, "name" : "VN"}
(1 row)

CASE 2: NULL ON NULL

postgres=# SELECT JSON_OBJECT('a': 1, 'b': NULL NULL ON NULL);
json_object
-----------------------
{"a" : 1, "b" : null}
(1 row)

CASE 3 : ABSENT ON NULL

postgres=# SELECT JSON_OBJECT('a': 1, 'b': NULL ABSENT ON NULL);
json_object
-------------
{"a" : 1}
(1 row)

postgres=# SELECT JSON_OBJECT('id': 10, 'email': NULL, 'active': true
ABSENT ON NULL);
json_object
------------------------------
{"id" : 10, "active" : true}
(1 row)

CASE 4 : WITH AND WITHOUT UNIQUE KEYS

postgres=# SELECT JSON_OBJECT('k': 1, 'k': 2 WITHOUT UNIQUE KEYS);
json_object
--------------------
{"k" : 1, "k" : 2}
(1 row)

postgres=# SELECT JSON_OBJECT('k': 1, 'k': 2 WITH UNIQUE KEYS);
ERROR: duplicate JSON object key value: "k"
postgres=# SELECT JSON_OBJECT('k1': 1, 'k2': 2 WITH UNIQUE KEYS);
json_object
----------------------
{"k1" : 1, "k2" : 2}
(1 row)

CASE 5 : Returning Objects

postgres=# SELECT pg_typeof(JSON_OBJECT('a': 1 RETURNING jsonb));
pg_typeof
-----------
jsonb
(1 row)

postgres=# SELECT pg_typeof(JSON_OBJECT('a': 1 RETURNING text));
pg_typeof
-----------
text
(1 row)

Thank you,
Narayanan

Hi,

On Tue, Oct 6, 2026 at 11:32 AM Narayanan Venkateswaran
<narayananvpostgres@gmail.com> wrote:

Hi,

On Sun, Oct 4, 2026 at 2:46 AM PG Bug reporting form
<noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference: 19737
Logged by: Shallow
Email address: theshallow27@gmail.com
PostgreSQL version: 18.6
Operating system: Linux
Description:

The documented syntax makes the key/value pair list optional and says that
an
empty pair list constructs an empty object. However, adding any of the
optional
clauses without a pair causes a syntax error.

**Reproduction:**

```sql
SELECT JSON_OBJECT(); -- returns {}
SELECT JSON_OBJECT(NULL ON NULL); -- syntax error, SQLSTATE 42601
SELECT JSON_OBJECT(ABSENT ON NULL); -- syntax error, SQLSTATE 42601
SELECT JSON_OBJECT(WITH UNIQUE KEYS); -- syntax error, SQLSTATE 42601
```

I am able to reproduce this issue on master

postgres=# SELECT JSON_OBJECT();
json_object
-------------
{}
(1 row)

postgres=# SELECT JSON_OBJECT(RETURNING jsonb);
json_object
-------------
{}
(1 row)

postgres=# SELECT JSON_OBJECT(NULL ON NULL);
ERROR: syntax error at or near "ON"
LINE 1: SELECT JSON_OBJECT(NULL ON NULL);
^
postgres=# SELECT JSON_OBJECT(ABSENT ON NULL);
ERROR: syntax error at or near "ON"
LINE 1: SELECT JSON_OBJECT(ABSENT ON NULL);
^
postgres=# SELECT JSON_OBJECT(WITH UNIQUE KEYS);
ERROR: syntax error at or near "WITH"
LINE 1: SELECT JSON_OBJECT(WITH UNIQUE KEYS);
^
postgres=# SELECT JSON_OBJECT(ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb);
ERROR: syntax error at or near "ON"
LINE 1: SELECT JSON_OBJECT(ABSENT ON NULL WITH UNIQUE KEYS RETURNING...

**Expected result:** Each form with an empty pair list should construct
`{}`;

From the Documentation
(https://www.postgresql.org/docs/current/functions-json.html) this is
the correct expected result

json_object ( [ { key_expression { VALUE | ':' } value_expression [
FORMAT JSON [ ENCODING UTF8 ] ] }[, ...] ] [ { NULL | ABSENT } ON NULL
] [ { WITH | WITHOUT } UNIQUE [ KEYS ] ] [ RETURNING data_type [
FORMAT JSON [ ENCODING UTF8 ] ] ])

Constructs a JSON object of all the key/value pairs given, or an empty
object if none are given.

the clauses do not change the result when there are no pairs.

NOTE:

The problems seems to occur only when there are no key value pairs,

Currently, calling JSON_OBJECT() without key-value arguments fails
with a syntax error
if modifier clauses like ABSENT ON NULL or WITH UNIQUE KEYS are specified. This
happens because the grammar rule in gram.y handling modifier clauses
unconditionally
required key-value pairs.

The attached patch adds conflict-free productions in gram.y to allow empty
JSON_OBJECT() calls with NULL, UNIQUE, and RETURNING clauses.
Regression tests have also been added to sqljson.sql covering all clause
combinations for empty objects.

Thank you,
Narayanan

On Wed, Oct 14, 2026 at 7:06 PM Narayanan Venkateswaran
<narayananvpostgres@gmail.com> wrote:

Currently, calling JSON_OBJECT() without key-value arguments fails
with a syntax error
if modifier clauses like ABSENT ON NULL or WITH UNIQUE KEYS are specified. This
happens because the grammar rule in gram.y handling modifier clauses
unconditionally
required key-value pairs.

The attached patch adds conflict-free productions in gram.y to allow empty
JSON_OBJECT() calls with NULL, UNIQUE, and RETURNING clauses.
Regression tests have also been added to sqljson.sql covering all clause
combinations for empty objects.

Thanks for the patch. It seems to work fine. I think it would be good
to make the same change for JSON_ARRAY.

One question: The commit message has this line

The SQL standard permits calling JSON_OBJECT() with no key-value pairs
to construct an empty JSON object while still specifying modifier
clauses such as NULL-handling (NULL ON NULL / ABSENT ON NULL), key
uniqueness constraints (WITH / WITHOUT UNIQUE KEYS), and the output type
(RETURNING ...).

Is there any reference for this? I searched for this using an LLM and
found that the standard doesn't specify it but Oracle etc implement it
this way.

Thanks & Regards
Nitin Motiani,
Google

Hi Nitin,

Thank you very much for the review!

On Sun, Oct 18, 2026 at 11:45 AM Nitin Motiani <nitinmotiani@google.com> wrote:

On Wed, Oct 14, 2026 at 7:06 PM Narayanan Venkateswaran
<narayananvpostgres@gmail.com> wrote:

Currently, calling JSON_OBJECT() without key-value arguments fails
with a syntax error
if modifier clauses like ABSENT ON NULL or WITH UNIQUE KEYS are specified. This
happens because the grammar rule in gram.y handling modifier clauses
unconditionally
required key-value pairs.

The attached patch adds conflict-free productions in gram.y to allow empty
JSON_OBJECT() calls with NULL, UNIQUE, and RETURNING clauses.
Regression tests have also been added to sqljson.sql covering all clause
combinations for empty objects.

Thanks for the patch. It seems to work fine. I think it would be good
to make the same change for JSON_ARRAY.

Thank you for pointing this out, I will look into JSON_ARRAY and
submit a follow up.

That said, please find some replies inline,

One question: The commit message has this line

The SQL standard permits calling JSON_OBJECT() with no key-value pairs
to construct an empty JSON object while still specifying modifier
clauses such as NULL-handling (NULL ON NULL / ABSENT ON NULL), key
uniqueness constraints (WITH / WITHOUT UNIQUE KEYS), and the output type
(RETURNING ...).

Is there any reference for this?

You are right, I checked this link -
https://github.com/ronsavage/SQL/blob/19215adc8639d031a44acad7873c209444b71f1f/sql-2016.ebnf#L2266-L2272

JSON_object_constructor ::=
JSON_OBJECT left_paren
( JSON_name_and_value ( comma JSON_name_and_value )*
JSON_constructor_null_clause?
JSON_key_uniqueness_constraint? )?
JSON_output_clause?
right_paren

JSON_constructor_null_clause? and JSON_key_uniqueness_constraint? are
nested inside the outer ( ... )?
that begins with a required JSON_name_and_value. This implies that
JSON_name_and_value is not optional.

However, this is different from the postgres documentation,

https://www.postgresql.org/docs/devel/functions-json.html#FUNCTIONS-JSON-CREATION-TABLE

json_object ( [ { key_expression { VALUE | ':' } value_expression [
FORMAT JSON [ ENCODING UTF8 ] ] }[, ...] ]
[ { NULL | ABSENT } ON NULL ]
[ { WITH | WITHOUT } UNIQUE [ KEYS ] ]
[ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ])

This treats the key:value as different from the ON NULL and UNIQUE
optional clauses

I searched for this using an LLM and
found that the standard doesn't specify it but Oracle etc implement it
this way.

I will investigate JSON_ARRAY from the perspective of the postgre
documentation and revert back.

Thanks & Regards
Nitin Motiani,
Google

Thank you,
Narayanan

Hi,

On Mon, Oct 19, 2026 at 5:03 PM Narayanan Venkateswaran
<narayananvpostgres@gmail.com> wrote:

Hi Nitin,

Thank you very much for the review!

On Sun, Oct 18, 2026 at 11:45 AM Nitin Motiani <nitinmotiani@google.com> wrote:

On Wed, Oct 14, 2026 at 7:06 PM Narayanan Venkateswaran
<narayananvpostgres@gmail.com> wrote:

Thanks for the patch. It seems to work fine. I think it would be good
to make the same change for JSON_ARRAY.

Thank you for pointing this out, I will look into JSON_ARRAY and
submit a follow up.

.
.
.

I will investigate JSON_ARRAY from the perspective of the postgre
documentation and revert back.

I am able to reproduce the same issue in JSON_ARRAY,

The following works,

postgres=# SELECT JSON_ARRAY();
json_array
------------
[]
(1 row)

postgres=# SELECT JSON_ARRAY('a', NULL, 'b' NULL ON NULL);
json_array
------------------
["a", null, "b"]
(1 row)

postgres=# SELECT JSON_ARRAY('a', NULL, 'b' ABSENT ON NULL);

json_array
------------
["a", "b"]
(1 row)

The following does not work,

postgres=# SELECT JSON_ARRAY(NULL ON NULL);

ERROR: syntax error at or near "ON"
LINE 1: SELECT JSON_ARRAY(NULL ON NULL);
^
postgres=# SELECT JSON_ARRAY(ABSENT ON NULL);

ERROR: syntax error at or near "ON"
LINE 1: SELECT JSON_ARRAY(ABSENT ON NULL);
^
postgres=# SELECT JSON_ARRAY(NULL ON NULL RETURNING jsonb);
ERROR: syntax error at or near "ON"
LINE 1: SELECT JSON_ARRAY(NULL ON NULL RETURNING jsonb);

I also checked the postgres documentation here -
https://www.postgresql.org/docs/devel/functions-json.html#FUNCTIONS-JSON-CREATION-TABLE

json_array ( [ { value_expression [ FORMAT JSON ] } [, ...] ]
[ { NULL | ABSENT } ON NULL ]
[ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ])

[ { value_expression ... } [, ...] ] is an optional bracket.
[ { NULL | ABSENT } ON NULL ] is an independent optional bracket.
[ RETURNING data_type ... ] is an independent optional bracket.

Because each bracket is independent in PostgreSQL's documented syntax,
calling JSON_ARRAY()
with no value expressions while providing a { NULL | ABSENT } ON NULL
modifier clause should be
valid syntax, but is currently rejected by the parser.

I am working on a follow-up patch that addresses json_array also, I
shall submit it soon.

Thanks & Regards
Nitin Motiani,
Google

Thank you,
Narayanan

Thank you,
Narayanan

Hi,

On Tue, Oct 20, 2026 at 12:57 AM Narayanan Venkateswaran
<narayananvpostgres@gmail.com> wrote:

Hi,

On Mon, Oct 19, 2026 at 5:03 PM Narayanan Venkateswaran
<narayananvpostgres@gmail.com> wrote:

Hi Nitin,

Thank you very much for the review!

On Sun, Oct 18, 2026 at 11:45 AM Nitin Motiani <nitinmotiani@google.com> wrote:

On Wed, Oct 14, 2026 at 7:06 PM Narayanan Venkateswaran
<narayananvpostgres@gmail.com> wrote:

Thanks for the patch. It seems to work fine. I think it would be good
to make the same change for JSON_ARRAY.

.
.
.

I am working on a follow-up patch that addresses json_array also, I
shall submit it soon.

Please find attached the patch that fixes the problem in json_array also.

Thank you,
Narayanan

I am working on a follow-up patch that addresses json_array also, I
shall submit it soon.

Please find attached the patch that fixes the problem in json_array also.

Thanks Narayanan. It seems to work fine. One concern I have is that
this differs from the SQL standard. Since the documentation is already
different, this is probably the right fix to make. But the commit
message and the documentation should reflect this change. What do you
think?

Also can you create a commitfest entry for this?

Regards,
Nitin Motiani
Google