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.
|
1 2 3 4 |
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.
|
1 2 3 4 5 6 7 8 |
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:
|
1 2 |
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:
|
1 2 3 |
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:
|
1 2 3 |
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:
|
1 2 3 4 5 |
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:
|
1 2 3 4 5 6 |
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:
|
1 2 |
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:
|
1 2 |
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:
|
1 2 3 |
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:
|
1 2 3 4 5 6 7 8 |
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:
|
1 2 |
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.
2 Comments
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… 🙂
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
uhere 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
collectionhere can also be a bind parameter.e.g.
FOR doc IN @@collection REMOVE doc IN @@collectionBut 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.