Edit

OData collection operators in Azure AI Search - any and all

  • Article

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.

Syntax

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:

OData syntax diagram for Azure AI Search

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 returns true if the predicate is true for every element of the collection.
    • An expression using any returns true if the predicate is true for at least one element of the collection.
  • 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 returns true. If the collection is empty, it returns false.

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.

Examples

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)

Limitations

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.

Next steps