public inbox for [email protected]
help / color / mirror / Atom feedFrom: Richard Guo <[email protected]>
To: Vik Fearing <[email protected]>
Cc: [email protected]
Cc: [email protected]
Cc: PG Bug reporting form <[email protected]>
Subject: Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor>
Date: Mon, 2 Mar 2026 14:09:46 +0900
Message-ID: <CAMbWs4_f-4BqvfKTt0YbkOky+Gf0Mpi2kCT3CAE8brZFnK275Q@mail.gmail.com> (raw)
In-Reply-To: <CAMbWs4_4Zc7O4pCU_nJU_8=Y2bOS3sEXJR=WH39Kc__UuaCW3w@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<CAMbWs4_4Zc7O4pCU_nJU_8=Y2bOS3sEXJR=WH39Kc__UuaCW3w@mail.gmail.com>
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)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor>
In-Reply-To: <CAMbWs4_f-4BqvfKTt0YbkOky+Gf0Mpi2kCT3CAE8brZFnK275Q@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox