MIME-Version: 1.0
References: <CAASxf_MW4RBUYuS3ybbJYH9U7LQ5cjT699naiG+xKX8g=7Ut2g@mail.gmail.com>
 <202403111534.xii7yxswadef@alvherre.pgsql> <CAASxf_PqyUZTo-h0C7qyGhEZQyuC6KUjqvWA4NeAYM+XT5NWnA@mail.gmail.com>
 <CAPF61jAwzR9OKAZJzmzMXNfok-+qxSD3-HnRAL43rX8-RQrPDQ@mail.gmail.com>
In-Reply-To: <CAPF61jAwzR9OKAZJzmzMXNfok-+qxSD3-HnRAL43rX8-RQrPDQ@mail.gmail.com>
From: Amit Langote <amitlangote09@gmail.com>
Date: Tue, 12 Mar 2024 21:07:01 +0900
Message-ID: <CA+HiwqE6kVsjDXg95+cEaErbUjecgrS_qTgcfV7MNT3k+58swA@mail.gmail.com>
Subject: Re: remaining sql/json patches
To: Himanshu Upadhyaya <upadhyaya.himanshu@gmail.com>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>, jian he <jian.universality@gmail.com>, 
	Andres Freund <andres@anarazel.de>, Andrew Dunstan <andrew@dunslane.net>, Erik Rijkers <er@xs4all.nl>, 
	PostgreSQL-development <pgsql-hackers@postgresql.org>, Tomas Vondra <tomas.vondra@enterprisedb.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CA%2BHiwqE6kVsjDXg95%2BcEaErbUjecgrS_qTgcfV7MNT3k%2B58swA%40mail.gmail.com>
Precedence: bulk

Hi Himanshu,

On Tue, Mar 12, 2024 at 6:42=E2=80=AFPM Himanshu Upadhyaya
<upadhyaya.himanshu@gmail.com> wrote:
>
> Hi,
>
> wanted to share the below case:
>
> =E2=80=98postgres[146443]=3D#=E2=80=99SELECT JSON_EXISTS(jsonb '{"custome=
r_name": "test", "salary":1000, "department_id":1}', '$.* ? (@=3D=3D $dept_=
id && @ =3D=3D $sal)' PASSING 1000 AS sal, 1 as dept_id);
>  json_exists
> -------------
>  f
> (1 row)
>
> isn't it supposed to return "true" as json in input is matching with both=
 the condition dept_id and salary?

I think you meant to use || in your condition, not &&, because 1000 !=3D 1.

See:

SELECT JSON_EXISTS(jsonb '{"customer_name": "test", "salary":1000,
"department_id":1}', '$.* ? (@ =3D=3D $dept_id || @ =3D=3D $sal)' PASSING 1=
000
AS sal, 1 as dept_id);
 json_exists
-------------
 t
(1 row)

Or you could've written the query as:

SELECT JSON_EXISTS(jsonb '{"customer_name": "test", "salary":1000,
"department_id":1}', '$ ? (@.department_id =3D=3D $dept_id && @.salary =3D=
=3D
$sal)' PASSING 1000 AS sal, 1 as dept_id);
 json_exists
-------------
 t
(1 row)

Does that make sense?

In any case, JSON_EXISTS() added by the patch here returns whatever
the jsonpath executor returns.  The latter is not touched by this
patch.  PASSING args, which this patch adds, seem to be working
correctly too.

--=20
Thanks, Amit Langote