Query with range and inequality filters on multiple properties overview

Firestore in Datastore mode supports using range and inequality filters on multiple properties in a single query. You can now have range and inequality conditions on multiple properties and simplify your application development by delegating implementation of post-filtering logic to Firestore in Datastore mode.

Range and inequality filters on multiple properties

The following query returns all users whose the age is greater than 35 and height is between 60 and 70 using range filters on age and height.

GQL

 SELECT * FROM /users WHERE age > 35 AND height > 60 AND height < 70;

Java


  Query<Entity> query =
    Query.newEntityQueryBuilder()
      .setKind("users")
      .setFilter(
        CompositeFilter.and(
            PropertyFilter.gt("age", 35), PropertyFilter.gt("height", 60), PropertyFilter.lt("height", 70)))
    .build();

Indexing considerations

Before you start running queries, make sure you have read about queries.

If an ORDER BY clause is not specified, Firestore in Datastore mode uses any index that can satisfy the query's filter condition to serve the query, producing a result set that is ordered according to the index definition.

To optimize the performance and cost of Firestore in Datastore mode queries, you should optimize the order of properties 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 [...]) index. Thus, the query that would be fast and cost-efficient would order salary before experience and look as follows:

GQL

SELECT *
FROM /employees
WHERE salary > 100000 AND experience > 0
ORDER BY salary, experience

Java

Query<Entity> query =
  Query.newEntityQueryBuilder()
    .setKind("employees")
    .setFilter(
        CompositeFilter.and(
            PropertyFilter.gt("salary", 100000), PropertyFilter.gt("experience", 0)))
    .setOrderBy(OrderBy("salary"), OrderBy("experience"))
    .build();

Node.js

const query = datastore
  .createQuery("employees")
  .filter(
    and([
      new PropertyFilter("salary", ">", 100000),
      new PropertyFilter("experience", ">", 0),
       ])
    )
  .order("salary")
  .order("experience");

Python

query = client.query(kind="employees")
query.add_filter("salary", ">", 100000)
query.add_filter("experience", ">", 0)
query.order = ["-salary", "-experience"]

Best practices for optimizing indexes

When optimizing indexes, note the following best practices.

Order queries by equalities followed by most selective range or inequality field

Firestore in Datastore mode uses the leftmost properties of a composite index to satisfy the equality constraints and the range & inequality constraint, if any, on the first field of the orderBy() query. These constraints can reduce the number of index entries that Firestore in Datastore mode scans. Firestore in Datastore mode uses the remaining properties of the index to satisfy other range & inequality constraints of the query. These constraints don't reduce the number of index entries that Firestore in Datastore mode scans but filter out unmatched entities so that the number of entities that are returned to the clients are reduced.

For more information about creating efficient indexes, see index structure and definition and optimizing indexes.

Order properties in decreasing order of query constraint selectivity

To ensure that Firestore in Datastore mode selects the optimal index for your query, specify an orderBy() clause that orders range & inequality properties in decreasing order of query constraint selectivity. Higher selectivity matches a smaller subset of entities, while lower selectivity matches a larger subset of entities. Ensure that you select range & inequality properties with higher selectivity earlier in the index ordering than properties with low selectivity.

To minimize the number of entities that Firestore in Datastore mode scans and returns over the network, you should always order properties 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

Query<Entity> query =
  Query.newEntityQueryBuilder()
    .setKind("employees")
    .setFilter(PropertyFilter.gt("salary", 100000))
    .setOrderBy(OrderBy("salary"))
    .build();
QueryResults<Entity> results = datastore.run(query);
// Order results by `experience`

Node.js

const query = datastore
  .createQuery("employees")
  .filter(new PropertyFilter("salary", ">", 100000))
  .order("salary");
const [entities] = await datastore.runQuery(query);
// Order results by `experience`

Python

query = client.query(kind="employees")
query.add_filter("salary", ">", 100000)
query.order = ["salary"]
results = query.fetch()
// Order results by `experience`

While adding an ordering on experience to the query will yield the same set of entities 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 Firestore in Datastore mode always prefers an index whose index properties prefix match the order by clause of the query. If experience were added to the order by clause, then Firestore in Datastore mode will select the (experience [...], salary [...]) index for computing query results. Since there are no other constraints on experience, Firestore in Datastore mode 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 properties are billed based on entities 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 properties:

  • Firestore in Datastore mode limits the number of range or inequality operators to 10. This is to prevent queries from becoming too expensive to run.

What's Next