pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses
12+ messages / 4 participants
[nested] [flat]
* BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses
@ 2026-10-02 22:36 PG Bug reporting form <noreply@postgresql.org>
2026-10-04 16:14 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses jian he <jian.universality@gmail.com>
2026-10-06 06:02 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: PG Bug reporting form @ 2026-10-02 22:36 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: theshallow27@gmail.com
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses
2026-10-02 22:36 BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses PG Bug reporting form <noreply@postgresql.org>
@ 2026-10-04 16:14 ` jian he <jian.universality@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: jian he @ 2026-10-04 16:14 UTC (permalink / raw)
To: theshallow27@gmail.com; pgsql-bugs@lists.postgresql.org
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/
Attachments:
draft_json_object_fix.txt (text/plain, 2.8K) download
diff --git a/doc/src/sgml/func/func-json.sgml b/doc/src/sgml/func/func-json.sgml
index 0763ec81ed8..cc021e18bcb 100644
--- a/doc/src/sgml/func/func-json.sgml
+++ b/doc/src/sgml/func/func-json.sgml
@@ -710,15 +710,29 @@
<entry role="func_table_entry"><para role="func_signature">
<indexterm><primary>json_object</primary></indexterm>
<function>json_object</function> (
- <optional> { <replaceable>key_expression</replaceable> { <literal>VALUE</literal> | ':' }
- <replaceable>value_expression</replaceable> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional> }<optional>, ...</optional> </optional>
+ { <replaceable>key_expression</replaceable> { <literal>VALUE</literal> | ':' }
+ <replaceable>value_expression</replaceable> <optional>
+ <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional> }<optional>, ...</optional>
<optional> { <literal>NULL</literal> | <literal>ABSENT</literal> } <literal>ON NULL</literal> </optional>
<optional> { <literal>WITH</literal> | <literal>WITHOUT</literal> } <literal>UNIQUE</literal> <optional> <literal>KEYS</literal> </optional> </optional>
<optional> <literal>RETURNING</literal> <replaceable>data_type</replaceable> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional> </optional>)
</para>
+
+ <para role="func_signature">
+ <indexterm>
+ <primary>json_object</primary>
+ </indexterm>
+ <function>json_object</function> (
+ <optional> <literal>RETURNING</literal> <replaceable>data_type</replaceable>
+ <optional> <literal>FORMAT JSON</literal>
+ <optional> <literal>ENCODING UTF8</literal> </optional> </optional> </optional>)
+ </para>
+
<para>
- Constructs a JSON object of all the key/value pairs given,
- or an empty object if none are given.
+ The first form constructs a JSON object of all the key/value pairs
+ given; the second form constructs an empty JSON object.
+ The result is of type <type>json</type> unless
+ <literal>RETURNING</literal> specifies a different type.
<replaceable>key_expression</replaceable> is a scalar expression
defining the <acronym>JSON</acronym> key, which is
converted to the <type>text</type> type.
@@ -735,6 +749,10 @@
<para>
<literal>json_object('code' VALUE 'P123', 'title': 'Jaws')</literal>
<returnvalue>{"code" : "P123", "title" : "Jaws"}</returnvalue>
+ </para>
+ <para>
+ <literal>json_object(RETURNING jsonb)</literal>
+ <returnvalue>{}</returnvalue>
</para></entry>
</row>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses
2026-10-02 22:36 BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses PG Bug reporting form <noreply@postgresql.org>
@ 2026-10-06 06:02 ` Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-14 13:35 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Narayanan Venkateswaran @ 2026-10-06 06:02 UTC (permalink / raw)
To: theshallow27@gmail.com; pgsql-bugs@lists.postgresql.org; +Cc: Dilip Kumar <dilipbalaut@gmail.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
> `{}`;
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses
2026-10-02 22:36 BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses PG Bug reporting form <noreply@postgresql.org>
2026-10-06 06:02 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
@ 2026-10-14 13:35 ` Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-18 06:15 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Nitin Motiani <nitinmotiani@google.com>
0 siblings, 1 reply; 12+ messages in thread
From: Narayanan Venkateswaran @ 2026-10-14 13:35 UTC (permalink / raw)
To: theshallow27@gmail.com; pgsql-bugs@lists.postgresql.org; +Cc: Dilip Kumar <dilipbalaut@gmail.com>
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
> Thank you,
> Narayanan
Attachments:
v1-0001-Allow-NULL-and-UNIQUE-clauses-in-empty-JSON_OBJEC.patch (application/octet-stream, 5.8K) download
From 36c8e8534ed2e4868b8b511a8f300ccb3015c74e Mon Sep 17 00:00:00 2001
From: Narayanan Venkateswaran <narayananvenk@google.com>
Date: Wed, 14 Oct 2026 12:12:18 +0000
Subject: [PATCH v1] Allow NULL and UNIQUE clauses in empty JSON_OBJECT()
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 ...).
Previously, gram.y only accepted empty arguments via:
JSON_OBJECT '(' json_returning_clause_opt ')'
Any attempt to specify null or uniqueness clauses without key-value
pairs, such as JSON_OBJECT(ABSENT ON NULL) or JSON_OBJECT(WITH UNIQUE
KEYS), resulted in a syntax error because the rule with modifier clauses
unconditionally required a non-empty json_name_and_value_list.
Simply making json_name_and_value_list optional causes shift/reduce
conflicts in Bison because tokens like NULL and ABSENT can serve as both
expressions/identifiers and clause keywords, requiring more than one
token of lookahead to disambiguate.
Fix by factoring json_object_constructor_null_clause and
json_key_uniqueness_constraint into non-optional base rules, and adding
dedicated productions for empty JSON_OBJECT() calls when a null clause
or uniqueness clause is present.
Add regression tests in sqljson covering all valid combinations of
modifier clauses for empty JSON_OBJECT() calls.
---
src/backend/parser/gram.y | 41 +++++++++++++++++++++++++--
src/test/regress/expected/sqljson.out | 36 +++++++++++++++++++++++
src/test/regress/sql/sqljson.sql | 6 ++++
3 files changed, 81 insertions(+), 2 deletions(-)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0563453fe24..95a739a4361 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -689,7 +689,9 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
json_table_default_plan_union_cross
json_wrapper_behavior
%type <boolean> json_key_uniqueness_constraint_opt
+ json_key_uniqueness_constraint
json_object_constructor_null_clause_opt
+ json_object_constructor_null_clause
json_array_constructor_null_clause_opt
/*
@@ -16484,6 +16486,33 @@ func_expr_common_subexpr:
n->location = @1;
$$ = (Node *) n;
}
+ | JSON_OBJECT '('
+ json_object_constructor_null_clause
+ json_key_uniqueness_constraint_opt
+ json_returning_clause_opt ')'
+ {
+ JsonObjectConstructor *n = makeNode(JsonObjectConstructor);
+
+ n->exprs = NULL;
+ n->absent_on_null = $3;
+ n->unique = $4;
+ n->output = (JsonOutput *) $5;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
+ | JSON_OBJECT '('
+ json_key_uniqueness_constraint
+ json_returning_clause_opt ')'
+ {
+ JsonObjectConstructor *n = makeNode(JsonObjectConstructor);
+
+ n->exprs = NULL;
+ n->absent_on_null = false;
+ n->unique = $3;
+ n->output = (JsonOutput *) $4;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
| JSON_OBJECT '(' json_returning_clause_opt ')'
{
JsonObjectConstructor *n = makeNode(JsonObjectConstructor);
@@ -17531,11 +17560,15 @@ json_predicate_type_constraint:
* KEYS-less productions a precedence less than IDENT (i.e., less than KEYS).
* This prevents reducing them when the next token is KEYS.
*/
-json_key_uniqueness_constraint_opt:
+json_key_uniqueness_constraint:
WITH UNIQUE KEYS { $$ = true; }
| WITH UNIQUE %prec UNBOUNDED { $$ = true; }
| WITHOUT UNIQUE KEYS { $$ = false; }
| WITHOUT UNIQUE %prec UNBOUNDED { $$ = false; }
+ ;
+
+json_key_uniqueness_constraint_opt:
+ json_key_uniqueness_constraint { $$ = $1; }
| /* EMPTY */ %prec UNBOUNDED { $$ = false; }
;
@@ -17560,9 +17593,13 @@ json_name_and_value:
;
/* empty means false for objects, true for arrays */
-json_object_constructor_null_clause_opt:
+json_object_constructor_null_clause:
NULL_P ON NULL_P { $$ = false; }
| ABSENT ON NULL_P { $$ = true; }
+ ;
+
+json_object_constructor_null_clause_opt:
+ json_object_constructor_null_clause { $$ = $1; }
| /* EMPTY */ { $$ = false; }
;
diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out
index 30f9e10ef0a..029c0cee879 100644
--- a/src/test/regress/expected/sqljson.out
+++ b/src/test/regress/expected/sqljson.out
@@ -295,6 +295,42 @@ SELECT JSON_OBJECT();
{}
(1 row)
+SELECT JSON_OBJECT(NULL ON NULL);
+ json_object
+-------------
+ {}
+(1 row)
+
+SELECT JSON_OBJECT(ABSENT ON NULL);
+ json_object
+-------------
+ {}
+(1 row)
+
+SELECT JSON_OBJECT(WITH UNIQUE KEYS);
+ json_object
+-------------
+ {}
+(1 row)
+
+SELECT JSON_OBJECT(WITHOUT UNIQUE);
+ json_object
+-------------
+ {}
+(1 row)
+
+SELECT JSON_OBJECT(NULL ON NULL WITH UNIQUE KEYS);
+ json_object
+-------------
+ {}
+(1 row)
+
+SELECT JSON_OBJECT(ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb);
+ json_object
+-------------
+ {}
+(1 row)
+
SELECT JSON_OBJECT(RETURNING json);
json_object
-------------
diff --git a/src/test/regress/sql/sqljson.sql b/src/test/regress/sql/sqljson.sql
index 9fa203ccb53..55dd1558160 100644
--- a/src/test/regress/sql/sqljson.sql
+++ b/src/test/regress/sql/sqljson.sql
@@ -64,6 +64,12 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SERIALIZE('{}' RETURNING bytea);
-- JSON_OBJECT()
SELECT JSON_OBJECT();
+SELECT JSON_OBJECT(NULL ON NULL);
+SELECT JSON_OBJECT(ABSENT ON NULL);
+SELECT JSON_OBJECT(WITH UNIQUE KEYS);
+SELECT JSON_OBJECT(WITHOUT UNIQUE);
+SELECT JSON_OBJECT(NULL ON NULL WITH UNIQUE KEYS);
+SELECT JSON_OBJECT(ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb);
SELECT JSON_OBJECT(RETURNING json);
SELECT JSON_OBJECT(RETURNING json FORMAT JSON);
SELECT JSON_OBJECT(RETURNING jsonb);
--
2.56.0.385.gd3acb90ef8-goog
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses
2026-10-02 22:36 BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses PG Bug reporting form <noreply@postgresql.org>
2026-10-06 06:02 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-14 13:35 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
@ 2026-10-18 06:15 ` Nitin Motiani <nitinmotiani@google.com>
2026-10-19 11:33 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Nitin Motiani @ 2026-10-18 06:15 UTC (permalink / raw)
To: Narayanan Venkateswaran <narayananvpostgres@gmail.com>; +Cc: theshallow27@gmail.com, pgsql-bugs@lists.postgresql.org, Dilip Kumar <dilipbalaut@gmail.com>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses
2026-10-02 22:36 BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses PG Bug reporting form <noreply@postgresql.org>
2026-10-06 06:02 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-14 13:35 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-18 06:15 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Nitin Motiani <nitinmotiani@google.com>
@ 2026-10-19 11:33 ` Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-19 19:27 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Narayanan Venkateswaran @ 2026-10-19 11:33 UTC (permalink / raw)
To: Nitin Motiani <nitinmotiani@google.com>; +Cc: theshallow27@gmail.com, pgsql-bugs@lists.postgresql.org, Dilip Kumar <dilipbalaut@gmail.com>
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-L...
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses
2026-10-02 22:36 BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses PG Bug reporting form <noreply@postgresql.org>
2026-10-06 06:02 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-14 13:35 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-18 06:15 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Nitin Motiani <nitinmotiani@google.com>
2026-10-19 11:33 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
@ 2026-10-19 19:27 ` Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-19 20:23 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Narayanan Venkateswaran @ 2026-10-19 19:27 UTC (permalink / raw)
To: Nitin Motiani <nitinmotiani@google.com>; +Cc: theshallow27@gmail.com, pgsql-bugs@lists.postgresql.org, Dilip Kumar <dilipbalaut@gmail.com>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses
2026-10-02 22:36 BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses PG Bug reporting form <noreply@postgresql.org>
2026-10-06 06:02 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-14 13:35 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-18 06:15 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Nitin Motiani <nitinmotiani@google.com>
2026-10-19 11:33 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-19 19:27 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
@ 2026-10-19 20:23 ` Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-21 08:45 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Nitin Motiani <nitinmotiani@google.com>
0 siblings, 1 reply; 12+ messages in thread
From: Narayanan Venkateswaran @ 2026-10-19 20:23 UTC (permalink / raw)
To: Nitin Motiani <nitinmotiani@google.com>; +Cc: theshallow27@gmail.com, pgsql-bugs@lists.postgresql.org, Dilip Kumar <dilipbalaut@gmail.com>
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
Attachments:
v2-0001-Allow-NULL-and-UNIQUE-clauses-in-empty-JSON_OBJEC.patch (application/octet-stream, 7.3K) download
From 454dbae1977707dd8ed101670e39a8516cc5e3d3 Mon Sep 17 00:00:00 2001
From: Narayanan Venkateswaran <narayananvenk@google.com>
Date: Mon, 19 Oct 2026 20:10:07 +0000
Subject: [PATCH v2] Allow NULL and UNIQUE clauses in empty JSON_OBJECT() and
JSON_ARRAY()
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Calling JSON_OBJECT() or JSON_ARRAY() with no value expressions while
specifying modifier clauses—such as NULL-handling (NULL ON NULL or
ABSENT ON NULL), key uniqueness constraints (WITH or WITHOUT UNIQUE KEYS
for objects), or the output type (RETURNING ...)—previously failed with a
syntax error.
In gram.y, the rules handling these modifier clauses unconditionally
required non-empty argument expression lists (json_name_and_value_list for
objects and json_value_expr_list for arrays). The rules accepting empty
argument lists only accepted the RETURNING clause.
Fix this by factoring json_object_constructor_null_clause,
json_array_constructor_null_clause, and json_key_uniqueness_constraint into
non-optional base rules, and adding dedicated productions in gram.y for
empty constructor calls when modifier clauses are present.
Add regression tests in sqljson covering modifier clause combinations for
empty JSON_OBJECT() and JSON_ARRAY() calls.
---
src/backend/parser/gram.y | 61 +++++++++++++++++++++++++--
src/test/regress/expected/sqljson.out | 60 ++++++++++++++++++++++++++
src/test/regress/sql/sqljson.sql | 10 +++++
3 files changed, 128 insertions(+), 3 deletions(-)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0563453fe24..03586a5a94b 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -689,8 +689,11 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
json_table_default_plan_union_cross
json_wrapper_behavior
%type <boolean> json_key_uniqueness_constraint_opt
+ json_key_uniqueness_constraint
json_object_constructor_null_clause_opt
+ json_object_constructor_null_clause
json_array_constructor_null_clause_opt
+ json_array_constructor_null_clause
/*
* Non-keyword token types. These are hard-wired into the "flex" lexer.
@@ -16484,6 +16487,33 @@ func_expr_common_subexpr:
n->location = @1;
$$ = (Node *) n;
}
+ | JSON_OBJECT '('
+ json_object_constructor_null_clause
+ json_key_uniqueness_constraint_opt
+ json_returning_clause_opt ')'
+ {
+ JsonObjectConstructor *n = makeNode(JsonObjectConstructor);
+
+ n->exprs = NULL;
+ n->absent_on_null = $3;
+ n->unique = $4;
+ n->output = (JsonOutput *) $5;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
+ | JSON_OBJECT '('
+ json_key_uniqueness_constraint
+ json_returning_clause_opt ')'
+ {
+ JsonObjectConstructor *n = makeNode(JsonObjectConstructor);
+
+ n->exprs = NULL;
+ n->absent_on_null = false;
+ n->unique = $3;
+ n->output = (JsonOutput *) $4;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
| JSON_OBJECT '(' json_returning_clause_opt ')'
{
JsonObjectConstructor *n = makeNode(JsonObjectConstructor);
@@ -16525,6 +16555,19 @@ func_expr_common_subexpr:
n->location = @1;
$$ = (Node *) n;
}
+ | JSON_ARRAY '('
+ json_array_constructor_null_clause
+ json_returning_clause_opt
+ ')'
+ {
+ JsonArrayConstructor *n = makeNode(JsonArrayConstructor);
+
+ n->exprs = NIL;
+ n->absent_on_null = $3;
+ n->output = (JsonOutput *) $4;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
| JSON_ARRAY '('
json_returning_clause_opt
')'
@@ -17531,11 +17574,15 @@ json_predicate_type_constraint:
* KEYS-less productions a precedence less than IDENT (i.e., less than KEYS).
* This prevents reducing them when the next token is KEYS.
*/
-json_key_uniqueness_constraint_opt:
+json_key_uniqueness_constraint:
WITH UNIQUE KEYS { $$ = true; }
| WITH UNIQUE %prec UNBOUNDED { $$ = true; }
| WITHOUT UNIQUE KEYS { $$ = false; }
| WITHOUT UNIQUE %prec UNBOUNDED { $$ = false; }
+ ;
+
+json_key_uniqueness_constraint_opt:
+ json_key_uniqueness_constraint { $$ = $1; }
| /* EMPTY */ %prec UNBOUNDED { $$ = false; }
;
@@ -17560,15 +17607,23 @@ json_name_and_value:
;
/* empty means false for objects, true for arrays */
-json_object_constructor_null_clause_opt:
+json_object_constructor_null_clause:
NULL_P ON NULL_P { $$ = false; }
| ABSENT ON NULL_P { $$ = true; }
+ ;
+
+json_object_constructor_null_clause_opt:
+ json_object_constructor_null_clause { $$ = $1; }
| /* EMPTY */ { $$ = false; }
;
-json_array_constructor_null_clause_opt:
+json_array_constructor_null_clause:
NULL_P ON NULL_P { $$ = false; }
| ABSENT ON NULL_P { $$ = true; }
+ ;
+
+json_array_constructor_null_clause_opt:
+ json_array_constructor_null_clause { $$ = $1; }
| /* EMPTY */ { $$ = true; }
;
diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out
index 30f9e10ef0a..6436aa56a70 100644
--- a/src/test/regress/expected/sqljson.out
+++ b/src/test/regress/expected/sqljson.out
@@ -295,6 +295,42 @@ SELECT JSON_OBJECT();
{}
(1 row)
+SELECT JSON_OBJECT(NULL ON NULL);
+ json_object
+-------------
+ {}
+(1 row)
+
+SELECT JSON_OBJECT(ABSENT ON NULL);
+ json_object
+-------------
+ {}
+(1 row)
+
+SELECT JSON_OBJECT(WITH UNIQUE KEYS);
+ json_object
+-------------
+ {}
+(1 row)
+
+SELECT JSON_OBJECT(WITHOUT UNIQUE);
+ json_object
+-------------
+ {}
+(1 row)
+
+SELECT JSON_OBJECT(NULL ON NULL WITH UNIQUE KEYS);
+ json_object
+-------------
+ {}
+(1 row)
+
+SELECT JSON_OBJECT(ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb);
+ json_object
+-------------
+ {}
+(1 row)
+
SELECT JSON_OBJECT(RETURNING json);
json_object
-------------
@@ -617,6 +653,30 @@ SELECT JSON_ARRAY();
[]
(1 row)
+SELECT JSON_ARRAY(NULL ON NULL);
+ json_array
+------------
+ []
+(1 row)
+
+SELECT JSON_ARRAY(ABSENT ON NULL);
+ json_array
+------------
+ []
+(1 row)
+
+SELECT JSON_ARRAY(NULL ON NULL RETURNING jsonb);
+ json_array
+------------
+ []
+(1 row)
+
+SELECT JSON_ARRAY(ABSENT ON NULL RETURNING jsonb);
+ json_array
+------------
+ []
+(1 row)
+
SELECT JSON_ARRAY(RETURNING json);
json_array
------------
diff --git a/src/test/regress/sql/sqljson.sql b/src/test/regress/sql/sqljson.sql
index 9fa203ccb53..aa02adeee33 100644
--- a/src/test/regress/sql/sqljson.sql
+++ b/src/test/regress/sql/sqljson.sql
@@ -64,6 +64,12 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SERIALIZE('{}' RETURNING bytea);
-- JSON_OBJECT()
SELECT JSON_OBJECT();
+SELECT JSON_OBJECT(NULL ON NULL);
+SELECT JSON_OBJECT(ABSENT ON NULL);
+SELECT JSON_OBJECT(WITH UNIQUE KEYS);
+SELECT JSON_OBJECT(WITHOUT UNIQUE);
+SELECT JSON_OBJECT(NULL ON NULL WITH UNIQUE KEYS);
+SELECT JSON_OBJECT(ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb);
SELECT JSON_OBJECT(RETURNING json);
SELECT JSON_OBJECT(RETURNING json FORMAT JSON);
SELECT JSON_OBJECT(RETURNING jsonb);
@@ -177,6 +183,10 @@ DROP TYPE mood;
-- JSON_ARRAY()
SELECT JSON_ARRAY();
+SELECT JSON_ARRAY(NULL ON NULL);
+SELECT JSON_ARRAY(ABSENT ON NULL);
+SELECT JSON_ARRAY(NULL ON NULL RETURNING jsonb);
+SELECT JSON_ARRAY(ABSENT ON NULL RETURNING jsonb);
SELECT JSON_ARRAY(RETURNING json);
SELECT JSON_ARRAY(RETURNING json FORMAT JSON);
SELECT JSON_ARRAY(RETURNING jsonb);
--
2.56.0.212.g129ede20d9-goog
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses
2026-10-02 22:36 BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses PG Bug reporting form <noreply@postgresql.org>
2026-10-06 06:02 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-14 13:35 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-18 06:15 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Nitin Motiani <nitinmotiani@google.com>
2026-10-19 11:33 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-19 19:27 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-19 20:23 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
@ 2026-10-21 08:45 ` Nitin Motiani <nitinmotiani@google.com>
2026-10-27 06:51 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Nitin Motiani @ 2026-10-21 08:45 UTC (permalink / raw)
To: Narayanan Venkateswaran <narayananvpostgres@gmail.com>; +Cc: theshallow27@gmail.com, pgsql-bugs@lists.postgresql.org, Dilip Kumar <dilipbalaut@gmail.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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses
2026-10-02 22:36 BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses PG Bug reporting form <noreply@postgresql.org>
2026-10-06 06:02 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-14 13:35 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-18 06:15 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Nitin Motiani <nitinmotiani@google.com>
2026-10-19 11:33 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-19 19:27 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-19 20:23 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-21 08:45 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Nitin Motiani <nitinmotiani@google.com>
@ 2026-10-27 06:51 ` Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-11-13 04:33 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Nitin Motiani <nitinmotiani@google.com>
0 siblings, 1 reply; 12+ messages in thread
From: Narayanan Venkateswaran @ 2026-10-27 06:51 UTC (permalink / raw)
To: Nitin Motiani <nitinmotiani@google.com>; +Cc: theshallow27@gmail.com, pgsql-bugs@lists.postgresql.org, Dilip Kumar <dilipbalaut@gmail.com>
Hi Nitin,
Thank you once again for the review.
On Wed, Oct 21, 2026 at 2:15 PM Nitin Motiani <nitinmotiani@google.com> 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
Attachments:
v3-0001-Allow-NULL-and-UNIQUE-clauses-in-empty-JSON_OBJEC.patch (application/octet-stream, 9.0K) download
From 39b95894e0822371283b99e776dc9164e9eb5ea6 Mon Sep 17 00:00:00 2001
From: Narayanan Venkateswaran <narayananvenk@google.com>
Date: Tue, 27 Oct 2026 05:52:05 +0000
Subject: [PATCH v3] Allow NULL and UNIQUE clauses in empty JSON_OBJECT() and
JSON_ARRAY()
The SQL standard (ISO/IEC 9075-2) grammar specifies modifier clauses (NULL
handling and key uniqueness constraints) within the optional group requiring
expression arguments, grammatically disallowing them when no arguments are
supplied. However, PostgreSQL's documentation specifies these clauses as
independent optional clauses for both JSON_OBJECT() and JSON_ARRAY(),
matching behavior in other implementations such as Oracle.
Previously, gram.y only accepted empty calls via:
JSON_OBJECT '(' json_returning_clause_opt ')'
JSON_ARRAY '(' json_returning_clause_opt ')'
Any attempt to specify null or uniqueness clauses without value expressions,
such as JSON_OBJECT(ABSENT ON NULL) or JSON_ARRAY(NULL ON NULL), resulted
in a syntax error because the rules handling modifier clauses unconditionally
required non-empty argument expression lists.
Fix by factoring json_object_constructor_null_clause,
json_array_constructor_null_clause, and json_key_uniqueness_constraint into
non-optional base rules, and adding dedicated conflict-free productions for
empty constructor calls when modifier clauses are present.
Also clarify the documentation and add regression tests in sqljson covering
modifier clause combinations for empty JSON_OBJECT() and JSON_ARRAY() calls.
---
doc/src/sgml/func/func-json.sgml | 7 ++-
src/backend/parser/gram.y | 61 +++++++++++++++++++++++++--
src/test/regress/expected/sqljson.out | 60 ++++++++++++++++++++++++++
src/test/regress/sql/sqljson.sql | 10 +++++
4 files changed, 133 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/func/func-json.sgml b/doc/src/sgml/func/func-json.sgml
index 0763ec81ed8..134beae5bd2 100644
--- a/doc/src/sgml/func/func-json.sgml
+++ b/doc/src/sgml/func/func-json.sgml
@@ -617,8 +617,10 @@
Constructs a JSON array from either a series of
<replaceable>value_expression</replaceable> parameters or from the results
of <replaceable>query_expression</replaceable>,
- which must be a SELECT query returning a single column. If
- <literal>ABSENT ON NULL</literal> is specified, NULL values are ignored.
+ which must be a SELECT query returning a single column.
+ An empty JSON array is returned if no parameters are given;
+ modifier clauses may also be specified in that case.
+ If <literal>ABSENT ON NULL</literal> is specified, NULL values are ignored.
This is always the case if a
<replaceable>query_expression</replaceable> is used. If the query returns
no rows, an empty JSON array is returned.
@@ -719,6 +721,7 @@
<para>
Constructs a JSON object of all the key/value pairs given,
or an empty object if none are given.
+ Modifier clauses may also be specified when constructing an empty object.
<replaceable>key_expression</replaceable> is a scalar expression
defining the <acronym>JSON</acronym> key, which is
converted to the <type>text</type> type.
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0563453fe24..03586a5a94b 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -689,8 +689,11 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
json_table_default_plan_union_cross
json_wrapper_behavior
%type <boolean> json_key_uniqueness_constraint_opt
+ json_key_uniqueness_constraint
json_object_constructor_null_clause_opt
+ json_object_constructor_null_clause
json_array_constructor_null_clause_opt
+ json_array_constructor_null_clause
/*
* Non-keyword token types. These are hard-wired into the "flex" lexer.
@@ -16484,6 +16487,33 @@ func_expr_common_subexpr:
n->location = @1;
$$ = (Node *) n;
}
+ | JSON_OBJECT '('
+ json_object_constructor_null_clause
+ json_key_uniqueness_constraint_opt
+ json_returning_clause_opt ')'
+ {
+ JsonObjectConstructor *n = makeNode(JsonObjectConstructor);
+
+ n->exprs = NULL;
+ n->absent_on_null = $3;
+ n->unique = $4;
+ n->output = (JsonOutput *) $5;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
+ | JSON_OBJECT '('
+ json_key_uniqueness_constraint
+ json_returning_clause_opt ')'
+ {
+ JsonObjectConstructor *n = makeNode(JsonObjectConstructor);
+
+ n->exprs = NULL;
+ n->absent_on_null = false;
+ n->unique = $3;
+ n->output = (JsonOutput *) $4;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
| JSON_OBJECT '(' json_returning_clause_opt ')'
{
JsonObjectConstructor *n = makeNode(JsonObjectConstructor);
@@ -16525,6 +16555,19 @@ func_expr_common_subexpr:
n->location = @1;
$$ = (Node *) n;
}
+ | JSON_ARRAY '('
+ json_array_constructor_null_clause
+ json_returning_clause_opt
+ ')'
+ {
+ JsonArrayConstructor *n = makeNode(JsonArrayConstructor);
+
+ n->exprs = NIL;
+ n->absent_on_null = $3;
+ n->output = (JsonOutput *) $4;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
| JSON_ARRAY '('
json_returning_clause_opt
')'
@@ -17531,11 +17574,15 @@ json_predicate_type_constraint:
* KEYS-less productions a precedence less than IDENT (i.e., less than KEYS).
* This prevents reducing them when the next token is KEYS.
*/
-json_key_uniqueness_constraint_opt:
+json_key_uniqueness_constraint:
WITH UNIQUE KEYS { $$ = true; }
| WITH UNIQUE %prec UNBOUNDED { $$ = true; }
| WITHOUT UNIQUE KEYS { $$ = false; }
| WITHOUT UNIQUE %prec UNBOUNDED { $$ = false; }
+ ;
+
+json_key_uniqueness_constraint_opt:
+ json_key_uniqueness_constraint { $$ = $1; }
| /* EMPTY */ %prec UNBOUNDED { $$ = false; }
;
@@ -17560,15 +17607,23 @@ json_name_and_value:
;
/* empty means false for objects, true for arrays */
-json_object_constructor_null_clause_opt:
+json_object_constructor_null_clause:
NULL_P ON NULL_P { $$ = false; }
| ABSENT ON NULL_P { $$ = true; }
+ ;
+
+json_object_constructor_null_clause_opt:
+ json_object_constructor_null_clause { $$ = $1; }
| /* EMPTY */ { $$ = false; }
;
-json_array_constructor_null_clause_opt:
+json_array_constructor_null_clause:
NULL_P ON NULL_P { $$ = false; }
| ABSENT ON NULL_P { $$ = true; }
+ ;
+
+json_array_constructor_null_clause_opt:
+ json_array_constructor_null_clause { $$ = $1; }
| /* EMPTY */ { $$ = true; }
;
diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out
index 30f9e10ef0a..6436aa56a70 100644
--- a/src/test/regress/expected/sqljson.out
+++ b/src/test/regress/expected/sqljson.out
@@ -295,6 +295,42 @@ SELECT JSON_OBJECT();
{}
(1 row)
+SELECT JSON_OBJECT(NULL ON NULL);
+ json_object
+-------------
+ {}
+(1 row)
+
+SELECT JSON_OBJECT(ABSENT ON NULL);
+ json_object
+-------------
+ {}
+(1 row)
+
+SELECT JSON_OBJECT(WITH UNIQUE KEYS);
+ json_object
+-------------
+ {}
+(1 row)
+
+SELECT JSON_OBJECT(WITHOUT UNIQUE);
+ json_object
+-------------
+ {}
+(1 row)
+
+SELECT JSON_OBJECT(NULL ON NULL WITH UNIQUE KEYS);
+ json_object
+-------------
+ {}
+(1 row)
+
+SELECT JSON_OBJECT(ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb);
+ json_object
+-------------
+ {}
+(1 row)
+
SELECT JSON_OBJECT(RETURNING json);
json_object
-------------
@@ -617,6 +653,30 @@ SELECT JSON_ARRAY();
[]
(1 row)
+SELECT JSON_ARRAY(NULL ON NULL);
+ json_array
+------------
+ []
+(1 row)
+
+SELECT JSON_ARRAY(ABSENT ON NULL);
+ json_array
+------------
+ []
+(1 row)
+
+SELECT JSON_ARRAY(NULL ON NULL RETURNING jsonb);
+ json_array
+------------
+ []
+(1 row)
+
+SELECT JSON_ARRAY(ABSENT ON NULL RETURNING jsonb);
+ json_array
+------------
+ []
+(1 row)
+
SELECT JSON_ARRAY(RETURNING json);
json_array
------------
diff --git a/src/test/regress/sql/sqljson.sql b/src/test/regress/sql/sqljson.sql
index 9fa203ccb53..aa02adeee33 100644
--- a/src/test/regress/sql/sqljson.sql
+++ b/src/test/regress/sql/sqljson.sql
@@ -64,6 +64,12 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SERIALIZE('{}' RETURNING bytea);
-- JSON_OBJECT()
SELECT JSON_OBJECT();
+SELECT JSON_OBJECT(NULL ON NULL);
+SELECT JSON_OBJECT(ABSENT ON NULL);
+SELECT JSON_OBJECT(WITH UNIQUE KEYS);
+SELECT JSON_OBJECT(WITHOUT UNIQUE);
+SELECT JSON_OBJECT(NULL ON NULL WITH UNIQUE KEYS);
+SELECT JSON_OBJECT(ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb);
SELECT JSON_OBJECT(RETURNING json);
SELECT JSON_OBJECT(RETURNING json FORMAT JSON);
SELECT JSON_OBJECT(RETURNING jsonb);
@@ -177,6 +183,10 @@ DROP TYPE mood;
-- JSON_ARRAY()
SELECT JSON_ARRAY();
+SELECT JSON_ARRAY(NULL ON NULL);
+SELECT JSON_ARRAY(ABSENT ON NULL);
+SELECT JSON_ARRAY(NULL ON NULL RETURNING jsonb);
+SELECT JSON_ARRAY(ABSENT ON NULL RETURNING jsonb);
SELECT JSON_ARRAY(RETURNING json);
SELECT JSON_ARRAY(RETURNING json FORMAT JSON);
SELECT JSON_ARRAY(RETURNING jsonb);
--
2.56.0.307.g5fe7bd680c-goog
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses
2026-10-02 22:36 BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses PG Bug reporting form <noreply@postgresql.org>
2026-10-06 06:02 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-14 13:35 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-18 06:15 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Nitin Motiani <nitinmotiani@google.com>
2026-10-19 11:33 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-19 19:27 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-19 20:23 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-21 08:45 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Nitin Motiani <nitinmotiani@google.com>
2026-10-27 06:51 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
@ 2026-11-13 04:33 ` Nitin Motiani <nitinmotiani@google.com>
2026-11-17 12:03 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Nitin Motiani <nitinmotiani@google.com>
0 siblings, 1 reply; 12+ messages in thread
From: Nitin Motiani @ 2026-11-13 04:33 UTC (permalink / raw)
To: Narayanan Venkateswaran <narayananvpostgres@gmail.com>; +Cc: theshallow27@gmail.com, pgsql-bugs@lists.postgresql.org, Dilip Kumar <dilipbalaut@gmail.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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses
2026-10-02 22:36 BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses PG Bug reporting form <noreply@postgresql.org>
2026-10-06 06:02 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-14 13:35 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-18 06:15 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Nitin Motiani <nitinmotiani@google.com>
2026-10-19 11:33 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-19 19:27 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-19 20:23 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-21 08:45 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Nitin Motiani <nitinmotiani@google.com>
2026-10-27 06:51 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-11-13 04:33 ` Re: BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses Nitin Motiani <nitinmotiani@google.com>
@ 2026-11-17 12:03 ` Nitin Motiani <nitinmotiani@google.com>
0 siblings, 0 replies; 12+ messages in thread
From: Nitin Motiani @ 2026-11-17 12:03 UTC (permalink / raw)
To: Narayanan Venkateswaran <narayananvpostgres@gmail.com>; +Cc: theshallow27@gmail.com, pgsql-bugs@lists.postgresql.org, Dilip Kumar <dilipbalaut@gmail.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
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2026-11-17 12:03 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-02 22:36 BUG #19737: Empty `JSON_OBJECT` cannot use documented `ON NULL` or unique-key clauses PG Bug reporting form <noreply@postgresql.org>
2026-10-04 16:14 ` jian he <jian.universality@gmail.com>
2026-10-06 06:02 ` Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-14 13:35 ` Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-18 06:15 ` Nitin Motiani <nitinmotiani@google.com>
2026-10-19 11:33 ` Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-19 19:27 ` Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-19 20:23 ` Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-10-21 08:45 ` Nitin Motiani <nitinmotiani@google.com>
2026-10-27 06:51 ` Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-11-13 04:33 ` Nitin Motiani <nitinmotiani@google.com>
2026-11-17 12:03 ` Nitin Motiani <nitinmotiani@google.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