OData collection operators in Azure AI Search - any
and all
When writing an OData filter expression to use with Azure AI Search, it's often useful to filter on collection fields. You can achieve this using the any
and all
operators.
The following EBNF (Extended Backus-Naur Form) defines the grammar of an OData expression that uses any
or all
.
collection_filter_expression ::=
field_path'/all(' lambda_expression ')'
| field_path'/any(' lambda_expression ')'
| field_path'/any()'
lambda_expression ::= identifier ':' boolean_expression
An interactive syntax diagram is also available:
Note
See OData expression syntax reference for Azure AI Search for the complete EBNF.
There are three forms of expression that filter collections.
- The first two iterate over a collection field, applying a predicate given in the form of a lambda expression to each element of the collection.
- An expression using
all
returnstrue
if the predicate is true for every element of the collection. - An expression using
any
returnstrue
if the predicate is true for at least one element of the collection.
- An expression using
- The third form of collection filter uses
any
without a lambda expression to test whether a collection field is empty. If the collection has any elements, it returnstrue
. If the collection is empty, it returnsfalse
.
A lambda expression in a collection filter is like the body of a loop in a programming language. It defines a variable, called the range variable, that holds the current element of the collection during iteration. It also defines another boolean expression that is the filter criteria to apply to the range variable for each element of the collection.
Match documents whose tags
field contains exactly the string "wifi":
tags/any(t: t eq 'wifi')
Match documents where every element of the ratings
field falls between 3 and 5, inclusive:
ratings/all(r: r ge 3 and r le 5)
Match documents where any of the geo coordinates in the locations
field is within the given polygon:
locations/any(loc: geo.intersects(loc, geography'POLYGON((-122.031577 47.578581, -122.031577 47.678581, -122.131577 47.678581, -122.031577 47.578581))'))
Match documents where the rooms
field is empty:
not rooms/any()
Match documents where (for all rooms) the rooms/amenities
field contains "tv", and rooms/baseRate
is less than 100:
rooms/all(room: room/amenities/any(a: a eq 'tv') and room/baseRate lt 100.0)
Not every feature of filter expressions is available inside the body of a lambda expression. The limitations differ depending on the data type of the collection field that you want to filter. The following table summarizes the limitations.
Data type | Features allowed in lambda expressions with any |
Features allowed in lambda expressions with all |
---|---|---|
Collection(Edm.ComplexType) |
Everything except search.ismatch and search.ismatchscoring |
Same |
Collection(Edm.String) |
Comparisons with eq or search.in Combining sub-expressions with or |
Comparisons with ne or not search.in() Combining sub-expressions with and |
Collection(Edm.Boolean) |
Comparisons with eq or ne |
Same |
Collection(Edm.GeographyPoint) |
Using geo.distance with lt or le geo.intersects Combining sub-expressions with or |
Using geo.distance with gt or ge not geo.intersects(...) Combining sub-expressions with and |
Collection(Edm.DateTimeOffset) , Collection(Edm.Double) , Collection(Edm.Int32) , Collection(Edm.Int64) |
Comparisons using eq , ne , lt , gt , le , or ge Combining comparisons with other sub-expressions using or Combining comparisons except ne with other sub-expressions using and Expressions using combinations of and and or in Disjunctive Normal Form (DNF) |
Comparisons using eq , ne , lt , gt , le , or ge Combining comparisons with other sub-expressions using and Combining comparisons except eq with other sub-expressions using or Expressions using combinations of and and or in Conjunctive Normal Form (CNF) |
For more details on these limitations as well as examples, see Troubleshooting collection filters in Azure AI Search. For more in-depth information on why these limitations exist, see Understanding collection filters in Azure AI Search.