home shape

Modifying AQL

ArangoDB comes with a powerful query language, called AQL. It combines all the different aspects in any easy-to-use query language. You can use joins as in SQL or graph queries as in Cypher. However, up to now it only supported read-queries.

FOR u IN users 
  FOR c IN cities 
    FILTER u.zip == c.zip 
    RETURN { 'name': u.name, 'city': c.name }

Allows you to join the name of city a persons lives in. If you want to follow the social graph and mix in the neighbors, simply add a graph query.

FOR u IN users
  FOR c IN cities
    FILTER u.zip == c.zip
    RETURN {
      'name': u.name,
      'city': c.name, 
      'knows': GRAPH_NEIGHBORS('social', u)[*].vertex.name
    }


So far, so good. No media break in queries. However, if you wanted to change data in version 2.1, you needed to first query the data and then switch from AQL to one of the CRUD operations for the collection. Now it is possible to stay within AQL.

Updating documents

To update existing documents, we can either use the UPDATE or the REPLACE operation. UPDATE updates only the specified attributes in the found documents, and REPLACE completely replaces the found documents with the specified values.

We’ll start with an UPDATE query that rewrites the gender attribute in all documents:

FOR u IN users
  UPDATE u WITH { gender: TRANSLATE(u.gender, { m: 'male', f: 'female' }) } IN users

To add new attributes to existing documents, we can also use an UPDATE query. The following query adds an attribute numberOfLogins for all users with status active:

FOR u IN users
  FILTER u.active == true
  UPDATE u WITH { numberOfLogins: 0 } IN users

Existing attributes can also be updated based on their previous value:

FOR u IN users
  FILTER u.active == true
  UPDATE u WITH { numberOfLogins: u.numberOfLogins + 1 } IN users

The above query will only work if there was already a numberOfLogins attribute present in the document. If it is unsure whether there is a numberOfLogins attribute in the document, the increase must be made conditional:

FOR u IN users
  FILTER u.active == true
  UPDATE u WITH { 
    numberOfLogins: HAS(u, 'numberOfLogins') ? u.numberOfLogins + 1 : 1 
  } IN users

Updates of multiple attributes can be combined in a single query:

FOR u IN users
  FILTER u.active == true
  UPDATE u WITH { 
    lastLogin: DATE_NOW(), 
    numberOfLogins: HAS(u, 'numberOfLogins') ? u.numberOfLogins + 1 : 1 
  } IN users

To completely replace existing documents, use the REPLACE operation. The following query replaces all documents in collection backup with the documents found in collection users. Only those documents will be replaced that are present in both collections. Documents are compared using their _key attributes:

FOR u IN users
  REPLACE u IN backup

The above query will fail if there are documents in collection users that are not in collection backup yet. In this case, the query would attempt to replace documents that do not exist. To make the query succeed for such case, use the ignoreErrors query option:

FOR u IN users
  REPLACE u IN backup OPTIONS { ignoreErrors: true }

Removing documents

Removing documents can be achieved with the REMOVE operation. To remove all users within a certain age range, we can use the following query:

FOR u IN users
  FILTER u.active == true && u.age >= 35 && u.age <= 37
  REMOVE u IN users

Creating documents

To create new documents, there is the INSERT operation. It can also be used to generate copies of existing documents from other collections, or to create synthetic documents (e.g. for testing purposes). The following query creates 1000 test users in collection users with some attributes set:

FOR i IN 1..1000
  INSERT { 
    id: 100000 + i, 
    age: 18 + FLOOR(RAND() * 25), 
    name: CONCAT('test', TO_STRING(i)), 
    active: false, 
    gender: i % 2 == 0 ? 'male' : 'female' 
  } IN users

Copying data from one collection into another

To copy data from one collection into another, an INSERT operation can be used:

FOR u IN users
  INSERT u IN backup

This will copy over all documents from collection users into collection backup. Note that both collections must already exist when the query is executed. The query might fail if backup already contains documents, as executing the insert might attempt to insert the same document again. This will trigger a unique key constraint violation. To make such copy operation work in all cases, the target collection can be emptied before using a REMOVE query.

Frank Celler

Frank Celler

Frank is both entrepreneur and backend developer, developing mostly memory databases for two decades. He is the CTO and co-founder of ArangoDB. Try to challenge Frank asking him questions on C, C++ and MRuby. Besides Frank organizes Cologne’s NoSQL group & is an active member of NoSQL community.

2 Comments

  1. Clinton Wood on July 12 2014, at 8:18 am

    Awesome! Now we need some DDL:

    CREATE COLLECTION ‘tmpUsers’
    DROP COLLECTION ‘tmpUsers’

    and/or for example

    FOR i IN 1..1000 INSERT {…} IN tmpUsers OPTIONS { createCollection: true }
    FOR u IN users INSERT u in backup OPTIONS { createCollection: true }

    or something like that… This would require being able to execute more than one AQL statement per query (which I’m not sure can be done?) and to wrap that in a transaction, would be well… 🙂

  2. ArangoDB database on January 10 2018, at 11:21 pm

    Really sorry for the late reply!

    You have to specify the collection, because you can iterate over one collection reading and write to another, e.g.

    “`FOR doc IN peng
    REMOVE doc._key IN pardauz
    “`
    Your example

    “`FOR u IN users
    FILTER u.active == true
    REMOVE u
    “`

    will not work because `u` here can be an arbitrary expression that contains the document key as a string or as an object with the _key attribute, e.g.

    “`REMOVE UNSET(u, [ “_key”, “_id” ]) // no key present, and which collection to delete from?
    REMOVE { _key: “abc” } // key present, but which collection to delete from?
    REMOVE “abc” // key present, but which collection to delete from?
    REMOVE CONCAT(otherVariable, “-“, TO_NUMBER(yetAnotherVariable)) // will create a string value usable as key, but which collection to delete from?
    REMOVE MERGE({ foo : 1, bar : 2 }, { _key: CONCAT(otherVariable, “test”) } // will create an object with a _key attribute, but which collection to delete from?
    REMOVE ZIP(a, b) // totally unclear if the created object will create a _key attribute or not, and which collection to delete from?
    “`
    To make this work unambiguously in all cases and for all types of expressions it is required that the collection name is specified separately, i.e.

    “`REMOVE key-expression IN collection
    “`
    Note that `collection` here can also be a bind parameter.

    e.g. `FOR doc IN @@collection REMOVE doc IN @@collection`

    But the bottom line is that REMOVE works with arbitrary input and not just the input from a FOR loop that happens to be directly in front of it.

    Hope we could answer your question.

Leave a Comment





Get the latest tutorials, blog posts and news: