public inbox for [email protected]
help / color / mirror / Atom feedBUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor>
7+ messages / 5 participants
[nested] [flat]
* BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor>
@ 2026-02-26 09:57 PG Bug reporting form <[email protected]>
2026-02-26 14:20 ` Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> Vik Fearing <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: PG Bug reporting form @ 2026-02-26 09:57 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
The following bug has been logged on the website:
Bug reference: 19418
Logged by: Lukas Eder
Email address: [email protected]
PostgreSQL version: 18.2
Operating system: Linux
Description:
When using the ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor by query>
syntax, 6.34 GR 4) b) i) says that empty tables should produce a JSON array
with no elements (intuitively), not NULL.
Try this:
select json_array(select 1 where false);
It produces NULL, not []
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor>
2026-02-26 09:57 BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> PG Bug reporting form <[email protected]>
@ 2026-02-26 14:20 ` Vik Fearing <[email protected]>
2026-02-27 14:44 ` Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> Richard Guo <[email protected]>
2026-03-03 02:41 ` Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> jian he <[email protected]>
0 siblings, 2 replies; 7+ messages in thread
From: Vik Fearing @ 2026-02-26 14:20 UTC (permalink / raw)
To: [email protected]; [email protected]; PG Bug reporting form <[email protected]>
On 26/02/2026 10:57, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 19418
> Logged by: Lukas Eder
> Email address: [email protected]
> PostgreSQL version: 18.2
> Operating system: Linux
> Description:
>
> When using the ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor by query>
> syntax, 6.34 GR 4) b) i) says that empty tables should produce a JSON array
> with no elements (intuitively), not NULL.
>
> Try this:
>
> select json_array(select 1 where false);
>
> It produces NULL, not []
I can confirm that postgres violates the standard here.
--
Vik Fearing
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor>
2026-02-26 09:57 BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> PG Bug reporting form <[email protected]>
2026-02-26 14:20 ` Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> Vik Fearing <[email protected]>
@ 2026-02-27 14:44 ` Richard Guo <[email protected]>
2026-03-02 05:09 ` Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> Richard Guo <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: Richard Guo @ 2026-02-27 14:44 UTC (permalink / raw)
To: Vik Fearing <[email protected]>; +Cc: [email protected]; [email protected]; PG Bug reporting form <[email protected]>
On Thu, Feb 26, 2026 at 11:20 PM Vik Fearing <[email protected]> wrote:
> On 26/02/2026 10:57, PG Bug reporting form wrote:
> > Try this:
> >
> > select json_array(select 1 where false);
> >
> > It produces NULL, not []
> I can confirm that postgres violates the standard here.
It looks like postgres rewrites JSON_ARRAY(query) into JSON_ARRAYAGG()
internally:
explain (verbose, costs off)
select json_array(select 1 where false);
QUERY PLAN
---------------------------------------------------
Result
Output: (InitPlan expr_1).col1
InitPlan expr_1
-> Aggregate
Output: JSON_ARRAYAGG(1 RETURNING json)
-> Result
One-Time Filter: false
(7 rows)
The comment above transformJsonArrayQueryConstructor() says:
/*
* Transform JSON_ARRAY(query [FORMAT] [RETURNING] [ON NULL]) into
* (SELECT JSON_ARRAYAGG(a [FORMAT] [RETURNING] [ON NULL]) FROM (query) q(a))
*/
Because of this transformation, we inherit standard aggregate
behavior: evaluating an aggregate over an empty set without a GROUP BY
yields NULL instead of the expected [].
I wonder if we can fix it by wrapping the JSON_ARRAYAGG in a COALESCE
to catch the NULL and convert it to an empty array; ie:
SELECT COALESCE(
JSON_ARRAYAGG(a [FORMAT] [RETURNING] [ON NULL]),
'[]'::[RETURNING_TYPE]
) FROM (query) q(a)
- Richard
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor>
2026-02-26 09:57 BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> PG Bug reporting form <[email protected]>
2026-02-26 14:20 ` Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> Vik Fearing <[email protected]>
2026-02-27 14:44 ` Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> Richard Guo <[email protected]>
@ 2026-03-02 05:09 ` Richard Guo <[email protected]>
2026-03-02 05:45 ` Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> Richard Guo <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Richard Guo @ 2026-03-02 05:09 UTC (permalink / raw)
To: Vik Fearing <[email protected]>; +Cc: [email protected]; [email protected]; PG Bug reporting form <[email protected]>
On Fri, Feb 27, 2026 at 11:44 PM Richard Guo <[email protected]> wrote:
> I wonder if we can fix it by wrapping the JSON_ARRAYAGG in a COALESCE
> to catch the NULL and convert it to an empty array; ie:
>
> SELECT COALESCE(
> JSON_ARRAYAGG(a [FORMAT] [RETURNING] [ON NULL]),
> '[]'::[RETURNING_TYPE]
> ) FROM (query) q(a)
The attached patch seems to fix it.
- Richard
Attachments:
[application/octet-stream] v1-0001-Fix-JSON_ARRAY-query-to-return-on-empty-sets.patch (7.1K, 2-v1-0001-Fix-JSON_ARRAY-query-to-return-on-empty-sets.patch)
download | inline diff:
From 23cd04c3cf316e0ee0e6977dc2ef4994d404e732 Mon Sep 17 00:00:00 2001
From: Richard Guo <[email protected]>
Date: Mon, 2 Mar 2026 12:11:00 +0900
Subject: [PATCH v1] Fix JSON_ARRAY(query) to return [] on empty sets
According to the SQL/JSON standard, JSON_ARRAY(query) must return an
empty JSON array ('[]') when the subquery returns zero rows.
Previously, the parser rewrote JSON_ARRAY(query) into a JSON_ARRAYAGG
aggregate function. Because this aggregate evaluates to NULL over an
empty set without a GROUP BY clause, the constructor erroneously
returned NULL.
This patch fixes the issue by wrapping the internally generated
JSON_ARRAYAGG node in a COALESCE expression during parse analysis,
catching the NULL from empty sets and replacing it with an empty array
cast to the correct RETURNING type.
---
doc/src/sgml/func/func-json.sgml | 3 ++-
src/backend/parser/parse_expr.c | 15 +++++++++++++--
src/test/regress/expected/sqljson.out | 22 ++++++++++++++++++----
src/test/regress/sql/sqljson.sql | 5 +++++
4 files changed, 38 insertions(+), 7 deletions(-)
diff --git a/doc/src/sgml/func/func-json.sgml b/doc/src/sgml/func/func-json.sgml
index 1ec73cff464..d510ba8375b 100644
--- a/doc/src/sgml/func/func-json.sgml
+++ b/doc/src/sgml/func/func-json.sgml
@@ -620,7 +620,8 @@
which must be a SELECT query returning a single column. 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.
+ <replaceable>query_expression</replaceable> is used. If the query returns
+ no rows, an empty JSON array is returned.
</para>
<para>
<literal>json_array(1,true,json '{"a":null}')</literal>
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index dcfe1acc4c3..f9719a8598c 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -3768,7 +3768,8 @@ transformJsonObjectConstructor(ParseState *pstate, JsonObjectConstructor *ctor)
/*
* Transform JSON_ARRAY(query [FORMAT] [RETURNING] [ON NULL]) into
- * (SELECT JSON_ARRAYAGG(a [FORMAT] [RETURNING] [ON NULL]) FROM (query) q(a))
+ * (SELECT COALESCE(JSON_ARRAYAGG(a [FORMAT] [RETURNING] [ON NULL]), '[]')
+ * FROM (query) q(a))
*/
static Node *
transformJsonArrayQueryConstructor(ParseState *pstate,
@@ -3780,6 +3781,7 @@ transformJsonArrayQueryConstructor(ParseState *pstate,
Alias *alias = makeNode(Alias);
ResTarget *target = makeNode(ResTarget);
JsonArrayAgg *agg = makeNode(JsonArrayAgg);
+ CoalesceExpr *coalesce = makeNode(CoalesceExpr);
ColumnRef *colref = makeNode(ColumnRef);
Query *query;
ParseState *qpstate;
@@ -3813,9 +3815,18 @@ transformJsonArrayQueryConstructor(ParseState *pstate,
agg->constructor->output = ctor->output;
agg->constructor->location = ctor->location;
+ /*
+ * JSON_ARRAY(query) requires returning an empty array if the subquery
+ * returns zero rows. However, the internal JSON_ARRAYAGG evaluates to
+ * NULL on an empty set. We wrap the aggregate in a COALESCE to catch
+ * this NULL and replace it with an empty JSON array.
+ */
+ coalesce->args = list_make2(agg, makeStringConst("[]", ctor->location));
+ coalesce->location = ctor->location;
+
target->name = NULL;
target->indirection = NIL;
- target->val = (Node *) agg;
+ target->val = (Node *) coalesce;
target->location = ctor->location;
alias->aliasname = pstrdup("q");
diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out
index c7b9e575445..1af7fe78107 100644
--- a/src/test/regress/expected/sqljson.out
+++ b/src/test/regress/expected/sqljson.out
@@ -724,6 +724,7 @@ SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' FORMAT JSON RETURNING text) FORMAT
[[{ "a" : 123 }]]
(1 row)
+-- JSON_ARRAY(subquery)
SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i));
json_array
------------
@@ -757,6 +758,19 @@ SELECT JSON_ARRAY(WITH x AS (SELECT 1) VALUES (TRUE));
[true]
(1 row)
+-- JSON_ARRAY(subquery) with empty result set
+SELECT JSON_ARRAY(SELECT 1 WHERE FALSE);
+ json_array
+------------
+ []
+(1 row)
+
+SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) WHERE i > 4);
+ json_array
+------------
+ []
+(1 row)
+
-- Should fail
SELECT JSON_ARRAY(SELECT FROM (VALUES (1)) foo(i));
ERROR: subquery must return only one column
@@ -1090,13 +1104,13 @@ DROP VIEW json_arrayagg_view;
-- Test JSON_ARRAY(subquery) deparsing
EXPLAIN (VERBOSE, COSTS OFF)
SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING jsonb);
- QUERY PLAN
----------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------
Result
Output: (InitPlan expr_1).col1
InitPlan expr_1
-> Aggregate
- Output: JSON_ARRAYAGG("*VALUES*".column1 RETURNING jsonb)
+ Output: COALESCE(JSON_ARRAYAGG("*VALUES*".column1 RETURNING jsonb), '[]'::jsonb)
-> Values Scan on "*VALUES*"
Output: "*VALUES*".column1
(7 rows)
@@ -1105,7 +1119,7 @@ CREATE VIEW json_array_subquery_view AS
SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING jsonb);
\sv json_array_subquery_view
CREATE OR REPLACE VIEW public.json_array_subquery_view AS
- SELECT ( SELECT JSON_ARRAYAGG(q.a RETURNING jsonb) AS "json_arrayagg"
+ SELECT ( SELECT COALESCE(JSON_ARRAYAGG(q.a RETURNING jsonb), '[]'::jsonb) AS "coalesce"
FROM ( SELECT foo.i
FROM ( VALUES (1), (2), (NULL::integer), (4)) foo(i)) q(a)) AS "json_array"
DROP VIEW json_array_subquery_view;
diff --git a/src/test/regress/sql/sqljson.sql b/src/test/regress/sql/sqljson.sql
index 343d344d270..881da122a72 100644
--- a/src/test/regress/sql/sqljson.sql
+++ b/src/test/regress/sql/sqljson.sql
@@ -193,6 +193,7 @@ SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' RETURNING text));
SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' FORMAT JSON RETURNING text));
SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' FORMAT JSON RETURNING text) FORMAT JSON);
+-- JSON_ARRAY(subquery)
SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i));
SELECT JSON_ARRAY(SELECT i FROM (VALUES (NULL::int[]), ('{1,2}'), (NULL), (NULL), ('{3,4}'), (NULL)) foo(i));
SELECT JSON_ARRAY(SELECT i FROM (VALUES (NULL::int[]), ('{1,2}'), (NULL), (NULL), ('{3,4}'), (NULL)) foo(i) RETURNING jsonb);
@@ -201,6 +202,10 @@ SELECT JSON_ARRAY(SELECT i FROM (VALUES (NULL::int[]), ('{1,2}'), (NULL), (NULL)
SELECT JSON_ARRAY(SELECT i FROM (VALUES (3), (1), (NULL), (2)) foo(i) ORDER BY i);
SELECT JSON_ARRAY(WITH x AS (SELECT 1) VALUES (TRUE));
+-- JSON_ARRAY(subquery) with empty result set
+SELECT JSON_ARRAY(SELECT 1 WHERE FALSE);
+SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) WHERE i > 4);
+
-- Should fail
SELECT JSON_ARRAY(SELECT FROM (VALUES (1)) foo(i));
SELECT JSON_ARRAY(SELECT i, i FROM (VALUES (1)) foo(i));
--
2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor>
2026-02-26 09:57 BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> PG Bug reporting form <[email protected]>
2026-02-26 14:20 ` Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> Vik Fearing <[email protected]>
2026-02-27 14:44 ` Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> Richard Guo <[email protected]>
2026-03-02 05:09 ` Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> Richard Guo <[email protected]>
@ 2026-03-02 05:45 ` Richard Guo <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Richard Guo @ 2026-03-02 05:45 UTC (permalink / raw)
To: Vik Fearing <[email protected]>; +Cc: [email protected]; [email protected]; PG Bug reporting form <[email protected]>; Álvaro Herrera <[email protected]>
On Mon, Mar 2, 2026 at 2:09 PM Richard Guo <[email protected]> wrote:
> On Fri, Feb 27, 2026 at 11:44 PM Richard Guo <[email protected]> wrote:
> > I wonder if we can fix it by wrapping the JSON_ARRAYAGG in a COALESCE
> > to catch the NULL and convert it to an empty array; ie:
> >
> > SELECT COALESCE(
> > JSON_ARRAYAGG(a [FORMAT] [RETURNING] [ON NULL]),
> > '[]'::[RETURNING_TYPE]
> > ) FROM (query) q(a)
> The attached patch seems to fix it.
(cc-ing Álvaro who committed 7081ac46a)
Regarding back-patching, I believe this fix is safe to back-patch to
stable branches. However, similar to a nearby bug fix, this will only
apply to newly created views. Existing views will continue to exhibit
the old behavior until recreated. Additionally, this changes the
user-facing output from NULL to [], so users may need to update any
application code that relied on the NULL behavior.
- Richard
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor>
2026-02-26 09:57 BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> PG Bug reporting form <[email protected]>
2026-02-26 14:20 ` Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> Vik Fearing <[email protected]>
@ 2026-03-03 02:41 ` jian he <[email protected]>
2026-04-20 08:29 ` Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> Amit Langote <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: jian he @ 2026-03-03 02:41 UTC (permalink / raw)
To: Vik Fearing <[email protected]>; +Cc: [email protected]; [email protected]; PG Bug reporting form <[email protected]>
On Thu, Feb 26, 2026 at 10:20 PM Vik Fearing <[email protected]> wrote:
>
> > Try this:
> >
> > select json_array(select 1 where false);
> >
> > It produces NULL, not []
>
>
> I can confirm that postgres violates the standard here.
>
> --
Since the subject title mentioned JSON_VALUE.
SELECT JSON_VALUE(((select NULL where false)), '$');
SELECT JSON_QUERY(((select NULL where false)), '$');
SELECT JSON_EXISTS(((select NULL where false)), '$');
Should the above produce []?
--
jian
https://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor>
2026-02-26 09:57 BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> PG Bug reporting form <[email protected]>
2026-02-26 14:20 ` Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> Vik Fearing <[email protected]>
2026-03-03 02:41 ` Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> jian he <[email protected]>
@ 2026-04-20 08:29 ` Amit Langote <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Amit Langote @ 2026-04-20 08:29 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: Vik Fearing <[email protected]>; [email protected]; [email protected]; PG Bug reporting form <[email protected]>
On Tue, Mar 3, 2026 at 11:42 AM jian he <[email protected]> wrote:
> On Thu, Feb 26, 2026 at 10:20 PM Vik Fearing <[email protected]> wrote:
> > > Try this:
> > >
> > > select json_array(select 1 where false);
> > >
> > > It produces NULL, not []
> >
> >
> > I can confirm that postgres violates the standard here.
> >
> > --
>
> Since the subject title mentioned JSON_VALUE.
>
> SELECT JSON_VALUE(((select NULL where false)), '$');
> SELECT JSON_QUERY(((select NULL where false)), '$');
> SELECT JSON_EXISTS(((select NULL where false)), '$');
>
> Should the above produce []?
AFAIK about the standard, no. The empty-set -> '[]' rule is specific
to JSON_ARRAY(<query>), whose job is to collect rows into an array.
JSON_VALUE, JSON_QUERY, and JSON_EXISTS return a scalar, a JSON value,
and a boolean, respectively, not array-shaped values, so there's no
empty-array concept to invoke; empty-input behavior is governed by ON
EMPTY / ON ERROR.
--
Thanks, Amit Langote
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2026-04-20 08:29 UTC | newest]
Thread overview: 7+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-02-26 09:57 BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor> PG Bug reporting form <[email protected]>
2026-02-26 14:20 ` Vik Fearing <[email protected]>
2026-02-27 14:44 ` Richard Guo <[email protected]>
2026-03-02 05:09 ` Richard Guo <[email protected]>
2026-03-02 05:45 ` Richard Guo <[email protected]>
2026-03-03 02:41 ` jian he <[email protected]>
2026-04-20 08:29 ` Amit Langote <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox