GQL is a SQL-like language for retrieving entities and keys. The syntax for GQL queries is similar to that of SQL. This page is a reference for using GQL with the Python NDB and DB client libraries.

GQL maps roughly to SQL: You can think of a GQLkind as a SQL table, a GQLentity as a SQL row, and a GQLproperty as a SQL column. However, a SQL row-column lookup is a single value, whereas in GQL a property value can be a list.

GQL Versions

You need different versions of GQL depending on where you run queries. There are two GQL references:

• GQL Reference for Python NDB/DB, for the GQL grammar used in the NDB and DB client libraries (use the reference on this page).

• GQL Reference, for the GQL grammar used in the current Datastore API and in theGoogle Cloud console Datastore Viewer.

Syntax

The GQL syntax for Python NDB/DB can be summarized as follows:

SELECT [DISTINCT] [* | <property list> | __key__]  [FROM <kind>]  [WHERE <condition> [AND <condition> ...]]  [ORDER BY <property> [ASC | DESC] [, <property> [ASC | DESC] ...]]  [LIMIT [<offset>,]<count>]  [OFFSET <offset>]  <property list> := <property> [, <property> ...]  <condition> := <property> {< | <= | > | >= | = | != } <value>  <condition> := <property> IN <list>  <condition> := ANCESTOR IS <entity or key>  <list> := (<value> [, <value> ...]])

As with SQL, GQL keywords are case insensitive. Kind and property names arecase sensitive.

GQL only supportsSELECT statements.

A GQL query returns zero or more entire entities,projected entities,orkeysof the requested kind. Every GQL query always begins withSELECT *,SELECT __key__ or SELECT<property list>, whereproperty is a comma delimited list of one or more entity propertiesto be returned from the query. (A GQL query cannot perform a SQL-like "join"query.)

Tip:SELECT __key__ or SELECT<property list> queries are faster and use less CPUtime thanSELECT * queries.

The optionalDISTINCT^{(experimental)} clausespecifies that only completely unique results will be returned in a result set. This willonly return the first result for entities which have the same values for the propertiesthat are being projected.

The optionalFROM clause limits the result set to those entitiesof the given kind. A query without aFROM clause is called akindless query and can only have aWHERE that specifies a__key__property.

The optionalWHERE clause limits the result set to thoseentities that meet one or more conditions. Each condition compares a propertyof the entity with a value using a comparison operator. If multiple conditionsare given with theAND keyword, then an entity must meet all of theconditions to be returned by the query. GQL does not have anORoperator. However, it does have anIN operator, which provides alimited form ofOR.

TheIN operator compares value of a property to each item in alist. TheIN operator is equivalent to many= queries,one for each value, that are ORed together. An entity whose value for the givenproperty equals any of the values in the list can be returned for the query.

Note: TheIN and!= operatorsuse multiple queries behind the scenes. For example, theINoperator executes a separate underlying datastore query for every item in thelist. The entities returned are a result of the cross-product of all theunderlying datastore queries and are de-duplicated. A maximum of 30 datastorequeries are allowed for any single GQL query.

A condition can also test whether an entity has a given entity as anancestor, using theANCESTOR IS operator. The value is a modelinstance orkeyfor the ancestor entity. For more information on ancestors, seeKeys and Entity Groups.

The left-hand side of a comparison is always a property name. A typicalproperty name consists of alphanumeric characters optionally mixed withunderscores and dots. In other words, they match the regular expression[a-zA-Z0-9_]+(\.[a-zA-Z0-9_]+)*.

Caution: Property namescontaining other printable characters must be quoted with double-quotes. Forexample:"first-name". Spaces or non-printable characters inproperty names are not supported.

