Projection Queries
Most Datastore queries return whole entities as their results, but often an application is actually interested in only a few of the entity's properties. Projection queries allow you to query the Datastore for just those specific properties of an entity that you actually need, at lower latency and cost than retrieving the entire entity.
Projection queries are similar to SQL queries of the form
SELECT name, email, phone FROM CUSTOMER
You can use all of the filtering and sorting features available for standard entity queries, subject to the
limitations
described below. The query returns abridged results with only the specified properties (
name
,
email
, and
phone
in the example) populated with values; all other properties have no data.
Contents
- Using projection queries in Datastore
- Grouping
- Limitations on projections
- Projections and multiple-valued properties
- Indexes for projections
Using projection queries in Datastore
Here's an example that demonstrates the construction of a projection query:
Node.js (JSON)
var guestbookKey = { path: [{ kind: 'Guestbook', name: guestbookName }] };
var guestbookQuery = {
kinds: [{ name: 'Greeting' }],
filter: {
propertyFilter: {
property: { name: '__key__' },
operator: 'HAS_ANCESTOR',
value: { keyValue: guestbookKey }
}
},
projection: [{ property: { name: "user" } },
{ property: { name: "date" } }]
};
Python (Protocol Buffers)
guestbook_key = datastore.Key()
path_element = guestbook_key.path_element.add()
path_element.kind = 'Guestbook'
path_element.name = guestbook_name
query = datastore.Query()
query.kind.add().name = 'Greeting'
property_filter = query.filter.property_filter
property_filter.property.name = '__key__'
property_filter.operator = datastore.PropertyFilter.HAS_ANCESTOR
property_filter.value.key_value.CopyFrom(guestbook_key)
# only return the user and date properties
query.projection.add().property.name = 'user'
query.projection.add().property.name = 'date'
Java (Protocol Buffers)
Key guestbookKey = makeKey("Guestbook", guestbookName).build();
Query.Builder proj = Query.newBuilder();
proj.addKindBuilder().setName("Greeting");
// query for all children of guestbookName
proj.setFilter(makeFilter(
"__key__", PropertyFilter.Operator.HAS_ANCESTOR, makeValue(guestbookKey)));
// only return the user and date properties
proj.addProjection(PropertyExpression.newBuilder().setProperty(
PropertyReference.newBuilder().setName("user")));
proj.addProjection(PropertyExpression.newBuilder().setProperty(
PropertyReference.newBuilder().setName("date")));
The following example shows how to process the query's results by iterating through the list of entities returned and casting each property value to the expected type:
Node.js (JSON)
datastore.runQuery({
query: guestbookQuery
}).execute(function(err, result) {
if (!err && result.batch.entityResults) {
result.batch.entityResults.forEach(function(er) {
var entity = er.entity;
// In projected queries, the timestamp_microseconds_value field is
// moved to the integerValue field.
console.log('User: ', entity.properties.user.stringValue,
', Time: ', entity.properties.date.integerValue / 1e6);
});
}
callback(err);
});
Python (Protocol Buffers)
req = datastore.RunQueryRequest()
req.query.CopyFrom(query)
resp = self.datastore.run_query(req)
for entity_result in resp.batch.entity_result:
entity = entity_result.entity
for prop in entity.property:
if prop.name == 'user':
user = prop.value.string_value
elif prop.name == 'date':
# In projected queries, the timestamp_microseconds_value field is
# moved to the integer_value field.
date = time.ctime(prop.value.integer_value / 1e6)
print 'user: %s, date: %s' % (user, date)
Java (Protocol Buffers)
List<EntityResult> results = datastore.runQuery(
RunQueryRequest.newBuilder().setQuery(proj).build()).getBatch().getEntityResultList();
for (EntityResult result : results) {
Map<String, Value> props = getPropertyMap(result.getEntity());
String user = getString(props.get("user"));
// In projected queries, the timestamp_microseconds_value field is
// moved to the integer_value field, which DatastoreHelper knows to check.
Date date = DatastoreHelper.toDate(props.get("date"));
System.out.println(String.format("User: %s, Date: %s", user, date));
}
Grouping
Projection queries can add
group_by
PropertyReferences to ensure that only completely unique results will be returned in a result set. This will only return the first result for entities which have the same values for the
properties that are being projected.
Node.js (JSON)
datastore.runQuery({
query: guestbookQuery
}).execute(function(err, result) {
if (!err && result.batch.entityResults) {
result.batch.entityResults.forEach(function(er) {
var entity = er.entity;
// In projected queries, the timestamp_microseconds_value field is
// moved to the integerValue field.
console.log('User: ', entity.properties.user.stringValue,
', Time: ', entity.properties.date.integerValue / 1e6);
});
}
callback(err);
});
Python (Protocol Buffers)
req = datastore.RunQueryRequest()
req.query.CopyFrom(query)
resp = self.datastore.run_query(req)
for entity_result in resp.batch.entity_result:
entity = entity_result.entity
for prop in entity.property:
if prop.name == 'user':
user = prop.value.string_value
elif prop.name == 'date':
# In projected queries, the timestamp_microseconds_value field is
# moved to the integer_value field.
date = time.ctime(prop.value.integer_value / 1e6)
print 'user: %s, date: %s' % (user, date)
Java (Protocol Buffers)
Query.Builder distinctQuery = Query.newBuilder();
distinctQuery.addKindBuilder().setName("Widget");
// Add projections on properties 'A' and 'B'
distinctQuery.addProjection(PropertyExpression.newBuilder().setProperty(
PropertyReference.newBuilder().setName("A")));
distinctQuery.addProjection(PropertyExpression.newBuilder().setProperty(
PropertyReference.newBuilder().setName("B")));
// We only want entities where 'B' is less than 4 with the result sorted by 'B'
// in descending order followed by 'A' in ascending order.
distinctQuery.setFilter(makeFilter("B", PropertyFilter.Operator.LESS_THAN, makeValue(4)));
distinctQuery.addOrder(makeOrder("B", PropertyOrder.Direction.DESCENDING));
distinctQuery.addOrder(makeOrder("A", PropertyOrder.Direction.ASCENDING));
// We want distinct results so group by both of the properties we're selecting.
distinctQuery.addGroupBy(PropertyReference.newBuilder().setName("B"));
distinctQuery.addGroupBy(PropertyReference.newBuilder().setName("A"));
Limitations on projections
Projection queries are subject to the following limitations:
-
Only indexed properties can be projected.
Projection is not supported for strings that are longer than 500 characters, byte arrays that have more than 500 elements, and other properties explicitly marked as unindexed.
-
The same property cannot be projected more than once.
-
Properties referenced in an equality (
EQUAL
) or membership (IN
) filter cannot be projected.For example,
SELECT A FROM kind WHERE B = 1
is valid (projected property not used in the equality filter), as is
SELECT A FROM kind WHERE A > 1
(not an equality filter), but
SELECT A FROM kind WHERE A = 1
(projected property used in equality filter) is not.
-
Results returned by a projection query should not be saved back to the Datastore.
Because the query returns results that are only partially populated, you should not write them back to the Datastore.
Projections and multiple-valued properties
Projecting a property with multiple values will not populate all values for that property. Instead, a separate entity will be returned for each unique combination of projected values matching the query. For example, suppose you have an entity of kind
Foo
with two multiple-valued properties,
A
and
B
:
entity = Foo(A=[1, 1, 2, 3], B=['x', 'y', 'x'])
Then the projection query
SELECT A, B FROM Foo WHERE A < 3
will return four entities with the following combinations of values:
A
=
1
,
B
=
'x'
A
=
1
,
B
=
'y'
A
=
2
,
B
=
'x'
A
=
2
,
B
=
'y'
Indexes for projections
Projection queries require all properties specified in the projection to be included in a Datastore
index
. The Datastore development server automatically generates the needed indexes for you in the index configuration file,
datastore-indexes-auto.xml
, which is uploaded with your application.
One way to minimize the number of indexes required is to project the same properties consistently, even when not all of them are always needed. For example, these queries require two separate indexes:
SELECT A, B FROM Kind
SELECT A, B, C FROM Kind
However, if you always project properties
A
,
B
, and
C
, even when
C
is not required, only one index will be needed.
Converting an existing query into a projection query may require building a new index if the properties in the projection are not already included in another part of the query. For example, suppose you had an existing query like
SELECT * FROM Kind WHERE A > 1 ORDER BY A, B
which requires the index
Index(Kind, A, B)
Converting this to either of the projection queries
SELECT C FROM Kind WHERE A > 1 ORDER BY A, B
SELECT A, B, C FROM Kind WHERE A > 1 ORDER BY A, B
introduces a new property (
C
) and thus will require building a new index
Index(Kind,
A,
B,
C)
. Note that the projection query
SELECT A, B FROM Kind WHERE A > 1 ORDER BY A, B
would
not
change the required index, since the projected properties
A
and
B
were already included in the existing query.