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
Last activity: Jul 7, 2025, 02:39 PM