I’m afraid I might be missing something here, because the documentation for ANY and EVERY state the following regarding return values:
If at least one item in the array satisfies the ANY expression, then it returns the entire array; otherwise, it returns an empty array.
If all the items in the array satisfies the EVERY expression, then it returns the entire array; otherwise, it returns an empty array.
Don’t they just return a BOOLEAN? Is this a documentation quirk, or is there a nuance here that I’m somehow missing where the result could actually be an array? Simple tests like “SELECT ANY v IN [1, 2, 3] SATISFIES v = 2 END” show that a boolean comes back, not the whole array or an empty array.
Again, making sure I’m not missing something. I wanted to include an ANY expression as an index key expression, and would very much prefer the whole array is not the return value that gets embedded in the index (even in some weird misunderstood condition)…
a1 is MISSING returns MISSING
a1 is not array returns NULL
If at least one item in the array satisfies then returns true
else false
SELECT ANY v IN MISSING SATISFIES v.id = 5 END m,
ANY v IN NULL SATISFIES v.id = 5 END n,
ANY v IN "abc" SATISFIES v.id = 5 END n1,
ANY v IN [{"id":5}] SATISFIES v.id = 5 END t1,
ANY v IN [{"id":6}] SATISFIES v.id = 5 END f1;
"results": [
{
"f1": false,
"n": null,
"n1": null,
"t1": true
}
In WHERE/ON clause MISSING,NULL,false consider false
Including in indexkey will not benefit anything, If you want you can add in index condition and query must have exact same including variable.
If you can provide index definition/query in mind will able to help.
Example: Index ANY and query ANY clause must match exactly including variable v
CREATE INDEX ix1 ON default (c1,c2,c3) WHERE type = "xyz" AND ANY v IN a1 SATISFIES v.id = 5 END;
SELECT ....
FROM default AS d
WHERE d.type = "xyz" AND ANY v IN d.a1 SATISFIES v.id = 5 END AND d.c1 > 10 AND .......
