BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses
BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses
От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
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
```
**Expected result:** Each form with an empty pair list should construct
`{}`;
the clauses do not change the result when there are no pairs.
Re: 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.
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 ] ] ])
--
jian
https://www.enterprisedb.com/
> 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 ( [ 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/
Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses
От:
Narayanan Venkateswaran <narayananvpostgres@gmail.com>
Дата:
Hi,
On Sun, Oct 4, 2026 at 2:46 AM PG Bug reporting form
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
Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses
Hi,
On Tue, Oct 6, 2026 at 11:32 AM Narayanan Venkateswaran
wrote:
>
> Hi,
>
> On Sun, Oct 4, 2026 at 2:46 AM PG Bug reporting form
> 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
> Thank you,
> Narayanan
Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses
От:
Nitin Motiani <nitinmotiani@google.com>
Дата:
On Wed, Oct 14, 2026 at 7:06 PM Narayanan Venkateswaran 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
Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses
От:
Narayanan Venkateswaran <narayananvpostgres@gmail.com>
Дата:
Hi Nitin,
Thank you very much for the review!
On Sun, Oct 18, 2026 at 11:45 AM Nitin Motiani wrote:
>
> On Wed, Oct 14, 2026 at 7:06 PM Narayanan Venkateswaran
> 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
Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses
От:
Narayanan Venkateswaran <narayananvpostgres@gmail.com>
Дата:
Hi,
On Mon, Oct 19, 2026 at 5:03 PM Narayanan Venkateswaran
wrote:
>
> Hi Nitin,
>
> Thank you very much for the review!
>
> On Sun, Oct 18, 2026 at 11:45 AM Nitin Motiani wrote:
> >
> > On Wed, Oct 14, 2026 at 7:06 PM Narayanan Venkateswaran
> > 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
Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses
Hi, On Tue, Oct 20, 2026 at 12:57 AM Narayanan Venkateswaran wrote: > > Hi, > > On Mon, Oct 19, 2026 at 5:03 PM Narayanan Venkateswaran > wrote: > > > > Hi Nitin, > > > > Thank you very much for the review! > > > > On Sun, Oct 18, 2026 at 11:45 AM Nitin Motiani wrote: > > > > > > On Wed, Oct 14, 2026 at 7:06 PM Narayanan Venkateswaran > > > 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
Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses
От:
Nitin Motiani <nitinmotiani@google.com>
Дата:
> > 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
Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses
Hi Nitin, Thank you once again for the review. On Wed, Oct 21, 2026 at 2:15 PM Nitin Motiani wrote: > > > > 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? I agree with your suggestion. While the SQL standard (ISO/IEC 9075-2) EBNF grammar nests the modifier clauses inside the mandatory expression bracket, PostgreSQL's documentation has always listed them as independent optional clauses. Explicitly acknowledging this difference in the commit message and clarifying it in the documentation makes sense. I have updated the commit message to describe this context and added clarifications to func-json.sgml. Attached V3 patch covers both JSON_OBJECT and JSON_ARRAY. > > Also can you create a commitfest entry for this? Great suggestion again ! Done ! https://commitfest.postgresql.org/patch/7527/. > > Regards, > Nitin Motiani > Google Thank you, Narayanan
Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses
От:
Nitin Motiani <nitinmotiani@google.com>
Дата:
Thanks Narayanan. I've added myself as the reviewer on the commitfest entry. Will take a look at v3 in a couple of days. Regards, Nitin Motiani Google
Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses
От:
Nitin Motiani <nitinmotiani@google.com>
Дата:
Hi Narayanan, I looked into v3. A couple of points points : 1. Like the commit message, I think we should also update the doc to say that this behaviour is an extension beyond the SQL standard. 2. It might be worth adding one empty form to the existing "Test JSON_ARRAY deparsing" section in sqljson.sql, since an empty JSON_ARRAY with NULL ON NULL now prints a form that wasn't possible before. For example: SELECT JSON_ARRAY(NULL ON NULL RETURNING jsonb); CREATE VIEW json_array_view AS SELECT JSON_ARRAY(NULL ON NULL RETURNING jsonb); \sv json_array_view DROP VIEW json_array_view; which shows JSON_ARRAY( NULL ON NULL RETURNING jsonb) in the view definition. Please let me know what you think. Thanks & Regards, Nitin Motiani Google