Datastore queries

A Datastore query retrieves entities from Datastore that meet a specified set of conditions.

A typical query includes the following:

  • An entity kind to which the query applies
  • Optional filters based on the entities' property values, keys, and ancestors
  • Optional sort orders to sequence the results

When executed, a query retrieves all entities of the given kind that satisfy all of the given filters, sorted in the specified order. Queries execute as read-only.

This page describes the structure and kinds of queries used within App Engine to retrieve data from Datastore.

Filters

A query's filters set constraints on the properties, keys, and ancestors of the entities to be retrieved.

Property filters

A property filter specifies

  • A property name
  • A comparison operator
  • A property value
For example:

Filter propertyFilter =
    new FilterPredicate("height", FilterOperator.GREATER_THAN_OR_EQUAL, minHeight);
Query q = new Query("Person").setFilter(propertyFilter);

The property value must be supplied by the application; it cannot refer to or be calculated in terms of other properties. An entity satisfies the filter if it has a property of the given name whose value compares to the value specified in the filter in the manner described by the comparison operator.

The comparison operator can be any of the following (defined as enumerated constants in the nested class Query.FilterOperator):

Operator Meaning
EQUAL Equal to
LESS_THAN Less than
LESS_THAN_OR_EQUAL Less than or equal to
GREATER_THAN Greater than
GREATER_THAN_OR_EQUAL Greater than or equal to
NOT_EQUAL Not equal to
IN Member of (equal to any of the values in a specified list)

The NOT_EQUAL operator actually performs two queries: one in which all other filters are unchanged and the NOT_EQUAL filter is replaced with a LESS_THAN filter, and one where it is replaced with a GREATER_THAN filter. The results are then merged, in order. A query can have no more than one NOT_EQUAL filter, and a query that has one cannot have any other inequality filters.

The IN operator also performs multiple queries: one for each item in the specified list, with all other filters unchanged and the IN filter replaced with an EQUAL filter. The results are merged in order of the items in the list. If a query has more than one IN filter, it is performed as multiple queries, one for each possible combination of values in the IN lists.

A single query containing NOT_EQUAL or IN operators is limited to a maximum of 30 subqueries.

For more information about how NOT_EQUAL and IN queries translate to multiple queries in a JDO/JPA framework, see the article Queries with != and IN filters.

Key filters

To filter on the value of an entity's key, use the special property Entity.KEY_RESERVED_PROPERTY:

Filter keyFilter =
    new FilterPredicate(Entity.KEY_RESERVED_PROPERTY, FilterOperator.GREATER_THAN, lastSeenKey);
Query q = new Query("Person").setFilter(keyFilter);

Ascending sorts on Entity.KEY_RESERVED_PROPERTY are also supported.

When comparing for inequality, keys are ordered by the following criteria, in order:

  1. Ancestor path
  2. Entity kind
  3. Identifier (key name or numeric ID)

Elements of the ancestor path are compared similarly: by kind (string), then by key name or numeric ID. Kinds and key names are strings and are ordered by byte value; numeric IDs are integers and are ordered numerically. If entities with the same parent and kind use a mix of key name strings and numeric IDs, those with numeric IDs precede those with key names.

Queries on keys use indexes just like queries on properties and require custom indexes in the same cases, with a couple of exceptions: inequality filters or an ascending sort order on the key do not require a custom index, but a descending sort order on the key does. As with all queries, the development web server creates appropriate entries in the index configuration file when a query that needs a custom index is tested.

Ancestor filters

You can filter your Datastore queries to a specified ancestor so that the results returned will include only entities descended from that ancestor:

Query q = new Query("Person").setAncestor(ancestorKey);

Special query types

Some specific types of query deserve special mention:

Kindless queries

A query with no kind and no ancestor filter retrieves all of the entities of an application from Datastore. This includes entities created and managed by other App Engine features, such as statistics entities and Blobstore metadata entities (if any). Such kindless queries cannot include filters or sort orders on property values. They can, however, filter on entity keys by specifying Entity.KEY_RESERVED_PROPERTY as the property name:

Filter keyFilter =
    new FilterPredicate(Entity.KEY_RESERVED_PROPERTY, FilterOperator.GREATER_THAN, lastSeenKey);
Query q = new Query().setFilter(keyFilter);

Ancestor queries

A query with an ancestor filter limits its results to the specified entity and its descendants:

DatastoreService datastore = DatastoreServiceFactory.getDatastoreService();

Entity tom = new Entity("Person", "Tom");
Key tomKey = tom.getKey();
datastore.put(tom);

Entity weddingPhoto = new Entity("Photo", tomKey);
weddingPhoto.setProperty("imageURL", "http://domain.com/some/path/to/wedding_photo.jpg");

Entity babyPhoto = new Entity("Photo", tomKey);
babyPhoto.setProperty("imageURL", "http://domain.com/some/path/to/baby_photo.jpg");

Entity dancePhoto = new Entity("Photo", tomKey);
dancePhoto.setProperty("imageURL", "http://domain.com/some/path/to/dance_photo.jpg");

Entity campingPhoto = new Entity("Photo");
campingPhoto.setProperty("imageURL", "http://domain.com/some/path/to/camping_photo.jpg");

List<Entity> photoList = Arrays.asList(weddingPhoto, babyPhoto, dancePhoto, campingPhoto);
datastore.put(photoList);

Query photoQuery = new Query("Photo").setAncestor(tomKey);

// This returns weddingPhoto, babyPhoto, and dancePhoto,
// but not campingPhoto, because tom is not an ancestor
List<Entity> results =
    datastore.prepare(photoQuery).asList(FetchOptions.Builder.withDefaults());

Kindless ancestor queries

