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

От:
jian he <jian.universality@gmail.com>
Дата:
Вложения:
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/

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

От:
Narayanan Venkateswaran <narayananvpostgres@gmail.com>
Дата:
Вложения:
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

От:
Narayanan Venkateswaran <narayananvpostgres@gmail.com>
Дата:
Вложения:
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

От:
Narayanan Venkateswaran <narayananvpostgres@gmail.com>
Дата:
Вложения:
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


FAQ