home shape

AQL: Improved data-modification queries

Data-modification queries were enhanced in ArangoDB 2.4 to be able to also return the inserted, update or removed documents. For example, the following statement inserted a few documents and also returned them with all their attributes:

FOR i IN 1..10
  INSERT { value: i } IN test
  LET inserted = NEW
  RETURN inserted

The syntax for returning documents from data-modification queries only supported the exact above format. Using a LET clause was required, and the RETURN clause was limited to returning the variable introduced by the LET. These syntax restrictions have been lifted in the devel branch, which will become release 2.6 eventually.

The changes make returning values from data-modification statements easier and also more flexible.

Simpler syntax

For example, specifying a LET clause is not required anymore (though still fully supported). Instead, the RETURN clause can directly refer to the NEW pseudo-value, making the query shorter and easier to write:

FOR i IN 1..10
  INSERT { value: i } IN test
  RETURN NEW

Projections

It is now also possible to return a projection 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 just the keys of the inserted documents:

FOR i IN 1..10
  INSERT { value: i } IN test
  RETURN NEW._key

Using OLD and NEW in the same query

In previous versions, UPDATE and REPLACE statements could refer to either the OLD or the NEW pseudo-value, but not to both. 2.6 lifts that restriction, so now these queries can refer to both. One can utilize that to return both the previous and the updated revision:

FOR doc IN test 
  UPDATE doc WITH { value: 42 } IN test
  RETURN { old: OLD, new: NEW }

Calculations with OLD or NEW

It is now also possible to run additional calculations with LET statements between the data-modification part and the final RETURN:

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

Restrictions

Still the following restrictions remain:

  • a data-modification operation can optionally be followed by any number of LET clauses, and a final RETURN clause. No other operations (e.g. FOR, SORT, COLLECT) can be used after a data-modification operation
  • calculations following a data-modification operation must not access data in collections, so using functions such as GRAPH_TRAVERSAL etc. is disallowed.

The improvements are present in the devel branch and can be tested in there from now on. As usual, feedback is welcome!

(Initially posted on Jan’s Blog)

Jan Steemann

Jan Steemann

After more than 30 years of playing around with 8 bit computers, assembler and scripting languages, Jan decided to move on to work in database engineering. Jan is now a senior C/C++ developer with the ArangoDB core team, being there from version 0.1. He is mostly working on performance optimization, storage engines and the querying functionality. He also wrote most of AQL (ArangoDB’s query language).

1 Comment

  1. Diego Guraieb on March 31, 2015 at 9:27 pm

    Great! Are these operations atomic in sharded collections?

Leave a Comment





Get the latest tutorials, blog posts and news: