Datastore Queries
A Datastore query retrieves entities from the Datastore that meet a specified set of conditions. The query operates on entities of a given kind ; it can specify filters on the entities' property values, keys, and ancestors, and can return zero or more entities as results. A query can also specify sort orders to sequence the results by their property values. The results include all entities that have at least one value for every property named in the filters and sort orders, and whose property values meet all the specified filter criteria. The query can return entire entities, projected entities , or just entity keys .
A typical query includes the following:
- An entity kind to which the query applies
- Zero or more filters based on the entities' property values, keys, and ancestors
- Zero or more sort orders to sequence the results
Note: To conserve memory and improve performance, a query should, whenever possible, specify a limit on the number of results returned.
Every Datastore query computes its results using one or more indexes , tables containing entities 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 immediately available with no further computation needed.
Note: The index-based query mechanism supports a wide range of queries and is suitable for most applications. However, it does not support some kinds of query common in other database technologies: in particular, joins and aggregate queries aren't supported within the Datastore query engine. See Restrictions on Queries , below, for limitations on Datastore queries.
Contents
- Datastore query interface
- Query structure
- Restrictions on queries
- Retrieving results
- Query cursors
- Data consistency
Datastore query interface
Here's a basic example of issuing a query against the Datastore. It retrieves all people whose height falls inside a given range:
Node.js (JSON)
var heightRangeQuery = {
kinds: [{ name: 'Person' }],
filter: {
// Combine multiple filters by using a compositeFilter.
compositeFilter: {
operator: 'AND',
filters: [{
propertyFilter: {
property: { name: 'height' },
operator: 'GREATER_THAN_OR_EQUAL',
value: { integerValue: minHeight }
}
}, {
propertyFilter: {
property: { name: 'height' },
operator: 'LESS_THAN_OR_EQUAL',
value: { integerValue: maxHeight }
}
}]
},
}
};
datastore.runQuery({
query: heightRangeQuery
}).execute(function(err, result) {
// Iterate over the results.
if (!err && result.batch.entityResults) {
result.batch.entityResults.forEach(function(er) {
var entity = er.entity;
console.log(entity.properties.firstName.stringValue, ' ',
entity.properties.lastName.stringValue, ', ',
entity.properties.height.integerValue, ' inches tall');
});
if (result.batch.moreResults == 'NOT_FINISHED') {
var endCursor = result.batch.endCursor;
heightRangeQuery.startCursor = endCursor;
// Reissue the query to get more results...
}
}
callback(err);
});
Python (Protocol Buffers)
req = datastore.RunQueryRequest()
query = req.query
query.kind.add().name = 'Person'
# Use composite_filter to combine multiple filters
composite_filter = query.filter.composite_filter
composite_filter.operator = datastore.CompositeFilter.AND
height_min_filter = composite_filter.filter.add().property_filter
height_min_filter.property.name = 'height'
height_min_filter.operator = datastore.PropertyFilter.GREATER_THAN_OR_EQUAL
height_min_filter.value.integer_value = min_height
height_max_filter = composite_filter.filter.add().property_filter
height_max_filter.property.name = 'height'
height_max_filter.operator = datastore.PropertyFilter.LESS_THAN_OR_EQUAL
height_max_filter.value.integer_value = max_height
resp = self.datastore.run_query(req)
# Iterate over the results
for entity_result in resp.batch.entity_result:
entity = entity_result.entity
for prop in entity.property:
if prop.name == 'first_name':
first_name = prop.value.string_value
elif prop.name == 'last_name':
last_name = prop.value.string_value
elif prop.name == 'height':
height = prop.value.integer_value
print '%s %s, %d inches tall' % (first_name, last_name, height)
if resp.batch.more_results == datastore.QueryResultBatch.NOT_FINISHED:
end_cursor = resp.batch.end_cursor
query.start_cursor.CopyFrom(end_cursor)
# reissue the query to get more results...
Java (Protocol Buffers)
// import static com.google.api.services.datastore.client.DatastoreHelper.*;
Filter heightMinFilter = makeFilter(
"height", PropertyFilter.Operator.GREATER_THAN_OR_EQUAL, makeValue(minHeight)).build();
Filter heightMaxFilter = makeFilter(
"height", PropertyFilter.Operator.LESS_THAN_OR_EQUAL, makeValue(maxHeight)).build();
// Use makeCompositeFilter() to combine multiple filters
Filter heightRangeFilter = makeFilter(heightMinFilter, heightMaxFilter).build();
// Use Query.Builder to assemble a query
Query.Builder q = Query.newBuilder();
q.addKindBuilder().setName("Person");
q.setFilter(heightRangeFilter).build();
// Assemble a RunQueryRequest
RunQueryRequest request = RunQueryRequest.newBuilder().setQuery(q).build();
RunQueryResponse response = datastore.runQuery(request);
// Iterate over the results
for (EntityResult result : response.getBatch().getEntityResultList()) {
Map<String, Value> props = getPropertyMap(result.getEntity());
String firstName = getString(props.get("firstName"));
String lastName = getString(props.get("lastName"));
Long height = getLong(props.get("height"));
System.out.println(String.format("%s %s, %d inches tall", firstName, lastName, height));
}
if (response.getBatch().getMoreResults() == QueryResultBatch.MoreResultsType.NOT_FINISHED) {
ByteString endCursor = response.getBatch().getEndCursor();
q.setStartCursor(endCursor);
// reissue the query to get more results...
}
Notice how we combine multiple filters into a single composite filter. If you're setting only one filter on a query, you can just put that one filter directly on the query:
Node.js (JSON)
var heightMinQuery = {
kinds: [{ name: 'Person' }],
filter: {
propertyFilter: {
property: { name: 'height' },
operator: 'GREATER_THAN_OR_EQUAL',
value: { integerValue: minHeight }
}
}
};
Python (Protocol Buffers)
query = datastore.Query()
query.kind.add().name = 'Person'
height_min_filter = query.filter.property_filter
height_min_filter.property.name = 'height'
height_min_filter.operator = datastore.PropertyFilter.GREATER_THAN_OR_EQUAL
height_min_filter.value.integer_value = min_height
Java (Protocol Buffers)
// import static com.google.api.services.datastore.client.DatastoreHelper.*;
Filter heightMinFilter = makeFilter(
"height", PropertyFilter.Operator.GREATER_THAN_OR_EQUAL, makeValue(minHeight)).build();
Query.Builder q = Query.newBuilder();
q.addKindBuilder().setName("Person");
q.setFilter(heightMinFilter).build();
The Datastore currently only supports combining filters with the
AND
operator. However it's relatively straightforward to create your own
OR
query by issuing multiple queries and combining the results:
Node.js (JSON)
var async = require('async');
// Define the query for people who are too short.
var heightQuery1 = {
kinds: [{ name: 'Person' }],
filter: {
propertyFilter: {
property: { name: 'height' },
operator: 'LESS_THAN',
value: { integerValue: minHeight }
}
}
};
// Define the query for people who are too tall.
var heightQuery2 = {
kinds: [{ name: 'Person' }],
filter: {
propertyFilter: {
property: { name: 'height' },
operator: 'GREATER_THAN',
value: { integerValue: maxHeight }
}
}
};
async.waterfall([
// Issue both queries in parallel.
function(callback) {
async.parallel([
function(callback) {
datastore.runQuery({ query: heightQuery1 }).execute(callback);
},
function(callback) {
datastore.runQuery({ query: heightQuery2 }).execute(callback);
}], callback);
},
// And assemble the results.
// Both filters are by the same property and that property is not
// multi-value, so we don't need to worry about de-duping results.
function(results, callback) {
var entities = [];
if (results[0][0].batch.entityResults) {
entities.push.apply(entities, results[0][0].batch.entityResults);
}
if (results[1][0].batch.entityResults) {
entities.push.apply(entities, results[1][0].batch.entityResults);
}
callback(null, entities);
}], callback);
Python (Protocol Buffers)
# Get the people who are too short.
req = datastore.RunQueryRequest()
query = req.query
query.kind.add().name = 'Person'
too_short_filter = query.filter.property_filter
too_short_filter.property.name = 'height'
too_short_filter.operator = datastore.PropertyFilter.LESS_THAN
too_short_filter.value.integer_value = min_height
entity_results = []
resp = self.datastore.run_query(req)
entity_results.extend(resp.batch.entity_result)
# Now get the people who are too tall and add them to the result list.
req = datastore.RunQueryRequest()
query = req.query
query.kind.add().name = 'Person'
too_tall_filter = query.filter.property_filter
too_tall_filter.property.name = 'height'
too_tall_filter.operator = datastore.PropertyFilter.GREATER_THAN
too_tall_filter.value.integer_value = max_height
# Both filters are by the same property and that property is not
# multi-value, so we don't need to worry about de-duping results.
resp = self.datastore.run_query(req)
entity_results.extend(resp.batch.entity_result)
Java (Protocol Buffers)
// Get the people who are too short.
Filter tooShortFilter = makeFilter(
"height", PropertyFilter.Operator.LESS_THAN, makeValue(minHeight)).build();
Query.Builder q = Query.newBuilder();
q.addKindBuilder().setName("Person");
q.setFilter(tooShortFilter).build();
RunQueryRequest request = RunQueryRequest.newBuilder().setQuery(q).build();
RunQueryResponse response = datastore.runQuery(request);
List<EntityResult> results =
new ArrayList<EntityResult>(response.getBatch().getEntityResultList());
// Now get the people who are too tall and add them to the result list.
// Both filters are by the same property and that property is not
// multi-value, so we don't need to worry about de-duping results.
Filter tooTallFilter = makeFilter(
"height", PropertyFilter.Operator.GREATER_THAN, makeValue(maxHeight)).build();
q.setFilter(tooTallFilter).build();
request = RunQueryRequest.newBuilder().setQuery(q).build();
results.addAll(datastore.runQuery(request).getBatch().getEntityResultList());
Query structure
A query can specify an entity kind , zero or more filters , and zero or more sort orders .
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
Node.js (JSON)
var heightMinQuery = { kinds: [{ name: 'Person' }], filter: { propertyFilter: { property: { name: 'height' }, operator: 'GREATER_THAN_OR_EQUAL', value: { integerValue: minHeight } } } };
Python (Protocol Buffers)
query = datastore.Query() query.kind.add().name = 'Person' min_height_filter = query.filter.property_filter min_height_filter.property.name = 'height' min_height_filter.operator = datastore.PropertyFilter.GREATER_THAN_OR_EQUAL min_height_filter.value.integer_value = min_height
Java (Protocol Buffers)
Query.Builder q = Query.newBuilder(); q.addKindBuilder().setName("Person"); q.setFilter(makeFilter( "height", PropertyFilter.Operator.GREATER_THAN_OR_EQUAL, makeValue(minHeight)));
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:
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 |
Key filters
To filter on the value of an entity's key, use the special property
__key__
:
Node.js (JSON)
var keyFilterQuery = {
kinds: [{ name: 'Person' }],
filter: {
propertyFilter: {
property: { name: '__key__' },
operator: 'GREATER_THAN',
value: { keyValue: lastSeenKey }
}
}
};
Python (Protocol Buffers)
query = datastore.Query()
query.kind.add().name = 'Person'
key_filter = query.filter.property_filter
key_filter.property.name = '__key__'
key_filter.operator = datastore.PropertyFilter.GREATER_THAN
key_filter.value.key_value.CopyFrom(last_seen_key)
Java (Protocol Buffers)
Query.Builder q = Query.newBuilder();
q.addKindBuilder().setName("Person");
q.setFilter(makeFilter(
"__key__", PropertyFilter.Operator.GREATER_THAN, makeValue(lastSeenKey)));
When comparing for inequality, keys are ordered by the following criteria, in order:
- Ancestor path
- Entity kind
- 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 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:
Node.js (JSON)
var ancestorQuery = {
kinds: [{ name: 'Person' }],
filter: {
propertyFilter: {
property: { name: '__key__' },
operator: 'HAS_ANCESTOR',
value: { keyValue: ancestorKey }
}
}
};
Python (Protocol Buffers)
query = datastore.Query()
query.kind.add().name = 'Person'
key_filter = query.filter.property_filter
key_filter.property.name = '__key__'
key_filter.operator = datastore.PropertyFilter.HAS_ANCESTOR
key_filter.value.key_value.CopyFrom(ancestor_key)
Java (Protocol Buffers)
Query.Builder q = Query.newBuilder();
q.addKindBuilder().setName("Person");
q.setFilter(makeFilter(
"__key__", PropertyFilter.Operator.HAS_ANCESTOR, makeValue(ancestorKey)));
Sort orders
A query sort order specifies
- A property name
- A sort direction (ascending or descending)
For example:
Node.js (JSON)
// Order alphabetically by last name: var ascendingQuery = { kinds: [{ name: 'Person' }], order: [{ property: { name: 'lastName', direction: 'ASCENDING' } }] }; // Order by height, tallest to shortest: var descendingQuery = { kinds: [{ name: 'Person' }], order: [{ property: { name: 'height', direction: 'DESCENDING' } }] };
Python (Protocol Buffers)
# Order alphabetically by last name: last_name_asc = datastore.Query() last_name_asc.kind.add().name = 'Person' order = last_name_asc.order.add() order.property.name = 'last_name' order.direction = datastore.PropertyOrder.ASCENDING # Order by height, tallest to shortest: height_desc = datastore.Query() height_desc.kind.add().name = 'Person' order = height_desc.order.add() order.property.name = 'height' order.direction = datastore.PropertyOrder.DESCENDING
Java (Protocol Buffers)
// Order alphabetically by last name: Query.Builder lastNameAsc = Query.newBuilder(); lastNameAsc.addKindBuilder().setName("Person"); lastNameAsc.addOrder(makeOrder("lastName", PropertyOrder.Direction.ASCENDING)); // Order by height, tallest to shortest: Query.Builder heightDesc = Query.newBuilder(); heightDesc.addKindBuilder().setName("Person"); heightDesc.addOrder(makeOrder("height", PropertyOrder.Direction.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:
Node.js (JSON)
var multiSortedQuery = { kinds: [{ name: 'Person' }], order: [{ property: { name: 'lastName', direction: 'ASCENDING' } }, { property: { name: 'height', direction: 'DESCENDING' } }] };
Python (Protocol Buffers)
multi_sorted = datastore.Query() multi_sorted.kind.add().name = 'Person' order = multi_sorted.order.add() order.property.name = 'last_name' order.direction = datastore.PropertyOrder.ASCENDING order = multi_sorted.order.add() order.property.name = 'height' order.direction = datastore.PropertyOrder.DESCENDING
Java (Protocol Buffers)
Query.Builder q = Query.newBuilder(); q.addKindBuilder().setName("Person"); q.addOrder(makeOrder("lastName", PropertyOrder.Direction.ASCENDING)); q.addOrder(makeOrder("height", PropertyOrder.Direction.DESCENDING));
If no sort orders are specified, the results are returned in the order they are retrieved from the Datastore.
Note: Because of the way the 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.
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 the Datastore. Such
kindless queries
cannot include filters or sort orders on property values. They can, however, filter on entity keys by specifying
__key__
as the property name:
Node.js (JSON)
var kindlessQuery = {
filter: {
propertyFilter: {
property: { name: '__key__' },
operator: 'GREATER_THAN',
value: { keyValue: lastSeenKey }
}
}
};
Python (Protocol Buffers)
query = datastore.Query()
key_filter = query.filter.property_filter
key_filter.property.name = '__key__'
key_filter.operator = datastore.PropertyFilter.GREATER_THAN
key_filter.value.key_value.CopyFrom(last_seen_key)
Java (Protocol Buffers)
Query.Builder q = Query.newBuilder();
q.setFilter(makeFilter(
"__key__", PropertyFilter.Operator.GREATER_THAN, makeValue(lastSeenKey)));
Ancestor queries
A query with an ancestor filter limits its results to the specified entity and its descendants:
Node.js (JSON)
var async = require('async');
var tom = { key: { path: [{ kind: 'Person', name: 'Tom' }] } };
var tomKey = tom.key;
var tomGroup = tom.key.path[0];
async.waterfall([
function(callback) {
var weddingPhoto = {
key: { path: [tomGroup, { kind: 'Photo' }] },
properties: {
imageURL: { stringValue: 'http://domain.com/some/path/to/wedding_photo.jpg' }
}
};
var babyPhoto = {
key: { path: [tomGroup, { kind: 'Photo' }] },
properties: {
imageURL: { stringValue: 'http://domain.com/some/path/to/baby_photo.jpg' }
}
};
var dancePhoto = {
key: { path: [tomGroup, { kind: 'Photo' }] },
properties: {
imageURL: { stringValue: 'http://domain.com/some/path/to/dance_photo.jpg' }
}
};
// Not a child of tom!
var campingPhoto = {
key: { path: [{ kind: 'Photo' }] },
properties: {
imageURL: { stringValue: 'http://domain.com/some/path/to/wedding_photo.jpg' }
}
};
datastore.commit({
mutation: {
insert: [tom],
insertAutoId: [weddingPhoto, babyPhoto, dancePhoto, campingPhoto]
},
mode: 'NON_TRANSACTIONAL'
}).execute(callback);
},
function(result, response, callback) {
// This returns weddingPhto, babyPhoto, and dancePhoto,
// but not campingPhoto, because tom is not an ancestor.
datastore.runQuery({
query: {
kinds: [{ name: 'Photo' }],
filter: {
propertyFilter: {
property: { name: '__key__' },
operator: 'HAS_ANCESTOR',
value: { keyValue: tomKey }
}
}
}
}).execute(callback);
}
], callback);
Python (Protocol Buffers)
req = datastore.CommitRequest()
req.mode = datastore.CommitRequest.NON_TRANSACTIONAL
tom = req.mutation.insert.add()
path_element = tom.key.path_element.add()
path_element.kind = 'Person'
path_element.name = 'Tom'
wedding_photo = req.mutation.insert_auto_id.add()
wedding_photo.key.path_element.extend(tom.key.path_element)
wedding_photo.key.path_element.add().kind = 'Photo'
photo_url_property = wedding_photo.property.add()
photo_url_property.name = 'image_url'
photo_url_property.value.string_value = self.Url('wedding_photo.jpg')
baby_photo = req.mutation.insert_auto_id.add()
baby_photo.key.path_element.extend(tom.key.path_element)
baby_photo.key.path_element.add().kind = 'Photo'
photo_url_property = baby_photo.property.add()
photo_url_property.name = 'image_url'
photo_url_property.value.string_value = self.Url('baby_photo.jpg')
dance_photo = req.mutation.insert_auto_id.add()
dance_photo.key.path_element.extend(tom.key.path_element)
dance_photo.key.path_element.add().kind = 'Photo'
photo_url_property = dance_photo.property.add()
photo_url_property.name = 'image_url'
photo_url_property.value.string_value = self.Url('dance_photo.jpg')
camping_photo = req.mutation.insert_auto_id.add()
camping_photo.key.path_element.add().kind = 'Photo'
photo_url_property = camping_photo.property.add()
photo_url_property.name = 'image_url'
photo_url_property.value.string_value = self.Url('camping_photo.jpg')
self.datastore.commit(req)
req = datastore.RunQueryRequest()
query = req.query
query.kind.add().name = 'Photo'
key_filter = query.filter.property_filter
key_filter.property.name = '__key__'
key_filter.operator = datastore.PropertyFilter.HAS_ANCESTOR
key_filter.value.key_value.CopyFrom(tom.key)
# This returns wedding_photo, baby_photo, and dance_photo, but not
# camping_photo, because tom is not an ancestor.
resp = self.datastore.run_query(req)
results = resp.batch.entity_result
Java (Protocol Buffers)
Entity tom = Entity.newBuilder().setKey(makeKey("Person", "Tom")).build();
Key tomKey = tom.getKey();
Entity.Builder weddingPhoto = Entity.newBuilder().setKey(makeKey(tomKey, "Photo"));
weddingPhoto.addProperty(makeProperty(
"imageURL", makeValue("http://domain.com/some/path/to/wedding_photo.jpg").build()));
Entity.Builder babyPhoto = Entity.newBuilder().setKey(makeKey(tomKey, "Photo"));
babyPhoto.addProperty(makeProperty(
"imageURL", makeValue("http://domain.com/some/path/to/baby_photo.jpg").build()));
Entity.Builder dancePhoto = Entity.newBuilder().setKey(makeKey(tomKey, "Photo"));
dancePhoto.addProperty(makeProperty(
"imageURL", makeValue("http://domain.com/some/path/to/dance_photo.jpg").build()));
// not a child of tom!
Entity.Builder campingPhoto = Entity.newBuilder().setKey(makeKey("Photo"));
campingPhoto.addProperty(makeProperty(
"imageURL", makeValue("http://domain.com/some/path/to/camping_photo.jpg").build()));
CommitRequest commitRequest = CommitRequest.newBuilder()
.setMode(CommitRequest.Mode.NON_TRANSACTIONAL)
.setMutation(Mutation.newBuilder()
.addInsertAutoId(weddingPhoto)
.addInsertAutoId(babyPhoto)
.addInsertAutoId(dancePhoto)
.addInsertAutoId(campingPhoto)
.addInsert(tom))
.build();
datastore.commit(commitRequest);
Query.Builder q = Query.newBuilder();
q.addKindBuilder().setName("Photo");
q.setFilter(makeFilter(
"__key__", PropertyFilter.Operator.HAS_ANCESTOR, makeValue(tomKey)));
RunQueryRequest request = RunQueryRequest.newBuilder().setQuery(q).build();
RunQueryResponse response = datastore.runQuery(request);
// This returns weddingPhto, babyPhoto, and dancePhoto,
// but not campingPhoto, because tom is not an ancestor.
List<EntityResult> results = response.getBatch().getEntityResultList();