The right-hand side of a comparison can be one of the following (asappropriate for the property's data type):

• astr literal, as a single-quoted string. Single-quote characters in the string must be escaped as''. For example:'Joe''s Diner'
• an integer or floating point number literal. For example:42.7
• a Boolean literal, asTRUE orFALSE.
• theNULL literal, which represents the null value (None in Python).
• a datetime, date, or time literal, with either numeric values or a string representation, in the following forms:
  • DATETIME(year,month,day,hour,minute,second)
  • DATETIME('YYYY-MM-DD HH:MM:SS')
  • DATE(year,month,day)
  • DATE('YYYY-MM-DD')
  • TIME(hour,minute,second)
  • TIME('HH:MM:SS')
• an entity key literal, with either astring-encoded key or acomplete path of kinds and key names/IDs:
  • KEY('encoded key')
  • KEY('kind','name'/ID [, 'kind','name'/ID...])
• a User object literal, with the user's email address:
  USER('email-address')
• a GeoPt literal, with the latitude and longitude as floating point values:
  GEOPT(lat,long)
• a bound parameter value. In the query string, positional parameters are referenced by number:title = :1. Keyword parameters are referenced by name:title = :mytitle

Note: conditions of the formproperty = NULLcheck to see whether a null value is explicitly stored in the datastore for that property.This is not the same as checking to see if the entity lacks any value for the property!Datastore queries which refer to a property never return entities which don't have somevalue for that property.

Bound parameters can be bound as positional arguments or keyword argumentspassed tothe GqlQuery constructoror a model class'sgql()method. Property data types that do not have corresponding value literal syntaxmust be specified using parameter binding, including the list data type.Parameter bindings can be re-bound with new values during the lifetime of theGqlQueryinstance (such as to efficiently reuse a query) using thebind()method.

The optionalORDER BY clause indicates that results should bereturned sorted by the given properties, in either ascending (ASC)or descending (DESC) order. TheORDER BY clause canspecify multiple sort orders as a comma-delimited list, evaluated from left toright. If the direction is not specified, it defaults toASC. IfnoORDER BY clause is specified, the order of the results isundefined and may change over time.

An optionalLIMIT clause causes the query to stop returningresults after the first<count> entities. TheLIMIT clause can also include an<offset>, toskip that many results to find the first result to return. An optionalOFFSET clause can specify an<offset>, if noLIMIT clause is present.

Note: Like theoffset parameter for thefetch()method, anOFFSET in a GQL query string does not reduce the numberof entities fetched from the datastore. It only affects which results arereturned by the fetch() method. A query with an offset has performancecharacteristics that correspond linearly with the offset size plus the limitsize.

For information on executing GQL queries, binding parameters, and accessingresults, see theGqlQueryclass, and theModel.gql()class method.

Examples

fromgoogle.appengine.extimportdbclassPerson(db.Model):name=db.StringProperty()age=db.IntegerProperty()# We use a unique username for the Entity's key.amy=Person(key_name='amym',name='Amy',age=48)amy.put()Person(key_name='bettyd',name='Betty',age=42).put()Person(key_name='charliec',name='Charlie',age=32).put()Person(key_name='charliek',name='Charlie',age=29).put()Person(key_name='eedna',name='Edna',age=20).put()Person(key_name='fredm',name='Fred',age=16,parent=amy).put()Person(key_name='georgemichael',name='George').put()

To find all of the entities of thePerson kind whose ages arebetween 18 and 35 (i.e. both Charlies and Edna), use this query:

SELECT*FROMPersonWHEREage>=18ANDage<=35

To find the three entities of thePerson kind whose ages are thegreatest (i.e. Amy, Betty and Charlie), use this query:

SELECT*FROMPersonORDERBYageDESCLIMIT3

To find the entities of thePerson kind whose names are one of"Betty" or "Charlie", use this query:

SELECT*FROMPersonWHEREnameIN('Betty','Charlie')

To return only thename values for eachPerson, usethis query:

SELECTnameFROMPerson

To return only thename values for eachPerson,ordered byage, use this query:

SELECTnameFROMPersonORDERBYage

To find the keys of the entities of thePerson kind that have anage ofNone (i.e.KEY('Person', 'georgemichael')), use thisquery:

SELECT__key__FROMPersonWHEREage=NULL

To find all the entities, regardless of kind, that are in Amy'sentity group(i.e. Amy and Fred), use this query:

SELECT*WHERE__key__HASANCESTORKEY(Person,'Amy')

To match by Key, we can use__key__ on the left hand side of a condition.For example, we can use this to get allPerson entities that havea username that starts with "a".

SELECT*FROMPersonWHERE__key__>=KEY('Person','a')AND__key__<KEY('Person','b')

Note: If you ever build a query with an equality on__key__, consider usingget() instead to fetch theentity directly.