UPDATE

The UPDATE keyword can be used to partially update documents in a collection.

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

The system attributes _id, _key and _rev cannot be updated, _from and _to can.

Syntax

The two syntaxes for an update operation are:

UPDATE document IN collection
UPDATE keyExpression WITH document IN collection

Both variants can optionally end with an OPTIONS { … } clause.

collection must contain the name of the collection in which the documents should be updated. document must be a document that contains the attributes and values to update. Attributes that don’t exist in the stored document yet are added to it. The operation leaves other existing attributes not specified in document untouched. When using the first syntax, document must also contain the _key attribute to identify the document to be updated.

FOR u IN users
  UPDATE { _key: u._key, name: CONCAT(u.firstName, " ", u.lastName) } IN users

The following query is invalid because it does not contain a _key attribute and thus it is not possible to determine the documents to be updated:

FOR u IN users
  UPDATE { name: CONCAT(u.firstName, " ", u.lastName) } IN users

When using the second syntax, keyExpression provides the document identification. This can either be a string (which must then contain the document key) or a document, which must contain a _key attribute.

An object with _id attribute but without _key attribute as well as a document ID as string like "users/john" do not work. However, you can use DOCUMENT(id) to fetch the document via its ID and PARSE_IDENTIFIER(id).key to get the document key as string.

The following queries are equivalent:

FOR u IN users
  UPDATE u._key WITH { name: CONCAT(u.firstName, " ", u.lastName) } IN users

FOR u IN users
  UPDATE { _key: u._key } WITH { name: CONCAT(u.firstName, " ", u.lastName) } IN users

FOR u IN users
  UPDATE u WITH { name: CONCAT(u.firstName, " ", u.lastName) } IN users

An update operation may update arbitrary documents:

FOR i IN 1..1000
  UPDATE CONCAT('test', i) WITH { foobar: true } IN users

The documents it modifies can be in a different collection than the ones produced by a preceding FOR operation:

FOR u IN users
  FILTER u.active == false
  UPDATE u WITH { status: 'inactive' } IN backup

Note how documents are read from the users collection but updated in another collection called backup. Both collections need to use matching document keys for this to work.

Using the current value of a document attribute

The pseudo-variable OLD is not supported inside of WITH clauses (it is available after UPDATE). To access the current attribute value, you can usually refer to a document via the variable of the FOR loop, which is used to iterate over a collection:

FOR doc IN users
  UPDATE doc WITH {
    fullName: CONCAT(doc.firstName, " ", doc.lastName)
  } IN users

If there is no loop, because a single document is updated only, then there might not be a variable like above (doc), which would let you refer to the document which is being updated:

UPDATE "john" WITH { ... } IN users

LET key = PARSE_IDENTIFIER("users/john").key
UPDATE key WITH { ... } IN users

To access the current value in this situation, the document has to be retrieved and stored in a variable first:

LET doc = DOCUMENT("users/john")
UPDATE doc WITH {
  fullName: CONCAT(doc.firstName, " ", doc.lastName)
} IN users

An existing attribute can be modified based on its current value this way, to increment a counter for instance:

UPDATE doc WITH {
  karma: doc.karma + 1
} IN users

If the attribute karma doesn’t exist yet, doc.karma is evaluated to null. The expression null + 1 results in the new attribute karma being set to 1. If the attribute does exist, then it is increased by 1.

Arrays can be mutated too of course:

UPDATE doc WITH {
  hobbies: PUSH(doc.hobbies, "swimming")
} IN users

If the attribute hobbies doesn’t exist yet, it is conveniently initialized as [ "swimming" ] and otherwise extended.

Query options

You can optionally set query options for the UPDATE operation:

UPDATE ... IN users OPTIONS { ... }

ignoreErrors

ignoreErrors can be used to suppress query errors that may occur when trying to update non-existing documents or violating unique key constraints:

FOR i IN 1..1000
  UPDATE {
    _key: CONCAT('test', i)
  } WITH {
    foobar: true
  } IN users OPTIONS { ignoreErrors: true }

You cannot modify the _id, _key, and _rev system attributes, but attempts to change them are ignored and not considered errors.

keepNull

