Why does a Json Path inequality over an array not work?

Question

Given the following jsonb array, I want to filter it only for certain objects.

[
 {
 "id": "123"
 },
 {
 "id": "456"
 },
 {
 "id": "789"
 }
]

I can use the function jsonb_path_query_array with the path strict $[*] ? (@.id == $idToSearch[*]) and a variables parameter of {"idToSearch":["123","456"]} to filter by the id parameter. The logic being:

• $[*] breaks out the array
• ? is a filter predicate...
  • ... where the id property is equal to ...
  • ... any value in the idToSearch array variable.

I get this straightforward result:

[
 {
 "id": "123"
 },
 {
 "id": "456"
 }
]

If however, I invert the == in the path query to strict $[*] ? (@.id != $idToSearch[*]) then instead of getting the correct result of

[
 {
 "id": "789"
 }
]

instead I just get all the objects again.

As a workaround, I can use a negated equals strict $[*] ? (! (@.id == $idToSearch[*])) to get the correct result.

Why is the query not returning the correct result?

Is this a bug? If so, what's the cause and has it been filed? If it's not a bug, where am I going wrong in the path query, and what would be the correct way to express it?

---

create table test (
 _id int,
 items jsonb
);
insert into test values (1, '[
 {
 "id": "123"
 },
 {
 "id": "456"
 },
 {
 "id": "789"
 }
]');
select
 jsonb_path_query_array(
 items,
 'strict $[*] ? (@.id != $idToSearch[*])',
 '{"idToSearch":["123","456"]}')
from test;

db<>fiddle

Answer 1

(@.id == $idToSearch[*]) returns true if @.id is equal to any member of $idToSearch, yielding only those values present in $idToSearch[*].

(@.id != $idToSearch[*]) returns true if @.id is different from any member of $idToSearch, yielding every value from the left-hand array, because there's always at least one value in $idToSearch which is different.

!(@.id == $idToSearch[*]) is obviously the inverse of the first condition; it returns false if @.id is equal to any member of $idToSearch, that is, true if it is different from all members of $idToSearch.

In other words,

• (@.id == $idToSearch[*]) means @.id = ANY($idToSearch)
• (@.id != $idToSearch[*]) means @.id != ANY($idToSearch)
• !(@.id == $idToSearch[*]) means NOT (@.id == ANY($idToSearch)), or @.id != ALL($idToSearch)

• Charlieface: Is there any other way of writing "different from all members of the array"?
• mustaccio: What's wrong with the one you already got?
• Charlieface: Feels backwards with the double negative. Is there any other way of expressing != ANY in JsonPath?
• IMSoP: @Charlieface a != ANY(b) (on a native postgres array) would also fail, for exactly the same reason: it would spread the != operator across the elements, and then return true if any of those results is true: postgresql.org/docs/current/… What you want is an equivalent for a != ALL(b). Boolean logic is tricky that way.