Sample Header Ad - 728x90

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

3 votes
1 answer
373 views
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;
**dbfiddle**
Asked by Charlieface (17545 rep)
Jul 7, 2025, 01:01 PM
Last activity: Jul 7, 2025, 02:39 PM