SQL / AQL - Comparison
The ArangoDB Query Language (AQL) is similar to the Structured Query Language (SQL) in its purpose. Both support reading and modifying collection data, however AQL does not support data definition operations, such as creating and dropping databases, collections and indexes.
Though some of the keywords overlap, AQL syntax differs from SQL. For instance, the SQL WHERE and AQL FILTER clauses are equivalent in that they both define conditions for returning results. But, SQL uses predefined sequence to determine where the WHERE clause must occur in the statement. In AQL, clauses execute from left to right, so the position of a FILTER clause in the query determines its precedence.
Despite such differences, anyone with an SQL background should have no difficulty in learning AQL. If you run into any problems, we have a table below showing SQL functions and commands with their AQL equivalents.
Terminology
Below is a table with the terms of both systems.
| SQL | AQL |
| database | database |
| table | collection |
| row | document |
| column | attribute |
| table joins | collection joins |
| primary key | primary key (automatically present on _key attribute) |
| index | index |
INSERT
The INSERT keyword adds new documents to a collection. It uses the following syntax:
|
1 |
INSERT document INTO collection options |
Inserting a single row / document
SQL:
|
1 2 |
INSERT INTO users (name, gender) VALUES ("John Doe", "m"); |
AQL:
|
1 2 |
INSERT { name: "John Doe", gender: "m" } INTO users |
Inserting multiple rows / documents
SQL:
|
1 2 3 |
INSERT INTO users (name, gender) VALUES ("John Doe", "m"), ("Jane Smith", "f"); |
AQL:
|
1 2 3 4 5 |
FOR user IN [ { name: "John Doe", gender: "m" }, { name: "Jane Smith", gender: "f" } ] INSERT user INTO users |
Inserting rows / documents from a table / collection
SQL:
|
1 2 3 4 |
INSERT INTO backup (uid, name, gender) SELECT uid, name, gender FROM users WHERE active = 1; |
AQL:
|
1 2 3 |
FOR user IN users FILTER user.active == 1 INSERT user INTO backup |
Generating test rows / documents
SQL:
Use scripts or stored procedures or populate from an existing table.
AQL:
|
1 2 3 4 5 6 |
FOR i IN 1..1000 INSERT { name: CONCAT("test", i), gender: (i % 2 == 0 ? "f" : "m") } INTO users |
UPDATE
The UPDATE keyword partially modifies documents in a collection. There are two syntaxes available for this operation:
|
1 2 |
UPDATE document IN collection options UPDATE keyExpression WITH document IN collection options |
Updating a single row / document
SQL:
|
1 2 3 |
UPDATE users SET name = "John Smith" WHERE id = 1; |
AQL:
|
1 2 3 |
UPDATE { _key: "1" } WITH { name: "John Smith" } IN users |
Adding a new column / attribute with a default value
SQL:
|
1 2 3 |
ALTER TABLE users ADD COLUMN numberOfLogins INTEGER NOT NULL default 0; |
AQL:
|
1 2 3 |
FOR user IN users UPDATE user WITH { numberOfLogins: 0 } IN users |
Adding a new column / attribute with a calculated value
SQL:
|
1 2 3 4 5 6 7 8 9 |
ALTER TABLE users ADD COLUMN numberOfLogins INTEGER NOT NULL default 0; UPDATE users SET numberOfLogins = ( SELECT COUNT(*) FROM logins WHERE user = users.id ) WHERE active = 1; |
AQL:
|
1 2 3 4 5 6 7 8 9 10 11 |
FOR user IN users FILTER user.active == 1 UPDATE user WITH { numberOfLogins: LENGTH( FOR login IN logins FILTER login.user == user._key COLLECT WITH COUNT INTO numLogins RETURN numLogins ) } IN users |
Adding optional columns / attributes
SQL: *
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
ALTER TABLE users ADD COLUMN isImportantUser INTEGER default NULL, ADD COLUMN dateBecameImportant INTEGER default NULL; UPDATE users SET isImportantUser = 1, dateBecameImportant = UNIX_TIMESTAMP() WHERE isImportantUser IS NULL AND ( SELECT COUNT(*) FROM logins WHERE user = user.id ) > 50; |
*Not directly possible, must set column to default value (e.g. NULL) for rows that do not qualify.
AQL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
LET date = DATE_NOW() FOR user IN users FILTER user.isImportantUser == null LET numberOfLogins = ( FOR login IN logins FILTER login.user == user._key COLLECT WITH COUNT INTO numLogins RETURN numLogins ) FILTER numberOfLogins > 50 UPDATE user WITH { isImportantUser: 1, dateBecameImportant: date } IN users |
Removing a column / attribute
SQL:
|
1 2 |
ALTER TABLE users DROP COLUMN numberOfLogins; |
AQL:
|
1 2 3 4 |
FOR user IN users UPDATE user WITH { numberOfLogins: null } IN users OPTIONS { keepNull: false } |
Removing a column / attribute only for some rows / documents
SQL: *
|
1 2 3 4 |
UPDATE users SET isImportantUser = NULL, dateBecameImportant = NULL WHERE isImportantUser = 1 AND active = 0; |
*Not directly possible, must set column to default value (e.g. NULL) for rows that qualify.
AQL:
|
1 2 3 4 5 6 7 8 9 10 |
FOR user IN users FILTER user.isImportantUser == 1 AND user.active == 0 UPDATE user WITH { isImportantUser: null, dateBecameImportant: null } IN users OPTIONS { keepNull: false } |
REPLACE
The REPLACE keyword completely modifies documents in a collection. There are two syntaxes available for this operation:
|
1 2 |
REPLACE document IN collection options REPLACE keyExpression WITH document IN collection options |
Replacing a single row / document
SQL:
|
1 2 3 |
REPLACE INTO users (name, gender) VALUES ("Jane Smith", "f") WHERE id = 1; |
AQL:
|
1 2 3 4 5 6 |
REPLACE { _key: "1" } WITH { name: "Jane Smith", gender: "f" } IN users |
Replacing multiple rows / documents in a table / collection
SQL:
|
1 2 |
REPLACE INTO users (name, gender) SELECT name, gender FROM backup |
AQL:
|
1 2 3 4 5 6 7 |
FOR user IN backup REPLACE user WITH { name: backup.name, gender: backup.gender } IN users |
DELETE / REMOVE
SQL uses DELETE statements to remove rows from a table. In AQL, the REMOVE keyword allows you to remove documents from a collection.
Deleting a single row / document
SQL:
|
1 2 |
DELETE FROM users WHERE id = 1; |
AQL:
|
1 2 |
REMOVE { _key:"1" } IN users |
Deleting multiple rows / documents
SQL:
|
1 2 |
DELETE FROM users WHERE active = 1; |
AQL:
|
1 2 3 |
FOR user IN users FILTER user.active == 1 REMOVE user IN users |
Queries
Selecting all rows / documents from a table / collection, with all columns / attributes
SQL:
|
1 2 |
SELECT * FROM users; |
AQL:
|
1 2 |
FOR user IN users RETURN user |
Filtering rows / documents from a table / collection, with projection
SQL:
|
1 2 3 |
SELECT CONCAT(firstName, " ", lastName) AS name, gender FROM users WHERE active = 1; |
AQL:
|
1 2 3 4 5 6 7 |
FOR user IN users FILTER user.active == 1 RETURN { name: CONCAT(user.firstName, " ", user.lastName), gender: user.gender } |
Sorting rows / documents from a table / collection
SQL:
|
1 2 3 |
SELECT * FROM users WHERE active = 1 ORDER BY name, gender; |
AQL:
|
1 2 3 4 |
FOR user IN users FILTER user.active == 1 SORT user.name, user.gender RETURN user |
AGGREGATION
There are a series of functions and clauses in both SQL and AQL to group or further refine the result-set to get the information you need. For instance, counting documents, finding the smallest or largest value, and so on.
Counting rows / documents in a table / collection
Both SQL and AQL can count the rows or documents in the result-set and tell you how many it finds. AQL manages counts using the WITH keyword to count the documents into a return variable.
SQL:
|
1 2 3 |
SELECT gender, COUNT(*) AS number FROM users WHERE active = 1 GROUP BY gender; |
AQL:
|
1 2 3 4 5 6 7 8 |
FOR user IN users FILTER user.active == 1 COLLECT gender = user.gender WITH COUNT INTO number RETURN { gender: gender, number: number } |
Grouping rows / documents in a table / collection
In SQL, the GROUP BY clauses collects the result-set according to the given column. AQL replaces this with the COLLECT keyword.
SQL:
|
1 2 3 4 5 6 7 |
SELECT YEAR(dateRegister) AS year, MONTH(dateRegister) AS month, COUNT(*) AS number FROM users WHERE active = 1 GROUP BY year, month HAVING number > 20; |
AQL:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
FOR user IN users FILTER user.active == 1 COLLECT year = DATE_YEAR(user.dateRegistered), month = DATE_MONTH(user.dateRegistered) WITH COUNT INTO number FILTER number > 20 RETURN { year: year, month: month, number: number } |
Minimum, maximum calculation of rows / documents in a table / collection
Both SQL and AQL use functions to find the minimum and maximum values for a given field. In AQL, it’s handled with the COLLECT keyword.
SQL:
|
1 2 3 4 |
SELECT MIN(dateRegistered) AS minDate, MAX(dateRegistered) AS maxDate FROM users WHERE active = 1; |
AQL:
|
1 2 3 4 5 6 |
FOR user IN users FILTER user.active == 1 COLLECT AGGREGATE minDate = MIN(user.dateRegistered), maxDate = MAX(user.dateRegistered) RETURN { minDate, maxDate } |
Building horizontal lists
SQL: *
|
1 2 3 4 |
SELECT gender, GROUP_CONCAT(id) AS userIds FROM users WHERE active = 1 GROUP BY gender; |
*Not really applicable – use either a concatenated string column or a special datatype (non-portable).
AQL:
|
1 2 3 4 5 6 7 8 |
FOR user IN users FILTER user.active == 1 COLLECT gender = user.gender INTO usersByGender RETURN { gender: gender, userIds: usersByGender[*].user._key } |
JOINS
Similar to joins in relational databases, ArangoDB has its own implementation of JOINS. Coming from an SQL background, you may find the AQL syntax very different from your expectations.
Inner join
SQL:
|
1 2 3 |
SELECT * FROM users INNER JOIN friends ON (friends.user = users.id); |
AQL:
An inner join can be expressed easily in AQL by nesting FOR loops and using FILTER statements:
|
1 2 3 4 |
FOR user IN users FOR friend IN friends FILTER friend.user == user._key RETURN MERGE(user, friend) |
Note: in AQL the preferred way is to return the document parts from the different collections in individual sub-attributes to avoid attribute name conflicts, e.g.:
|
1 2 3 4 |
FOR user IN users FOR friend IN friends FILTER friend.user == user._key RETURN { user: user, friend: friend } |
It is also possible to return the matching documents in a horizontal list:
|
1 2 3 4 5 6 7 8 9 |
FOR user IN users RETURN { user: user, friends: ( FOR friend IN friends FILTER friend.user == user._key RETURN friend ) } |
Outer join
SQL:
|
1 2 3 |
SELECT * FROM users LEFT JOIN friends ON (friends.user = users.id); |
AQL:
Outer joins are not directly supported in AQL, but can be implemented using subqueries:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
FOR user IN users LET friends = ( FOR friend IN friends FILTER friend.user == user._key RETURN friend ) FOR friendToJoin IN ( LENGTH(friends) > 0 ? friends : [ { /* no match exists */ } ] ) RETURN { user: user, friend: friend } |
In the main, AQL is a declarative language. Queries express what results you want but not how you want to get there. AQL aims to be human-readable, therefore uses keywords from the English language.
It also aims to be client independent, meaning that the language and syntax are the same for all clients, no matter what programming language the clients use. Additionally, it supports complex query patterns and the various data models ArangoDB offers.
AQL also supports several aggregation and string functions. For more information, see AQL Functions.
6 Comments
AQL also supports
INSERT ... INTOin addition toINSERT ... IN, which is not only grammatically correct, but also even closer to SQL. In fact, IN and INTO can be used interchangeably in UPDATE, REPLACE and REMOVE statements.v2.7 features Object Literal Simplification, which can be used to shorten some of above queries and get them closer to SQL as well.
Thank you .. This article has helped me to understand how AQL queries work in Arangodb
We first had the name AvocadoDB but some other software company complained and we switched to the one and only ArangoDB 🙂
Do you have construct like “case when” in oracle?
I want a row_number() kind of functionality in ArangoDB.
@kk What would you use ROW_NUMBER() for? Simply to enumerate the results? That can be done on client-side very easily. Or to get a subset of results, like with LIMIT and OFFSET in some SQL dialects, e.g. to implement pagination? There’s the LIMIT operation in AQL which let’s you do this, but keep in mind that you should SORT the results or it won’t be stable.