Обсуждение: BUG #16489: Cannot strictly compare arrays in jsonb_path_query call that uses vars
BUG #16489: Cannot strictly compare arrays in jsonb_path_query call that uses vars
От
PG Bug reporting form
Дата:
The following bug has been logged on the website: Bug reference: 16489 Logged by: Dahan Jeremy Email address: jeremydahan@protonmail.com PostgreSQL version: 12.1 Operating system: macOS 10.14 Description: Hi, I found this very strange behavior of the jsonb_path_query function : Example : ``` SELECT jsonb_path_query('{"a":["b","c"]}'::jsonb, '$.a ? (@ == $d)' , '{"d":["b","c","e"]}'::jsonb) ``` When using the jsonb_path_query function with a vars argument that includes an array (here with key d), I cannot compare the queried array against the vars array. Instead, I get a per element comparison, which means instead of returning ["b","c"] the query returns "b" "c" just like the query ``` SELECT jsonb_path_query('{"a":["b","c"]}'::jsonb, '$.a[*] ? (@ == $d)' , '{"d":["b","c","e"]}'::jsonb) ``` (notice the $.a[*] instead of the $.a) Interestingly, this means that the following query also returns the same result ``` SELECT jsonb_path_query('{"a":["b","c"]}'::jsonb, '$.a[*] ? (@ != $d)' , '{"d":["b","c","e"]}'::jsonb) ``` (notice the != instead of the ==) I could not find mentions of this strange behavior in the documentation, and I would love to know if there is a way of comparing an array against another array in a jsonb_path_query function call (without using a WHERE clause). By the way, the current behavior can be useful for finding intersections of arrays. Thank you for this truly incredible project Best regards, Jeremy Dahan