Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor>

Richard Guo <guofenglinux@gmail.com>

View thread View on postgr.es

From: Richard Guo <guofenglinux@gmail.com>

To: Vik Fearing <vik@postgresfriends.org>

Cc: lukas.eder@gmail.com, pgsql-bugs@lists.postgresql.org, PG Bug reporting form <noreply@postgresql.org>

Date: 2026-03-02T05:09:46Z

Lists: pgsql-bugs

Attachments

v1-0001-Fix-JSON_ARRAY-query-to-return-on-empty-sets.patch (application/octet-stream) patch v1-0001

On Fri, Feb 27, 2026 at 11:44 PM Richard Guo <guofenglinux@gmail.com> 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

Commits

Enforce RETURNING typmod for empty-set JSON_ARRAY(query)
- 9d124a14b3d4 19 (unreleased) landed
Fix JSON_ARRAY(query) empty set handling and view deparsing
- 8d829f5a0203 19 (unreleased) landed