Cloud Firestore supports using range and inequality filters on multiple fields in a single query. You can now have range and inequality conditions on multiple fields and simplify your application development by delegating implementation of post-filtering logic to Cloud Firestore.
Range and inequality filters on multiple fields
The following query returns all users whose age is greater than 35 and height is between 60 and 70 using range filters on age and height.
Web version 9 modular
const q = query(
collection(db, "users"),
where('age', '>', 35),
where('height', '>', 60),
where('height', '<', 70)
);
Swift
let query = db.collection("users")
.whereField("age", isGreaterThan: 35)
.whereField("height", isGreaterThan: 60)
.whereField("height", isLessThan: 70)
Objective-C
FIRQuery *query =
[[[[self.db collectionWithPath:@"users"]
queryWhereField:@"age" isGreaterThan:@35]
queryWhereField:@"height" isGreaterThan:@60]
queryWhereField:@"height" isLessThan:@70];
Java Android
Query query = db.collection("users")
.whereGreaterThan("age", 35)
.whereGreaterThan("height", 60)
.whereLessThan("height", 70);
Kotlin+KTX Android
val query = db.collection("users")
.whereGreaterThan("age", 35)
.whereGreaterThan("height", 60)
.whereLessThan("height", 70)
Java
db.collection("users")
.whereGreaterThan("age", 35)
.whereGreaterThan("height", 60)
.whereLessThan("height", 70);
Node.js
db.collection("users")
.where('age', '>', 35),
.where('height', '>', 60),
.where('height', '<', 70)
Indexing considerations
Before you start running your queries, make sure you have read about queries and the Cloud Firestore data model.
In Cloud Firestore, the ORDER BY
clause of a query determines which indexes
can be used to serve the query. For example, an ORDER BY a ASC, b ASC
query
requires a composite index on the a ASC, b ASC
fields.
To optimize the performance and cost of Cloud Firestore queries, you should optimize the order of fields in the index. To do this, you should ensure that your index is ordered from left to right such that the query distills to a dataset that prevents scanning of extraneous index entries.
Suppose you would like to search through a collection of employees and find
employees whose salary is more than 100000 and whose number of years of experience is
greater than 0. Based on your understanding of the dataset, you know that the
salary constraint is more selective than the experience constraint. The ideal
index that would reduce the number of index scans would be the
(salary [...], experience [...])
. Thus, the query that would be fast and
cost-efficient would order salary
before experience
and look as follows:
Java
db.collection("employees")
.whereGreaterThan("salary", 100000)
.whereGreaterThan("experience", 0)
.orderBy("salary")
.orderBy("experience");
Node.js
db.collection("employees")
.where("salary", ">", 100000)
.where("experience", ">", 0)
.orderBy("salary")
.orderBy("experience");
Python
db.collection("employees")
.where("salary", ">", 100000)
.where("experience", ">", 0)
.order_by("salary")
.order_by("experience");
Best practices for optimizing indexes
When optimizing indexes, note the following best practices.
Order index fields by equalities followed by most selective range or inequality field
Cloud Firestore uses the leftmost fields of a composite index to satisfy the
equality constraints and the range or inequality constraint, if any, on the first field
of the orderBy()
query. These constraints can reduce the number of index
entries that Cloud Firestore scans. Cloud Firestore uses the remaining fields
of the index to satisfy other range or inequality constraints of the query. These
constraints don't reduce the number of index entries that Cloud Firestore scans
but filter out unmatched documents so that the number of documents that are
returned to the clients are reduced.
For more information about creating efficient indexes, see the definition of a perfect index.
Order fields in decreasing order of query constraint selectivity
To ensure that Cloud Firestore selects the optimal index for your query,
specify an orderBy()
clause that orders fields in decreasing order of query
constraint selectivity. Higher selectivity matches a smaller subset of
documents, while lower selectivity matches a larger subset of documents. Ensure that
you select range or inequality fields with higher selectivity earlier in the index
ordering than fields with lower selectivity.
To minimize the number of documents that Cloud Firestore scans and returns over the network, you should always order fields in the decreasing order of query constraint selectivity. If the result set is not in the required order and the result set is expected to be small, you can implement client-side logic to reorder it as per your ordering expectation.
For example, suppose you would like to search through a collection of employees to find employees whose salary is more than 100000 and order the results by the year of experience of the employee. If you expect only a small number of employees will have salary more than 100000, then the most efficient way to write the query is as follows:
Java
db.collection("employees")
.whereGreaterThan("salary", 100000)
.orderBy("salary")
.get()
.addOnSuccessListener(new OnSuccessListener<QuerySnapshot>() {
@Override
public void onSuccess(QuerySnapshot queryDocumentSnapshots) {
// Order results by `experience`
}
});;
Node.js
const querySnapshot = await db.collection('employees')
.where("salary", ">", 100000)
.orderBy("salary")
.get();
// Order results by `experience`
Python
results = db.collection("employees")
.where("salary", ">", 100000)
.order_by("salary")
.stream()
// Order results by `experience`
While adding an ordering on experience
to the query will yield the same set
of documents and obviate re-ordering the results on the clients, the query may
read many more extraneous index entries than the earlier query. This is because
Cloud Firestore always prefers an index whose index fields prefix match the
order by clause of the query. If experience
were added to the order by clause,
then Cloud Firestore will select the (experience [...], salary [...])
index
for computing query results. Since there are no other constraints on
experience
, Cloud Firestore will read all index entries of the
employees
collection before applying the salary
filter to find the final
result set. This means that index entries which don't satisfy the salary
filter are still read, thus increasing the latency and cost of the query.
Pricing
Queries with range and inequality filters on multiple fields are billed based on documents read and index entries read.
For detailed information, see the Pricing page.
Limitations
Apart from the query limitations, note the following limitations before using queries with range and inequality filters on multiple fields:
- Queries with range or inequality filters on document fields and only equality
constraints on the document key
(__name__)
are not supported. - Cloud Firestore limits the number of range or inequality fields to 10. This is to prevent queries from becoming too expensive to run.
What's Next
- Learn about optimizing your queries.
- Learn more about performing simple and compound queries.
- Understand how Cloud Firestore uses indexes.