public inbox for [email protected]help / color / mirror / Atom feed
BUG #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]> 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 14:20 Vik Fearing <[email protected]> parent: PG Bug reporting form <[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-27 14:44 Richard Guo <[email protected]> parent: Vik Fearing <[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-03-02 05:09 Richard Guo <[email protected]> parent: 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-03-02 05:45 Richard Guo <[email protected]> parent: 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-03-03 02:41 jian he <[email protected]> parent: Vik Fearing <[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-04-20 08:29 Amit Langote <[email protected]> parent: jian he <[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