Skip to main content

Data Queries

Data Access Queries

Retrieving data from the database with C8QL does always include a RETURN operation. It can be used to return a static value, such as a string:

RETURN "Hello C8!"

The query result is always an array of elements, even if a single element was returned and contains a single element in that case: ["Hello C8!"]

The function DOCUMENT() can be called to retrieve a single document via its document handle, for instance:

RETURN DOCUMENT("users/phil")

RETURN is usually accompanied by a FOR loop to iterate over the documents of a collection.

The following query executes the loop body for all documents of a collection called users. Each document is returned unchanged in this example:

FOR doc IN users

Instead of returning the raw doc, one can easily create a projection:

FOR doc IN users
RETURN { user: doc, newAttribute: true }

For every user document, an object with two attributes is returned. The value of the attribute user is set to the content of the user document, and newAttribute is a static attribute with the boolean value true.

Operations like FILTER, SORT and LIMIT can be added to the loop body to narrow and order the result. Instead of above shown call to DOCUMENT(), one can also retrieve the document that describes user phil like so:

FOR doc IN users
FILTER doc._key == "phil"

The document key is used in this example, but any other attribute could equally be used for filtering. Since the document key is guaranteed to be unique, no more than a single document will match this filter. For other attributes this may not be the case.

To return a subset of active users (determined by an attribute called status), sorted by name in ascending order, you can do:

FOR doc IN users
FILTER doc.status == "active"

Operations do not have to occur in a fixed order and that their order can influence the result significantly. Limiting the number of documents before a filter is usually not what you want, because it easily misses a lot of documents that would fulfill the filter criterion, but are ignored because of a premature LIMIT clause. Because of the aforementioned reasons, LIMIT is usually put at the very end, after FILTER, SORT and other operations.

See the High Level Operations chapter for more details.

Data Modification Queries

C8QL supports the following data-modification operations:

  • INSERT: insert new documents into a collection
  • UPDATE: partially update existing documents in a collection
  • REPLACE: completely replace existing documents in a collection
  • REMOVE: remove existing documents from a collection
  • UPSERT: conditionally insert or update documents in a collection

Below you find some simple example queries that use these operations. The operations are detailed in the chapter High Level Operations.

Modifying a single document

Let's start with the basics: INSERT, UPDATE and REMOVE operations on single documents. Here is an example that insert a document in an existing collection users:

firstName: "Anna",
name: "Pavlova",
profession: "artist"
} IN users

You may provide a key for the new document; if not provided, C8 will create one for you.

_key: "GilbertoGil",
firstName: "Gilberto",
name: "Gil",
city: "Fortalezza"
} IN users

As C8 is schema-free, attributes of the documents may vary:

_key: "PhilCarpenter",
firstName: "Phil",
name: "Carpenter",
middleName: "G.",
status: "inactive"
} IN users
_key: "NatachaDeclerck",
firstName: "Natacha",
name: "Declerck",
location: "Antwerp"
} IN users

Update is quite simple. The following C8QL statement will add or change the attributes status and location

UPDATE "PhilCarpenter" WITH {
status: "active",
location: "Beijing"
} IN users

Replace is an alternative to update where all attributes of the document are replaced.

_key: "NatachaDeclerck",
firstName: "Natacha",
name: "Leclerc",
status: "active",
level: "premium"
} IN users

Removing a document if you know its key is simple as well :

REMOVE "GilbertoGil" IN users


REMOVE { _key: "GilbertoGil" } IN users

Modifying multiple documents

Data-modification operations are normally combined with FOR loops to iterate over a given list of documents. They can optionally be combined with FILTER statements and the like.

Let's start with an example that modifies existing documents in a collection users that match some condition:

FOR u IN users
FILTER u.status == "not active"
UPDATE u WITH { status: "inactive" } IN users

Now, let's copy the contents of the collection users into the collection backup:

FOR u IN users
INSERT u IN backup

As a final example, let's find some documents in collection users and remove them from collection backup. The link between the documents in both collections is established via the documents' keys:

FOR u IN users
FILTER u.status == "deleted"
REMOVE u IN backup

Returning documents

Data-modification queries can optionally return documents. In order to reference the inserted, removed or modified documents in a RETURN statement, data-modification statements introduce the OLD and/or NEW pseudo-values:

FOR i IN 1..100
INSERT { value: i } IN test
FOR u IN users
FILTER u.status == "deleted"
REMOVE u IN users
FOR u IN users
FILTER u.status == "not active"
UPDATE u WITH { status: "inactive" } IN users

NEW refers to the inserted or modified document revision, and OLD refers to the document revision before update or removal.

INSERT statements can only refer to the NEW pseudo-value, and REMOVE operations only to OLD. UPDATE, REPLACE and UPSERT can refer to either.

In all cases the full documents will be returned with all their attributes, including the potentially auto-generated attributes such as _id, _key, or _rev and the attributes not specified in the update expression of a partial update.


It is possible to return a projection of the documents in OLD or NEW instead of returning the entire documents. This can be used to reduce the amount of data returned by queries.

For example, the following query will return only the keys of the inserted documents:

FOR i IN 1..100
INSERT { value: i } IN test

Using OLD and NEW in the same query

For UPDATE, REPLACE and UPSERT statements, both OLD and NEW can be used to return the previous revision of a document together with the updated revision:

FOR u IN users
FILTER u.status == "not active"
UPDATE u WITH { status: "inactive" } IN users
RETURN { old: OLD, new: NEW }

Calculations with OLD or NEW

It is also possible to run additional calculations with LET statements between the data-modification part and the final RETURN of a C8QL query.

For example, the following query performs an upsert operation and returns whether an existing document was updated, or a new document was inserted. It does so by checking the OLD variable after the UPSERT and using a LET statement to store a temporary string for the operation type:

UPSERT { name: "test" }
INSERT { name: "test" }
UPDATE { } IN users
LET opType = IS_NULL(OLD) ? "insert" : "update"
RETURN { _key: NEW._key, type: opType }


The name of the modified collection (users and backup in the above cases) must be known to the C8QL executor at query-compile time and cannot change at runtime. Using a bind parameter to specify the collection name is allowed.

Data-modification operations are restricted to one collection at a time. It is not possible to use multiple data-modification operations for the same collection in the same query, or follow up a data-modification operation for a specific collection with a read operation for the same collection. Neither is it possible to follow up any data-modification operation with a traversal query (which may read from arbitrary collections not necessarily known at the start of the traversal).

That means you may not place several REMOVE or UPDATE statements for the same collection into the same query. It is however possible to modify different collections by using multiple data-modification operations for different collections in the same query.

In case you have a query with several places that need to remove documents from the same collection, it is recommended to collect these documents or their keys in an array and have the documents from that array removed using a single REMOVE operation.

Data-modification operations can optionally be followed by LET operations to perform further calculations and a RETURN operation to return data.

Transactional Execution

On a single server, data-modification operations are executed transactionally. If a data-modification operation fails, any changes made by it will be rolled back automatically as if they never happened.

In a cluster, C8QL data-modification queries are currently not executed transactionally. Additionally, update, replace, upsert and remove C8QL queries currently require the _key attribute to be specified for all documents that should be modified or removed, even if a shared key attribute other than _key was chosen for the collection. This restriction may be overcome in a future release of C8.