StructuredQuery

Stay organized with collections Save and categorize content based on your preferences.

A Firestore query.

JSON representation 
{
  "select": {
    object (Projection)
  },
  "from": [
    {
      object (CollectionSelector)
    }
  ],
  "where": {
    object (Filter)
  },
  "orderBy": [
    {
      object (Order)
    }
  ],
  "startAt": {
    object (Cursor)
  },
  "endAt": {
    object (Cursor)
  },
  "offset": integer,
  "limit": integer
}
Fields
select

object (Projection)

The projection to return.
from[]

object (CollectionSelector)

The collections to query.
where

object (Filter)

The filter to apply.
orderBy[]

object (Order)

The order to apply to the query results.

Firestore allows callers to provide a full ordering, a partial ordering, or no ordering at all. In all cases, Firestore guarantees a stable ordering through the following rules:

  • The orderBy is required to reference all fields used with an inequality filter.
  • All fields that are required to be in the orderBy but are not already present are appended in lexicographical ordering of the field name.
  • If an order on __name__ is not specified, it is appended by default.

Fields are appended with the same sort direction as the last order specified, or 'ASCENDING' if no order was specified. For example:

  • ORDER BY a becomes ORDER BY a ASC, __name__ ASC
  • ORDER BY a DESC becomes ORDER BY a DESC, __name__ DESC
  • WHERE a > 1 becomes WHERE a > 1 ORDER BY a ASC, __name__ ASC
  • WHERE __name__ > ... AND a > 1 becomes WHERE __name__ > ... AND a > 1 ORDER BY a ASC, __name__ ASC
startAt

object (Cursor)

A potential prefix of a position in the result set to start the query at.

The ordering of the result set is based on the ORDER BY clause of the original query.

SELECT * FROM k WHERE a = 1 AND b > 2 ORDER BY b ASC, __name__ ASC;

This query's results are ordered by (b ASC, __name__ ASC).

Cursors can reference either the full ordering or a prefix of the location, though it cannot reference more fields than what are in the provided ORDER BY.

Continuing off the example above, attaching the following start cursors will have varying impact:

  • START BEFORE (2, /k/123): start the query right before a = 1 AND b > 2 AND __name__ > /k/123.
  • START AFTER (10): start the query right after a = 1 AND b > 10.

Unlike OFFSET which requires scanning over the first N results to skip, a start cursor allows the query to begin at a logical position. This position is not required to match an actual result, it will scan forward from this position to find the next document.

Requires:

  • The number of values cannot be greater than the number of fields specified in the ORDER BY clause.
endAt

object (Cursor)

A potential prefix of a position in the result set to end the query at.

This is similar to START_AT but with it controlling the end position rather than the start position.

Requires:

  • The number of values cannot be greater than the number of fields specified in the ORDER BY clause.
offset

integer

The number of documents to skip before returning the first result.

This applies after the constraints specified by the WHERE, START AT, & END AT but before the LIMIT clause.

Requires:

  • The value must be greater than or equal to zero if specified.
limit

integer

The maximum number of results to return.

Applies after all other constraints.

Requires:

  • The value must be greater than or equal to zero if specified.

Projection

The projection of document's fields to return.

JSON representation 
{
  "fields": [
    {
      object (FieldReference)
    }
  ]
}
Fields
fields[]

object (FieldReference)

The fields to return.

If empty, all fields are returned. To only return the name of the document, use ['__name__'].

FieldReference

A reference to a field in a document, ex: stats.operations.

JSON representation 
{
  "fieldPath": string
}
Fields
fieldPath

string

The relative path of the document being referenced.

Requires:

CollectionSelector

A selection of a collection, such as messages as m1.

JSON representation 
{
  "collectionId": string,
  "allDescendants": boolean
}
Fields
collectionId

string

The collection ID. When set, selects only collections with this ID.
allDescendants

boolean

When false, selects only collections that are immediate children of the parent specified in the containing RunQueryRequest. When true, selects all descendant collections.

Filter

A filter.

JSON representation 
{

  // Union field filter_type can be only one of the following:
  "compositeFilter": {
    object (CompositeFilter)
  },
  "fieldFilter": {
    object (FieldFilter)
  },
  "unaryFilter": {
    object (UnaryFilter)
  }
  // End of list of possible types for union field filter_type.
}
Fields
Union field filter_type. The type of filter. filter_type can be only one of the following:
compositeFilter

