Postgres : can't make JSONB_PATH_EXISTS work correctly-CodePudding

I'm struggling with JSONB_PATH_EXISTS Postgres function

I'm using PG 12 and following this documentation : https://www.postgresql.org/docs/12/functions-json.html

With the following request (test it on DBFiddle: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=d5aa984182852438c6f71cf5fa70324e) :

select 
    json
from (
    select '{
        "fields": {
            "foo": true,
            "number": 3,
            "listnb": [3, 4],
            "listenb2": ["3", "4"],
            "txt": "hello how are you",
            "listtxt": ["hello","how","are", "you", "3"],
            "nullval": null
        }

    }'::jsonb as json
) t
where 1=1
-- Works with 'strict'
AND JSONB_PATH_EXISTS(json -> 'fields' -> 'listtxt',  'strict $ ? (@.type() == "array")')

-- Doesn't work without 'strict'. Why ?
--AND JSONB_PATH_EXISTS(json -> 'fields' -> 'listtxt',  '$ ? (@.type() == "array")')

-- Can't add a nested condition on an array element value (syntax error)
--AND JSONB_PATH_EXISTS(json -> 'fields' -> 'listtxt',  'strict $ ? (@.type() == "array" && @[*] ? (@ == "how"))')
;

#1 - I can't get type() function work without strict mode

It could be related to the lax mode unwrapping arrays automatically, but the documentation explicitly states that it is not done when type() function is called :

The lax mode facilitates matching of a JSON document structure and path expression if the JSON data does not conform to the expected schema. [...] 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.

[...]

So I don't understand why we have a difference in the result

#2 I can't get the nested condition work (3rd AND in the sample request)

According to the examples in the documentation, the syntax looks OK but I have a syntax error that I don't understand.

Thank you for your help

CodePudding user response：

If you pass the complete JSON value to the function, then the following works:

where jsonb_path_exists(json, '$ ? (@.fields.listtxt.type() == "array")')

However I would probably simply use jsonb_typeof() without a path query

where jsonb_typeof(json -> 'fields' -> 'listtxt') = 'array'