Restrictions on queries

This page covers the restrictions on querying Google Cloud Datastore from Google App Engine. The following list are common restrictions that you'll run into when developing for Cloud Datastore.

Entities lacking a property named in the query are ignored

Entities of the same kind need not have the same properties. To be eligible as a query result, an entity must possess a value (possibly null) for every property named in the query's filters and sort orders. If not, the entity is omitted from the indexes used to execute the query and consequently will not be included in the query's results.

Filtering on unindexed properties returns no results

A query can't find property values that aren't indexed, nor can it sort on such properties. See the Datastore Indexes page for a detailed discussion of unindexed properties.

Inequality filters are limited to at most one property

To avoid having to scan the entire index, the query mechanism relies on all of a query's potential results being adjacent to one another in the index. To satisfy this constraint, a single query may not use inequality comparisons (<, <=, >, >=, !=) on more than one property across all of its filters. For example, the following query is valid, because both inequality filters apply to the same property:

SELECT * FROM Person WHERE birth_year >= :min_birth_year
                       AND birth_year <= :max_birth_year

However, this query is not valid, because it uses inequality filters on two different properties:

SELECT * FROM Person WHERE birth_year >= :max_birth_year
                       AND height <= :max_height          # ERROR

Note that a query can combine equality (=) filters for different properties, along with one or more inequality filters on a single property. Thus the following is a valid query:

SELECT * FROM Person WHERE last_name = :target_last_name
                       AND city = :target_city
                       AND birth_year >= :min_birth_year
                       AND birth_year <= :max_birth_year

Ordering of query results is undefined when no sort order is specified

When a query does not specify a sort order, the results are returned in the order they are retrieved. As the Cloud Datastore implementation evolves (or if an application's indexes change), this order may change. Therefore, if your application requires its query results in a particular order, be sure to specify that sort order explicitly in the query.

Sort orders are ignored on properties with equality filters

Queries that include an equality filter for a given property ignore any sort order specified for that property. This is a simple optimization to save needless processing for single-valued properties, since all results have the same value for the property and so no further sorting is needed. Multiple-valued properties, however, may have additional values besides the one matched by the equality filter. Because this use case is rare and applying the sort order would be expensive and require extra indexes, the Cloud Datastore query planner simply ignores the sort order even in the multiple-valued case. This may cause query results to be returned in a different order than the sort order appears to imply.

Properties used in inequality filters must be sorted first

To retrieve all results that match an inequality filter, a query scans the index for the first row matching the filter, then scans forward until it encounters a nonmatching row. For the consecutive rows to encompass the complete result set, they must be ordered by the property used in the inequality filter before any other properties. Thus if a query specifies one or more inequality filters along with one or more sort orders, the first sort order must refer to the same property named in the inequality filters. The following is a valid query:

SELECT * FROM Person WHERE birth_year >= :min_birth_year
                     ORDER BY birth_year, last_name

This query is not valid, because it doesn't sort on the property used in the inequality filter:

SELECT * FROM Person WHERE birth_year >= :min_birth_year
                     ORDER BY last_name                   # ERROR

Similarly, this query is not valid because the property used in the inequality filter is not the first one sorted:

SELECT * FROM Person WHERE birth_year >= :min_birth_year
                     ORDER BY last_name, birth_year       # ERROR

Properties with multiple values can behave in surprising ways

Because of the way they're indexed, entities with multiple values for the same property can sometimes interact with query filters and sort orders in unexpected and surprising ways.

If a query has multiple inequality filters on a given property, an entity will match the query only if at least one of its individual values for the property satisfies all of the filters. For example, if an entity of kind Widget has values 1 and 2 for property x, it will not match the query:

SELECT * FROM Widget WHERE x > 1
                       AND x < 2

Each of the entity's x values satisfies one of the filters, but neither single value satisfies both. Note that this does not apply to equality filters. For example, the same entity will satisfy the query

SELECT * FROM Widget WHERE x = 1
                       AND x = 2

even though neither of the entity's individual x values satisfies both filter conditions.

The not-equal (!=) operator works as a "value is other than" test. So, for example, the query

SELECT * FROM Widget WHERE x != 1

matches any Widget entity with an x value other than 1.

Similarly, the sort order for multiple-valued properties is unusual. Because such properties appear once in the index for each unique value, the first value seen in the index determines an entity's sort order:

  • If the query results are sorted in ascending order, the smallest value of the property is used for ordering.
  • If the results are sorted in descending order, the greatest value is used for ordering.
  • Other values do not affect the sort order, nor does the number of values.

This has the unusual consequence that an entity with property values 1 and 9 precedes one with values 4, 5, 6, and 7 in both ascending and descending order.

Queries inside transactions must include ancestor filters

Cloud Datastore transactions operate only on entities belonging to the same entity group (descended from a common ancestor). To preserve this restriction, all queries performed within a transaction must include an ancestor filter specifying an ancestor in the same entity group as the other operations in the transaction.

Send feedback about...

App Engine standard environment for Python