I dont want records who have LF in any array object, but it’s not working like that.
Query :
Select customerRoles from dataRT d
where d.type_=‘Policy’and d.customerRoles is not missing and
any cId IN customerRoles SATISFIES cId.id not like’%LF%’
end ;
Result : It should be a blank or another array who don’t have LF in any array object
Select customerRoles from dataRT d
where d.type_=‘Policy’ and d.customerRoles is not missing and
every cId IN customerRoles SATISFIES cId.id not like’%LF%’
end ;
ANY or SOME, EVERY, and ANY AND EVERY or SOME AND EVERY
Range predicates (ANY or SOME, EVERY, and ANY AND EVERY or SOME AND EVERY) allow you to test a boolean condition over the elements or attributes of a collection or object(s). They each evaluate to a boolean value.
ANY or SOME is TRUE if the collection is non-empty and at least one element matches.
EVERY is TRUE if the collection is empty, or if the collection is non-empty and every element matches.
ANY AND EVERY or SOME AND EVERY is TRUE if the collection is non-empty and every element matches.
Basically you want array that contains no LF. You need to reconstruct ARRAY again
Select ARRAY v FOR v IN customerRoles WHEN v.id not like ’%LF%’ END AS customerRoles from dataRT d
where d.type_=‘Policy’ and d.customerRoles is not missing and
ANY cId IN customerRoles SATISFIES cId.id not like’%LF%’
end ;
INSERT INTO default VALUES ("k001", { "type":"Policy", "customerRoles": [ { "id": "LA::CustomerRole::00000_0381_OW" }, { "id": "LA::CustomerRole::00000_49000376_LF" }, { "id": "LA:::CustomerRole::00000_49000375_JL" } ] });
SELECT ARRAY v.id FOR v IN d.customerRoles WHEN v.id NOT LIKE "%LF%" END AS customerRoles
FROM default AS d
WHERE d.type = "Policy" AND ANY v IN d.customerRoles SATISFIES v.id NOT LIKE "%LF%" END;
{
"results": [
{
"customerRoles": [
"LA::CustomerRole::00000_0381_OW",
"LA:::CustomerRole::00000_49000375_JL"
]
}
]
}
If that is not what you expected, please provide exact input object and output object will provide the query