object (CompositeFilter)

A composite filter.
fieldFilter

object (FieldFilter)

A filter on a document field.
unaryFilter

object (UnaryFilter)

A filter that takes exactly one argument.

CompositeFilter

A filter that merges multiple other filters using the given operator.

JSON representation 
{
  "op": enum (Operator),
  "filters": [
    {
      object (Filter)
    }
  ]
}
Fields
op

enum (Operator)

The operator for combining multiple filters.
filters[]

object (Filter)

The list of filters to combine.

Requires:

  • At least one filter is present.

Operator

A composite filter operator.

Enums
OPERATOR_UNSPECIFIED Unspecified. This value must not be used.
AND Documents are required to satisfy all of the combined filters.

FieldFilter

A filter on a specific field.

JSON representation 
{
  "field": {
    object (FieldReference)
  },
  "op": enum (Operator),
  "value": {
    object (Value)
  }
}
Fields
field

object (FieldReference)

The field to filter by.
op

enum (Operator)

The operator to filter by.
value

object (Value)

The value to compare to.

Operator

A field filter operator.

Enums
OPERATOR_UNSPECIFIED Unspecified. This value must not be used.
LESS_THAN

The given field is less than the given value.

Requires:

  • That field come first in orderBy.
LESS_THAN_OR_EQUAL

The given field is less than or equal to the given value.

Requires:

  • That field come first in orderBy.
GREATER_THAN

The given field is greater than the given value.

Requires:

  • That field come first in orderBy.
GREATER_THAN_OR_EQUAL

The given field is greater than or equal to the given value.

Requires:

  • That field come first in orderBy.
EQUAL The given field is equal to the given value.
NOT_EQUAL

The given field is not equal to the given value.

Requires:

  • No other NOT_EQUAL, NOT_IN, IS_NOT_NULL, or IS_NOT_NAN.
  • That field comes first in the orderBy.
ARRAY_CONTAINS The given field is an array that contains the given value.
IN

The given field is equal to at least one value in the given array.

Requires:

  • That value is a non-empty ArrayValue with at most 10 values.
  • No other IN or ARRAY_CONTAINS_ANY or NOT_IN.
ARRAY_CONTAINS_ANY

The given field is an array that contains any of the values in the given array.

Requires:

  • That value is a non-empty ArrayValue with at most 10 values.
  • No other IN or ARRAY_CONTAINS_ANY or NOT_IN.
NOT_IN

The value of the field is not in the given array.

Requires:

  • That value is a non-empty ArrayValue with at most 10 values.
  • No other IN, ARRAY_CONTAINS_ANY, NOT_IN, NOT_EQUAL, IS_NOT_NULL, or IS_NOT_NAN.
  • That field comes first in the orderBy.

UnaryFilter

A filter with a single operand.

JSON representation 
{
  "op": enum (Operator),
  "field": {
    object (FieldReference)
  }
}
Fields
op

enum (Operator)

The unary operator to apply.
field

object (FieldReference)

The field to which to apply the operator.

Operator

A unary operator.

Enums
OPERATOR_UNSPECIFIED Unspecified. This value must not be used.
IS_NAN The given field is equal to NaN.
IS_NULL The given field is equal to NULL.
IS_NOT_NAN

The given field is not equal to NaN.

Requires:

  • No other NOT_EQUAL, NOT_IN, IS_NOT_NULL, or IS_NOT_NAN.
  • That field comes first in the orderBy.
IS_NOT_NULL

The given field is not equal to NULL.

Requires:

  • A single NOT_EQUAL, NOT_IN, IS_NOT_NULL, or IS_NOT_NAN.
  • That field comes first in the orderBy.

Order

An order on a field.

JSON representation 
{
  "field": {
    object (FieldReference)
  },
  "direction": enum (Direction)
}
Fields
field

object (FieldReference)

The field to order by.
direction

enum (Direction)

The direction to order by. Defaults to ASCENDING.

Direction

A sort direction.

Enums
DIRECTION_UNSPECIFIED Unspecified.
ASCENDING Ascending.
DESCENDING Descending.