INSERT

The INSERT keyword can be used to insert new documents into a collection. On a single server, an insert operation is executed transactionally in an all-or-nothing fashion.

For sharded collections, the entire query and/or insert operation may not be transactional, especially if it involves different shards and/or DB-Servers.

Each INSERT operation is restricted to a single collection, and the collection name must not be dynamic. Only a single INSERT statement per collection is allowed per C8QL query, and it cannot be followed by read or write operations that access the same collection, by traversal operations, or C8QL functions that can read documents.

The syntax for an insert operation is:

INSERT document INTO collection [ OPTIONS options ]

note

The IN keyword is allowed in place of INTO and has the same meaning.

collection must contain the name of the collection into which the documents should be inserted. document is the document to be inserted, and it may or may not contain a _key attribute. If no _key attribute is provided, GDN will auto-generate a value for _key value. Inserting a document will also auto-generate a document revision number for the document.

FOR i IN 1..100
  INSERT { value: i } INTO numbers

An insert operation can also be performed without a FOR loop to insert a single document:

INSERT { value: 1 } INTO numbers

When inserting into an edge collection, it is mandatory to specify the attributes _from and _to in document:

FOR u IN users
  FOR p IN products
    FILTER u._key == p.recommendedBy
    INSERT { _from: u._id, _to: p._id } INTO recommendations

Setting query options

The OPTIONS keyword followed by an object with query options can optionally be provided in an INSERT operation.

It can be used to suppress query errors that may occur when violating unique key constraints:

FOR i IN 1..1000
  INSERT {
    _key: CONCAT('test', i),
    name: "test",
    foobar: true
  } INTO users OPTIONS { ignoreErrors: true }

To make sure data are durable when an insert query returns, there is the waitForSync query option:

FOR i IN 1..1000
  INSERT {
    _key: CONCAT('test', i),
    name: "test",
    foobar: true
  } INTO users OPTIONS { waitForSync: true }

If you want to replace existing documents with documents having the same key there is the overwrite query option. This will let you safely replace the documents instead of raising an "unique constraint violated error":

FOR i IN 1..1000
  INSERT {
    _key: CONCAT('test', i),
    name: "test",
    foobar: true
  } INTO users OPTIONS { overwrite: true }

Different write operations on the same collection do not block each other, as long as there are no write-write conficts on the same documents. From an application development perspective it can be desired to have exclusive write access on collections, to simplify the development.

Use the exclusive option to achieve this effect on a per query basis:

FOR doc IN collection
  INSERT { myval: doc.val + 1 } INTO users 
  OPTIONS { exclusive: true }

Returning the inserted documents

The inserted documents can also be returned by the query. In this case, the INSERT statement can be a RETURN statement (intermediate LET statements are allowed, too). To refer to the inserted documents, the INSERT statement introduces a pseudo-value named NEW.

The documents contained in NEW will contain all attributes, even those auto-generated by the database (e.g. _id, _key, _rev).

INSERT document INTO collection RETURN NEW

Following is an example using a variable named inserted to return the inserted documents. For each inserted document, the document key is returned:

FOR i IN 1..100
  INSERT { value: i }
  INTO users
  LET inserted = NEW
  RETURN inserted._key