Thread: A little confusion about JSON Path

Hello,

I don't understand why the following two JSON Path expressions aren't doing the same thing in Postgres 12:

    with sample (data) as (
      values
        ('{"k1": {"list":[1,2,3]}}'::jsonb)
    )
    select data, 
           jsonb_path_exists(data, '$.k1.list.type() ? (@ == "array")'), -- returns true as expected
           jsonb_path_exists(data, '$.k1.list ? (@.type() == "array")') -- returns false - not expected
    from sample;


Apparently "@.type()" returns something different then "$.k1.list.type()"

But maybe I simply don't understand how the @ is supposed to work.

Regards
Thomas

Thomas Kellerer wrote:
> I don't understand why the following two JSON Path expressions aren't doing the same thing in Postgres 12:
> 
>     with sample (data) as (
>       values
>         ('{"k1": {"list":[1,2,3]}}'::jsonb)
>     )
>     select data, 
>            jsonb_path_exists(data, '$.k1.list.type() ? (@ == "array")'), -- returns true as expected
>            jsonb_path_exists(data, '$.k1.list ? (@.type() == "array")') -- returns false - not expected
>     from sample;
> 
> 
> Apparently "@.type()" returns something different then "$.k1.list.type()"
> 
> But maybe I simply don't understand how the @ is supposed to work.

This seems to be a consequence of "lax" mode:

 "Besides, comparison operators automatically unwrap their operands in the lax mode,
  so you can compare SQL/JSON arrays out-of-the-box. An array of size 1 is considered
  equal to its sole element. Automatic unwrapping is not performed only when:

  - The path expression contains type() or size() methods that return the type and
    the number of elements in the array, respectively.

(from https://www.postgresql.org/docs/12/functions-json.html)

with sample (data) as (
  values
    ('{"k1": {"list":[1,2,3]}}'::jsonb)
)
select data,
       jsonb_path_exists(data, '$.k1.list ? (@.type() == "number")'),       -- lax mode unwraps the array
       jsonb_path_exists(data, 'strict $.k1.list ? (@.type() == "array")')  -- strict mode doesn't
from sample;


            data             | jsonb_path_exists | jsonb_path_exists 
-----------------------------+-------------------+-------------------
 {"k1": {"list": [1, 2, 3]}} | t                 | t
(1 row)

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Laurenz Albe schrieb am 17.10.2019 um 13:25:
>> I don't understand why the following two JSON Path expressions aren't doing the same thing in Postgres 12:
>>
>>  jsonb_path_exists(data, '$.k1.list.type() ? (@ == "array")'), -- returns true as expected
>>  jsonb_path_exists(data, '$.k1.list ? (@.type() == "array")') -- returns false - not expected
>>
>> Apparently "@.type()" returns something different then "$.k1.list.type()"
>>
> This seems to be a consequence of "lax" mode:
> 
>  "Besides, comparison operators automatically unwrap their operands in the lax mode,
>   so you can compare SQL/JSON arrays out-of-the-box. An array of size 1 is considered
>   equal to its sole element. Automatic unwrapping is not performed only when:
> 
>   - The path expression contains type() or size() methods that return the type and
>     the number of elements in the array, respectively.
> 
> (from https://www.postgresql.org/docs/12/functions-json.html)
> 
> with sample (data) as (
>   values
>     ('{"k1": {"list":[1,2,3]}}'::jsonb)
> )
> select data,
>        jsonb_path_exists(data, '$.k1.list ? (@.type() == "number")'),       -- lax mode unwraps the array
>        jsonb_path_exists(data, 'strict $.k1.list ? (@.type() == "array")')  -- strict mode doesn't
> from sample;


Ah, thanks. I did not see that part.