A kindless query that includes an ancestor filter will retrieve the specified ancestor and all of its descendants, regardless of kind. This type of query does not require custom indexes. Like all kindless queries, it cannot include filters or sort orders on property values, but can filter on the entity's key:

Filter keyFilter =
    new FilterPredicate(Entity.KEY_RESERVED_PROPERTY, FilterOperator.GREATER_THAN, lastSeenKey);
Query q = new Query().setAncestor(ancestorKey).setFilter(keyFilter);

The following example illustrates how to retrieve all entities descended from a given ancestor:

DatastoreService datastore = DatastoreServiceFactory.getDatastoreService();

Entity tom = new Entity("Person", "Tom");
Key tomKey = tom.getKey();
datastore.put(tom);

Entity weddingPhoto = new Entity("Photo", tomKey);
weddingPhoto.setProperty("imageURL", "http://domain.com/some/path/to/wedding_photo.jpg");

Entity weddingVideo = new Entity("Video", tomKey);
weddingVideo.setProperty("videoURL", "http://domain.com/some/path/to/wedding_video.avi");

List<Entity> mediaList = Arrays.asList(weddingPhoto, weddingVideo);
datastore.put(mediaList);

// By default, ancestor queries include the specified ancestor itself.
// The following filter excludes the ancestor from the query results.
Filter keyFilter =
    new FilterPredicate(Entity.KEY_RESERVED_PROPERTY, FilterOperator.GREATER_THAN, tomKey);

Query mediaQuery = new Query().setAncestor(tomKey).setFilter(keyFilter);

// Returns both weddingPhoto and weddingVideo,
// even though they are of different entity kinds
List<Entity> results =
    datastore.prepare(mediaQuery).asList(FetchOptions.Builder.withDefaults());

Keys-only queries

A keys-only query returns just the keys of the result entities instead of the entities themselves, at lower latency and cost than retrieving entire entities:

Query q = new Query("Person").setKeysOnly();

It is often more economical to do a keys-only query first, and then fetch a subset of entities from the results, rather than executing a general query which may fetch more entities than you actually need.

Projection queries

Sometimes all you really need from the results of a query are the values of a few specific properties. In such cases, you can use a projection query to retrieve just the properties you're actually interested in, at lower latency and cost than retrieving the entire entity; see the Projection Queries page for details.

Sort orders

A query sort order specifies

  • A property name
  • A sort direction (ascending or descending)

For example:

// Order alphabetically by last name:
Query q1 = new Query("Person").addSort("lastName", SortDirection.ASCENDING);

// Order by height, tallest to shortest:
Query q2 = new Query("Person").addSort("height", SortDirection.DESCENDING);

If a query includes multiple sort orders, they are applied in the sequence specified. The following example sorts first by ascending last name and then by descending height:

Query q =
    new Query("Person")
        .addSort("lastName", SortDirection.ASCENDING)
        .addSort("height", SortDirection.DESCENDING);

If no sort orders are specified, the results are returned in the order they are retrieved from Datastore.

Note: Because of the way Datastore executes queries, if a query specifies inequality filters on a property and sort orders on other properties, the property used in the inequality filters must be ordered before the other properties.

Indexes

Every Datastore query computes its results using one or more indexes, which contain entity keys in a sequence specified by the index's properties and, optionally, the entity's ancestors. The indexes are updated incrementally to reflect any changes the application makes to its entities, so that the correct results of all queries are available with no further computation needed.

App Engine predefines a simple index on each property of an entity. An App Engine application can define further custom indexes in an index configuration file named datastore-indexes.xml, which is generated in your application's /war/WEB-INF/appengine-generated directory . The development server automatically adds suggestions to this file as it encounters queries that cannot be executed with the existing indexes. You can tune indexes manually by editing the file before uploading the application.

Query interface example

The low-level Java Datastore API provides class Query for constructing queries and the PreparedQuery interface for retrieving entities from Datastore:

DatastoreService datastore = DatastoreServiceFactory.getDatastoreService();

Filter heightMinFilter =
    new FilterPredicate("height", FilterOperator.GREATER_THAN_OR_EQUAL, minHeight);

Filter heightMaxFilter =
    new FilterPredicate("height", FilterOperator.LESS_THAN_OR_EQUAL, maxHeight);

// Use CompositeFilter to combine multiple filters
CompositeFilter heightRangeFilter =
    CompositeFilterOperator.and(heightMinFilter, heightMaxFilter);

// Use class Query to assemble a query
Query q = new Query("Person").setFilter(heightRangeFilter);

// Use PreparedQuery interface to retrieve results
PreparedQuery pq = datastore.prepare(q);

for (Entity result : pq.asIterable()) {
  String firstName = (String) result.getProperty("firstName");
  String lastName = (String) result.getProperty("lastName");
  Long height = (Long) result.getProperty("height");

  out.println(firstName + " " + lastName + ", " + height + " inches tall");
}

Notice the use of FilterPredicate and CompositeFilter to construct filters. If you're setting only one filter on a query, you can just use FilterPredicate by itself:

Filter heightMinFilter =
    new FilterPredicate("height", FilterOperator.GREATER_THAN_OR_EQUAL, minHeight);

Query q = new Query("Person").setFilter(heightMinFilter);

However, if you want to set more than one filter on a query, you must use CompositeFilter, which requires at least two filters. The example above uses the shortcut helper CompositeFilterOperator.and; the following example shows one way of constructing a composite OR filter:

Filter tooShortFilter = new FilterPredicate("height", FilterOperator.LESS_THAN, minHeight);

Filter tooTallFilter = new FilterPredicate("height", FilterOperator.GREATER_THAN, maxHeight);

Filter heightOutOfRangeFilter = CompositeFilterOperator.or(tooShortFilter, tooTallFilter);

Query q = new Query("Person").setFilter(heightOutOfRangeFilter);

What's next?