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
