MIME-Version: 1.0
References: <19418-591ba1f29862ef5b@postgresql.org> <2abdb464-27f5-4759-bb0b-f09ab5b5ceab@postgresfriends.org>
 <CACJufxFRtto3WCDLusTsWQZqSdBqc9_LEuA_3S4sjfU_-U7ROw@mail.gmail.com>
In-Reply-To: <CACJufxFRtto3WCDLusTsWQZqSdBqc9_LEuA_3S4sjfU_-U7ROw@mail.gmail.com>
From: Amit Langote <amitlangote09@gmail.com>
Date: Mon, 20 Apr 2026 17:29:00 +0900
Message-ID: <CA+HiwqE1Co392qux47+DK31_HQMF9c-g1To+u1hxSOKwjzEN9g@mail.gmail.com>
Subject: Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC
 9075-2:2023(E) 6.34 <JSON value constructor>
To: jian he <jian.universality@gmail.com>
Cc: Vik Fearing <vik@postgresfriends.org>, lukas.eder@gmail.com, 
	pgsql-bugs@lists.postgresql.org, 
	PG Bug reporting form <noreply@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CA%2BHiwqE1Co392qux47%2BDK31_HQMF9c-g1To%2Bu1hxSOKwjzEN9g%40mail.gmail.com>
Precedence: bulk

On Tue, Mar 3, 2026 at 11:42=E2=80=AFAM jian he <jian.universality@gmail.co=
m> wrote:
> On Thu, Feb 26, 2026 at 10:20=E2=80=AFPM Vik Fearing <vik@postgresfriends=
.org> 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.

--=20
Thanks, Amit Langote