NDB Queries
An application can use queries to search the Datastore for entities that match specific search criteria called filters.
Overview
An application can use queries to search the Datastore for entities that match specific search criteria called filters. For example, an application that keeps track of several guestbooks could use a query to retrieve messages from one guestbook, ordered by date:
fromgoogle.appengine.extimportndb...classGreeting(ndb.Model):"""Models an individual Guestbook entry with content and date."""content=ndb.StringProperty()date=ndb.DateTimeProperty(auto_now_add=True)@classmethoddefquery_book(cls,ancestor_key):returncls.query(ancestor=ancestor_key).order(-cls.date)...classMainPage(webapp2.RequestHandler):GREETINGS_PER_PAGE=20defget(self):guestbook_name=self.request.get('guestbook_name')ancestor_key=ndb.Key('Book',guestbook_nameor'*notitle*')greetings=Greeting.query_book(ancestor_key).fetch(self.GREETINGS_PER_PAGE)self.response.out.write('<html><body>')forgreetingingreetings:self.response.out.write('<blockquote>%s</blockquote>'%cgi.escape(greeting.content))self.response.out.write('</body></html>')Some queries are more complex than others; the datastore needs pre-built indexes for these. These pre-built indexes are specified in a configuration file,index.yaml. On the development server, if you run a query that needs an index that you haven't specified, the development server automatically adds it to itsindex.yaml. But in your web site, a query that needs a not-yet-specified index fails. Thus, the typical development cycle is to try a new query on the development server and then update the web site to use the automatically-changedindex.yaml. You can updateindex.yaml separately from uploading the application by runninggcloud app deploy index.yaml. If your datastore has many entities, it takes a long time to create a new index for them; in this case, it's wise to update the index definitions before uploading code that uses the new index. You can use the Administration Console to find out when the indexes have finished building.
The App Engine Datastore natively supports filters for exact matches (the == operator) and comparisons (the <, <=, > and >= operators). It supports combining multiple filters using a BooleanAND operation, with some limitations (see below).
In addition to the native operators, the API supports the!= operator, combining groups of filters using the BooleanOR operation, and theIN operation, which test for equality to one of a list of possible values (like Python's 'in' operator). These operations don't map 1:1 to the Datastore's native operations; thus they are a little quirky and slow, relatively. They are implemented using in-memory merging of result streams. Note thatp != v is implemented as "p < v OR p > v". (This matters forrepeated properties.)
Limitations: The Datastore enforces some restrictions on queries. Violating these will cause it to raise exceptions. For example, combining too many filters, using inequalities for multiple properties, or combining an inequality with a sort order on a different property are all currently disallowed. Also filters referencing multiple properties sometimes require secondary indexes to be configured.
Unsupported: The Datastore does not directly support substring matches, case-insensitive matches, or so-called full-text search. There are ways to implement case-insensitive matches and even full-text search using computed properties.
Filtering by Property Values
Recall theAccount class fromNDB Properties:
classAccount(ndb.Model):username=ndb.StringProperty()userid=ndb.IntegerProperty()email=ndb.StringProperty()Usually you don't want to retrieveall entities of a given kind; you want only those with a specific value or range of values for some property.
Property objects overload some operators to return filter expressions that can be used to control a query: for example, to find all Account entities whose userid property has the exact value 42, you can use the expression
query=Account.query(Account.userid==42)(If you are sure that there was just oneAccount with thatuserid, you might prefer to useuserid as a key.Account.get_by_id(...) is faster thanAccount.query(...).get().)
NDB supports these operations:
property ==value
property <value
property <=value
property >value
property >=value
property !=value
property.IN([value1,value2])
To filter for an inequality, you can use syntax like the following:
query=Account.query(Account.userid >=40)This finds all Account entities whoseuserid property isgreater than or equal to 40.
Two of these operations,!= and IN, are implemented as combinations of the others, and are a little quirky as described in!= and IN.
You can specify multiple filters:
query=Account.query(Account.userid >=40,Account.userid <50)This combines the specified filter arguments, returning all Account entities whose userid value is greater than or equal to 40 and less than 50.
Note: As mentioned earlier, the Datastore rejects queries using inequality filtering on more than one property.
Instead of specifying an entire query filter in a single expression, you may find it more convenient to build it up in steps: for example:
query1=Account.query()# Retrieve all Account entititesquery2=query1.filter(Account.userid >=40)# Filter on userid >= 40query3=query2.filter(Account.userid <50)# Filter on userid < 50 tooquery3 is equivalent to thequery variable from the previous example. Note that query objects are immutable, so the construction ofquery2 does not affectquery1 and the construction ofquery3 does not affectquery1 orquery2.
The != and IN Operations
Recall theArticle class fromNDB Properties:
classArticle(ndb.Model):title=ndb.StringProperty()stars=ndb.IntegerProperty()tags=ndb.StringProperty(repeated=True)The!= (not-equal) andIN (membership) operations are implemented by combining other filters using theOR operation. The first of these,
property !=value
is implemented as
(property <value) OR (property >value)
For example,
query=Article.query(Article.tags!='perl')is equivalent to
query=Article.query(ndb.OR(Article.tags <'perl',Article.tags >'perl'))Note: Perhaps surprisingly, this query does not search forArticle entities that don't include 'perl' as a tag! Rather, it finds all entities with at least one tag unequal to 'perl'. For example, the following entity would be included in the results, even though it has 'perl' as one of its tags:
Article(title='Perl + Python = Parrot',stars=5,tags=['python','perl'])Article(title='Introduction to Perl',stars=3,tags=['perl'])There is no way to query for entities that do not include a tag equal to 'perl'.
Similarly, the IN operation
property IN [value1,value2, ...]
which tests for membership in a list of possible values, is implemented as
(property ==value1) OR (property ==value2) OR ...
For example,
query=Article.query(Article.tags.IN(['python','ruby','php']))is equivalent to
query=Article.query(ndb.OR(Article.tags=='python',Article.tags=='ruby',Article.tags=='php'))Note:Queries usingORde-duplicate their results: the result stream doesn'tinclude entity more than once, even if an entity matches two or more subqueries.
Querying for Repeated Properties
TheArticle class defined in the preceding section alsoserves as an example of querying for repeated properties. Notably, a filterlike
Article.tags=='python'uses a single value, even thoughArticle.tags is arepeated property. You cannot compare repeated properties to listobjects (the Datastore won't understand it), and a filter like
Article.tags.IN(['python','ruby','php'])does something completely different from searching forArticle entities whose tags value is the list['python', 'ruby', 'php']:it searches for entities whosetags value (regarded as a list)containsat least one of those values.
Querying for a value ofNone on a repeated property has undefined behavior; don't do that.
Combining AND and OR Operations
You can nestAND andOR operations arbitrarily. For example:
query=Article.query(ndb.AND(Article.tags=='python',ndb.OR(Article.tags.IN(['ruby','jruby']),ndb.AND(Article.tags=='php',Article.tags!='perl'))))However, due toOR's implementation, a query of this form that is too complex might fail with an exception. You are safer if you normalize these filters so there is (at most) a singleOR operation at the top of the expression tree, and a single level ofAND operations below that.
To perform this normalization, you need to remember both your rules of Boolean logic, and how the!= andIN filters are actually implemented:
- Expand the
!=andINoperators to their primitive form, where!=becomes a check for the property being < or > than the value, andINbecomes a check for the property being == to the first value or the second value or...all the way to the last value in the list. - An
ANDwith anORinside it is equivalent to anORof multipleANDs applied to the originalANDoperands, with a singleORoperand substituted for the originalOR. For exampleAND(a, b, OR(c, d))is equivalent toOR(AND(a, b, c), AND(a, b, d)) - An
ANDthat has an operand that is itself anANDoperation can incorporate the operands of the nestedANDinto the enclosingAND. For exampleAND(a, b, AND(c, d))is equivalent toAND(a, b, c, d) - An
ORthat has an operand that is itself anORoperation can incorporate the operands of the nestedORinto the enclosingOR. For exampleOR(a, b, OR(c, d))is equivalent toOR(a, b, c, d)
If we apply these transformations in stages to the example filter, using a simpler notation than Python, you get:
- Using rule #1 on the
INand!=operators:AND(tags == 'python', OR(tags == 'ruby', tags == 'jruby', AND(tags == 'php', OR(tags < 'perl', tags > 'perl'))))
- Using rule #2 on the innermost
ORnested within anAND:AND(tags == 'python', OR(tags == 'ruby', tags == 'jruby', OR(AND(tags == 'php', tags < 'perl'), AND(tags == 'php', tags > 'perl'))))
- Using rule #4 on the
ORnested within anotherOR:AND(tags == 'python', OR(tags == 'ruby', tags == 'jruby', AND(tags == 'php', tags < 'perl'), AND(tags == 'php', tags > 'perl')))
- Using rule #2 on the remaining
ORnested within anAND:OR(AND(tags == 'python', tags == 'ruby'), AND(tags == 'python', tags == 'jruby'), AND(tags == 'python', AND(tags == 'php', tags < 'perl')), AND(tags == 'python', AND(tags == 'php', tags > 'perl')))
- Using rule #3 to collapse the remaining nested
ANDs:OR(AND(tags == 'python', tags == 'ruby'), AND(tags == 'python', tags == 'jruby'), AND(tags == 'python', tags == 'php', tags < 'perl'), AND(tags == 'python', tags == 'php', tags > 'perl'))
Caution:For some filters, this normalization can cause a combinatorialexplosion. Consider theAND of 3ORclauses with 2 basic clauses each.When normalized, this becomes anOR of 8ANDclauses with 3 basic clauses each: that is, 6 terms become 24.
Specifying Sort Orders
You can use theorder() method to specify the order in which a query returns its results. This method takes a list of arguments, each of which is either a property object (to be sorted in ascending order) or its negation (denoting descending order). For example:
query=Greeting.query().order(Greeting.content,-Greeting.date)This retrieves allGreeting entities, sorted by ascending value of theircontent property. Runs of consecutive entities with the same content property will be sorted by descending value of theirdate property. You can use multipleorder() calls to the same effect:
query=Greeting.query().order(Greeting.content).order(-Greeting.date)Note: When combining filterswithorder(), the Datastore rejects certain combinations.In particular, when you use an inequality filter, the first sort order(if any) must specify the same property as the filter.Also, you sometimes need to configure a secondary index.
Ancestor Queries
Ancestor queries allow you to make strongly consistent queries to the datastore, however entities with the same ancestor are limited to 1 write per second. Here's a simple comparison of the tradeoffs and structure between an ancestor and non-ancestor query using customers and their associated purchases in the datastore.
In the following non-ancestor example, there's one entity in the datastore for eachCustomer, and one entity in the datastore for eachPurchase, with aKeyProperty that points to the customer.
classCustomer(ndb.Model):name=ndb.StringProperty()classPurchase(ndb.Model):customer=ndb.KeyProperty(kind=Customer)price=ndb.IntegerProperty()To find all the purchases that belong to the customer, you can use the following query:
purchases=Purchase.query(Purchase.customer==customer_entity.key).fetch()In this case, the datastore offers high write throughput, but only eventual consistency. If a new purchase was added you may get stale data. You can eliminate this behavior using ancestor queries.
For customers and purchases with ancestor queries, you still have the same structure with two separate entities. The customer part is the same. However, when you create purchases, you no longer need to specify theKeyProperty() for purchases anymore. This is because when you use ancestor queries, you call the customer entity's key when you create a purchase entity.
classCustomer(ndb.Model):name=ndb.StringProperty()classPurchase(ndb.Model):price=ndb.IntegerProperty()Each purchase has a key, and the customer has its own key as well. However, each purchase key will have the customer_entity's key embedded in it. Remember, this will be limited to one write per ancestor per second. The following creates an entity with an ancestor:
purchase=Purchase(parent=customer_entity.key)To query for the purchases of a given customer, use the following query.
purchases=Purchase.query(ancestor=customer_entity.key).fetch()Query Attributes
Query objects have the following read-only data attributes:
| Attribute | Type | Default | Description |
|---|---|---|---|
| kind | str | None | Kind name (usually the class name) |
| ancestor | Key | None | Ancestor specified to query |
| filters | FilterNode | None | Filter expression |
| orders | Order | None | Sort orders |
Printing a query object (or callingstr() orrepr() on it)produces a nicely-formatted string representation:
print(Employee.query())# -> Query(kind='Employee')print(Employee.query(ancestor=ndb.Key(Manager,1)))# -> Query(kind='Employee', ancestor=Key('Manager', 1))Filtering for Structured Property Values
A query can filter directly for the field values of structured properties. For example, a query for all contacts with an address whose city is'Amsterdam' would look like
query=Contact.query(Contact.addresses.city=='Amsterdam')If you combine multiple such filters, the filters may matchdifferentAddress sub-entities withinthe same Contact entity.For example:
query=Contact.query(Contact.addresses.city=='Amsterdam',# Beware!Contact.addresses.street=='Spear St')may find contacts with an address whose city is'Amsterdam' and another (different) address whose street is'Spear St'. However, at least for equality filters, you cancreate a query that returns only results with multiple values in asingle sub-entity:
query=Contact.query(Contact.addresses==Address(city='San Francisco',street='Spear St'))If you use this technique, properties of the sub-entity equal toNone are ignored in the query. If a property has a default value, you have to explicitly set it toNone to ignore it in the query, otherwise the query includes a filter requiring that property value to be equal to the default. For example, if theAddress model had a propertycountry withdefault='us', the above example would only return contacts with country equal to'us'; to consider contacts with other country values, you would need to filter forAddress(city='San Francisco', street='Spear St',country=None).
If a sub-entity has any property values equal toNone, they are ignored. Thus, it doesn't make sense to filter for a sub-entity property value ofNone.
Using Properties Named by String
Sometimes you want to filter or order a query based on a property whose name is specified by string. For example, if you let the user enter search queries liketags:python, it would be convenient to somehow turn that into a query like
Article.query(Article."tags"=="python")# does NOT work
If your model is anExpando, then your filter can useGenericProperty, the classExpando uses for dynamic properties:
property_to_query='location'query=FlexEmployee.query(ndb.GenericProperty(property_to_query)=='SF')UsingGenericProperty also works if your model is not anExpando, but if you want to ensure that you are only using defined property names, you can also use the_properties class attribute
query=Article.query(Article._properties[keyword]==value)or usegetattr() to get it from the class:
query=Article.query(getattr(Article,keyword)==value)The difference is thatgetattr() uses the "Python name" of the property while_properties is indexed by the "datastore name" of the property. These only differ when the property was declared with something like
classArticleWithDifferentDatastoreName(ndb.Model):title=ndb.StringProperty('t')Here the Python name istitle but the datastore name ist.
These approaches also work for ordering query results:
expando_query=FlexEmployee.query().order(ndb.GenericProperty('location'))property_query=Article.query().order(Article._properties[keyword])Query Iterators
While a query is in progress, its state is held in aniterator object. (Most applications won't use them directly; it'snormally more straightforward to callfetch(20) thanto manipulate the iterator object.)There are two basic ways to get such an object:
- using Python's built-in
iter()function on aQueryobject - calling the
Queryobject'siter()method
The first supports the use of a Pythonfor loop(which implicitly calls theiter() function)to loop over a query.
forgreetingingreetings:self.response.out.write('<blockquote>%s</blockquote>'%cgi.escape(greeting.content))The second way, using theQuery object'siter() method, allows you to pass options to theiterator to affect its behavior. For example, to use akeys-only query in afor loop, you can write this:
forkeyinquery.iter(keys_only=True):print(key)Query iterators have other useful methods:
| Method | Description |
|---|---|
__iter__() | Part of Python's iterator protocol. |
next() | Returns the next resultor raises the exceptionStopIteration if there is none. |
has_next() | ReturnsTrue if a subsequentnext() call will return a result,False if it will raiseStopIteration.Blocks until the answer to this question is known and buffers the result(if any) until you retrieve it with next(). |
probably_has_next() | Likehas_next(), but uses a faster (and sometimes inaccurate) shortcut.May return a false positive ( True whennext() would actually raiseStopIteration), but never a false negative (False whennext() would actually return a result). |
cursor_before() | Returns a query cursor representing a point just before the last result returned. Raises an exception if no cursor is available (in particular, if the produce_cursors query option was not passed). |
cursor_after() | Returns a query cursor representing a point just after the last result returned. Raises an exception if no cursor is available (in particular, if the produce_cursors query option was not passed). |
index_list() | Returns a list of indexes used by an executed query, including primary, composite, kind, and single-property indexes. |
Query Cursors
Aquery cursor is a small opaque data structure representing a resumption point in a query. This is useful for showing a user a page of results at a time; it's also useful for handling long jobs that might need to stop and resume. A typical way to use them is with a query'sfetch_page() method. It works somewhat likefetch(), but it returns a triple(results, cursor, more). The returnedmore flag indicates that there are probably more results; a UI can use this, for example, to suppress a "Next Page" button or link. To request subsequent pages, pass the cursor returned by onefetch_page() call into the next. ABadArgumentError is raised if you pass in an invalid cursor. Note that the validation only checks whether the value is base64 encoded. You'll have to do any further needed validation.
Thus, to let the user view all entities matching a query, fetching them a page at a time, your code might look like:
fromgoogle.appengine.datastore.datastore_queryimportCursor...classList(webapp2.RequestHandler):GREETINGS_PER_PAGE=10defget(self):"""Handles requests like /list?cursor=1234567."""cursor=Cursor(urlsafe=self.request.get('cursor'))greets,next_cursor,more=Greeting.query().fetch_page(self.GREETINGS_PER_PAGE,start_cursor=cursor)self.response.out.write('<html><body>')forgreetingingreets:self.response.out.write('<blockquote>%s</blockquote>'%cgi.escape(greeting.content))ifmoreandnext_cursor:self.response.out.write('<a href="/list?cursor=%s">More...</a>'%next_cursor.urlsafe())self.response.out.write('</body></html>')Note the use ofurlsafe() andCursor(urlsafe=s) to serialize and deserialize the cursor. This allows you to pass a cursor to a client on the web in the response to one request, and receive it back from the client in a later request.
Note: Thefetch_page() method typically returns a cursor even if there are no more results, but this is not guaranteed: the cursor value returned may beNone. Note also that because themore flag is implemented using the iterator'sprobably_has_next() method, in rare circumstances it may returnTrue even though the next page is empty.
Some NDB queries don't support query cursors, but you can fix them. If a query usesIN,OR, or!=, then the query results won't work with cursorsunless ordered by key. If an application doesn't order the results by key and callsfetch_page(), it gets aBadArgumentError. IfUser.query(User.name.IN(['Joe', 'Jane'])).order(User.name).fetch_page(N) gets an error, change it toUser.query(User.name.IN(['Joe', 'Jane'])).order(User.name, User.key).fetch_page(N)
Instead of "paging" through query results, you can use a query'siter() method to get a cursor at a precise point. To do this, passproduce_cursors=True toiter(); when the iterator is at the right place, call itscursor_after() to get a cursor that's just after that. (Or, similarly, callcursor_before() for a cursor just before.) Note that callingcursor_after() orcursor_before() may make a blocking Datastore call, rerunning part of the query in order to extract a cursor that points to the middle of a batch.
To use a cursor to page backwards through query results, create a reverse query:
# Set up.q=Bar.query()q_forward=q.order(Bar.key)q_reverse=q.order(-Bar.key)# Fetch a page going forward.bars,cursor,more=q_forward.fetch_page(10)# Fetch the same page going backward.r_bars,r_cursor,r_more=q_reverse.fetch_page(10,start_cursor=cursor)Calling a Function for each Entity ("Mapping")
Suppose you need to get theAccount entities corresponding to theMessage entities returned by a query. You could write something like this:
message_account_pairs=[]formessageinmessage_query:key=ndb.Key('Account',message.userid)account=key.get()message_account_pairs.append((message,account))However, this is pretty inefficient: it waits to fetch an entity, then uses the entity; waits for the next entity, uses the entity. There is a lot of waiting time. Another way is to write a callback function that is mapped over the query results:
defcallback(message):key=ndb.Key('Account',message.userid)account=key.get()returnmessage,accountmessage_account_pairs=message_query.map(callback)# Now message_account_pairs is a list of (message, account) tuples.This version will run somewhat faster than the simpleforloop above because some concurrency is possible.However, because theget() call incallback() is still synchronous,the gain is not tremendous.This is a good place to useasynchronous gets.
GQL
GQL is a SQL-like language for retrieving entities or keys from the App Engine Datastore. While GQL's features are different from those of a query language for a traditional relational database, the GQL syntax is similar to that of SQL. The GQL syntax is described in theGQL Reference.
You can use GQL to construct queries. This is similar to creating a query withModel.query(), but uses GQL syntax to define the query filter and order. To use it:
ndb.gql(querystring)returns aQueryobject (the same type as returned byModel.query()). All the usual methods are available on suchQueryobjects:fetch(),map_async(),filter(), etc.Model.gql(querystring)is a shorthand forndb.gql("SELECT * FROMModel " +querystring). Typically,querystring is something like"WHEREprop1 > 0 ANDprop2 = TRUE".- To query models containing structured properties, you can use
foo.barin your GQL syntax to reference subproperties. - GQL supports SQL-like parameter bindings. An application can define a query and then bind values into it:or
query=ndb.gql("SELECT * FROM Article WHERE stars > :1")query2=query.bind(3)query=ndb.gql("SELECT * FROM Article WHERE stars > :1",3)Calling a query's
bind()function returns a new query; it does not change the original. - If your model class overrides the
_get_kind()class method, your GQL query should use the kind returned by that function, not the class name. - If a property in your model overrides its name (e.g.,
foo = StringProperty('bar')) your GQL query should use the overridden property name (in the example,bar).
Always use the parameter-binding feature if some values in your query are user-supplied variables. This avoids attacks based on syntactic hacks.
It is an error to query for a model that hasn't been imported (or, more generally, defined).
It is an error to use a property name that is not defined by the model class unless that model is an Expando.
Specifying a limit or offset to the query'sfetch() overrides the limit or offset set by GQL'sOFFSET andLIMIT clauses. Don't combine GQL'sOFFSET andLIMIT withfetch_page() Note that the 1,000 result maximum imposed by App Engine on queries applies to both offset and limit.
If you are accustomed to SQL, beware of false assumptions when using GQL. GQL is translated to NDB's native query API. This is different from a typical Object-Relational mapper (like SQLAlchemy or Django's database support), where the API calls are translated into SQL before they are transmitted to the database server. GQL does not support Datastore modifications (inserts, deletes or updates); it only supports queries.
Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
Last updated 2025-12-15 UTC.