Subject: Re: remaining sql/json patches

From: Alvaro Herrera <[email-masked]>
Date: Mon, 11 Mar 2024 16:34:31 +0100

On 2024-Mar-11, Shruthi Gowda wrote:

> *CASE 2:*
> ------------------
> SELECT * FROM JSON_TABLE(jsonb '{
>          "id" : 901,
>          "age" : 30,
>          "*FULL_NAME*" : "KATE DANIEL"}',
>                 '$'
>                 COLUMNS(
>                      FULL_NAME varchar(20),
>                      ID int,
>                      AGE int
>       )
>    ) as t;

I think this is expected: when you use FULL_NAME as a SQL identifier, it
is down-cased, so it no longer matches the uppercase identifier in the
JSON data.  You'd have to do it like this:

SELECT * FROM JSON_TABLE(jsonb '{
         "id" : 901,
         "age" : 30,
         "*FULL_NAME*" : "KATE DANIEL"}',
                '$'
                COLUMNS(
                     "FULL_NAME" varchar(20),
                     ID int,
                     AGE int
      )
   ) as t;

so that the SQL identifier is not downcased.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/

---

From: Shruthi Gowda <[email-masked]>
Date: Tue, 12 Mar 2024 00:37:34 +0530

Thanka Alvaro. It works fine when quotes are used around the column name.

---

From: Himanshu Upadhyaya <[email-masked]>
Date: Tue, 12 Mar 2024 15:11:58 +0530

Hi,

wanted to share the below case:

‘postgres[146443]=#’SELECT JSON_EXISTS(jsonb '{"customer_name": "test",
"salary":1000, "department_id":1}', '$.* ? (@== $dept_id && @ == $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?

-- 
Regards,
Himanshu Upadhyaya
EnterpriseDB: http://www.enterprisedb.com

---

From: Amit Langote <[email-masked]>
Date: Tue, 12 Mar 2024 21:07:01 +0900

Hi Himanshu,

On Tue, Mar 12, 2024 at 6:42 PM Himanshu Upadhyaya
<[email-masked]> wrote:
> ‘postgres[146443]=#’SELECT JSON_EXISTS(jsonb '{"customer_name": "test", "salary":1000, "department_id":1}', '$.* ? (@== $dept_id && @ == $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 != 1.

See:

SELECT JSON_EXISTS(jsonb '{"customer_name": "test", "salary":1000,
"department_id":1}', '$.* ? (@ == $dept_id || @ == $sal)' PASSING 1000
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 == $dept_id && @.salary ==
$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.

-- 
Thanks, Amit Langote

---

From: Himanshu Upadhyaya <[email-masked]>
Date: Wed, 13 Mar 2024 09:49:04 +0530

On Tue, Mar 12, 2024 at 5:37 PM Amit Langote <[email-masked]>
wrote:

> Yes, got it. Thanks for the clarification.