When updating an attribute with a null value, ArangoDB will not remove the attribute from the document but store a null value for it. To get rid of attributes in an update operation, set them to null and provide the keepNull option:

FOR u IN users
  UPDATE u WITH {
    foobar: true,
    notNeeded: null
  } IN users OPTIONS { keepNull: false }

The above query will remove the notNeeded attribute from the documents and update the foobar attribute normally.

mergeObjects

The option mergeObjects controls whether object contents will be merged if an object attribute is present in both the UPDATE query and in the to-be-updated document.

The following query will set the updated document’s name attribute to the exact same value that is specified in the query. This is due to the mergeObjects option being set to false:

FOR u IN users
  UPDATE u WITH {
    name: { first: "foo", middle: "b.", last: "baz" }
  } IN users OPTIONS { mergeObjects: false }

Contrary, the following query will merge the contents of the name attribute in the original document with the value specified in the query:

FOR u IN users
  UPDATE u WITH {
    name: { first: "foo", middle: "b.", last: "baz" }
  } IN users OPTIONS { mergeObjects: true }

Attributes in name that are present in the to-be-updated document but not in the query will now be preserved. Attributes that are present in both will be overwritten with the values specified in the query.

Note: the default value for mergeObjects is true, so there is no need to specify it explicitly.

waitForSync

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

FOR u IN users
  UPDATE u WITH {
    foobar: true
  } IN users OPTIONS { waitForSync: true }

ignoreRevs

In order to not accidentally overwrite documents that have been updated since you last fetched them, you can use the option ignoreRevs to either let ArangoDB compare the _rev value and only succeed if they still match, or let ArangoDB ignore them (default):

FOR i IN 1..1000
  UPDATE { _key: CONCAT('test', i), _rev: "1287623" }
  WITH { foobar: true } IN users
  OPTIONS { ignoreRevs: false }

exclusive

The RocksDB engine does not require collection-level locks. Different write operations on the same collection do not block each other, as long as there are no write-write conflicts on the same documents. From an application development perspective it can be desired to have exclusive write access on collections, to simplify the development. Note that writes do not block reads in RocksDB. Exclusive access can also speed up modification queries, because we avoid conflict checks.

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

FOR doc IN collection
  UPDATE doc 
  WITH { updated: true } IN collection 
  OPTIONS { exclusive: true }

refillIndexCaches

Whether to update existing entries in the in-memory edge cache if edge documents are updated.

UPDATE { _key: "123", _from: "vert/C", _to: "vert/D" } IN edgeColl
  OPTIONS { refillIndexCaches: true }

Returning the modified documents

The modified documents can also be returned by the query. In this case, the UPDATE statement needs to be followed a RETURN statement (intermediate LET statements are allowed, too). These statements can refer to the pseudo-values OLD and NEW. The OLD pseudo-value refers to the document revisions before the update, and NEW refers to document revisions after the update.

Both OLD and NEW will contain all document attributes, even those not specified in the update expression.

UPDATE document IN collection options RETURN OLD
UPDATE document IN collection options RETURN NEW
UPDATE keyExpression WITH document IN collection options RETURN OLD
UPDATE keyExpression WITH document IN collection options RETURN NEW

Following is an example using a variable named previous to capture the original documents before modification. For each modified document, the document key is returned.

FOR u IN users
  UPDATE u WITH { value: "test" }
  IN users 
  LET previous = OLD 
  RETURN previous._key

The following query uses the NEW pseudo-value to return the updated documents, without some of the system attributes:

FOR u IN users
  UPDATE u WITH { value: "test" } 
  IN users
  LET updated = NEW 
  RETURN UNSET(updated, "_key", "_id", "_rev")

It is also possible to return both OLD and NEW:

FOR u IN users
  UPDATE u WITH { value: "test" } 
  IN users
  RETURN { before: OLD, after: NEW }

Transactionality

On a single server, updates are executed transactionally in an all-or-nothing fashion.

If the RocksDB engine is used and intermediate commits are enabled, a query may execute intermediate transaction commits in case the running transaction (AQL query) hits the specified size thresholds. In this case, the query’s operations carried out so far will be committed and not rolled back in case of a later abort/rollback. That behavior can be controlled by adjusting the intermediate commit settings for the RocksDB engine.

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