Coming from SQL

If you worked with a relational database management system (RDBMS) such as MySQL, MariaDB or PostgreSQL, you will be familiar with its query language, a dialect of SQL (Structured Query Language).

ArangoDB’s query language is called AQL. There are some similarities between both languages despite the different data models of the database systems. The most notable difference is probably the concept of loops in AQL, which makes it feel more like a programming language. It suits the schema-less model more natural and makes the query language very powerful while remaining easy to read and write.

To get started with AQL, have a look at our detailed comparison of SQL and AQL. It will also help you to translate SQL queries to AQL when migrating to ArangoDB.

You may also be interested in the white paper Switching from Relational Databases to ArangoDB on our website!

Basic queries

How do select lists translate to AQL queries?

In traditional SQL you may either fetch all columns of a table row by row, using SELECT * FROM table, or select a subset of the columns. The list of table columns to fetch is commonly called select list:

  1. SELECT columnA, columnB, columnZ FROM table

Since documents aren’t two-dimensional, and neither do you want to be limited to returning two-dimensional lists, the requirements for a query language are higher. AQL is thus a little bit more complex than plain SQL at first, but offers much more flexibility in the long run. It lets you handle arbitrarily structured documents in convenient ways, mostly leaned on the syntax used in JavaScript.

Composing the documents to be returned

The AQL RETURN statement returns one item per document it is handed. You can return the whole document, or just parts of it. Given that oneDocument is a document (retrieved like LET oneDocument = DOCUMENT("myusers/3456789") for instance), it can be returned as-is like this:

  1. RETURN oneDocument
  1. [
  2.     {
  3.         "_id": "myusers/3456789",
  4.         "_key": "3456789",
  5.         "_rev": "14253647",
  6.         "firstName": "John",
  7.         "lastName": "Doe",
  8.         "address": {
  9.             "city": "Gotham",
  10.             "street": "Road To Nowhere 1"
  11.         },
  12.         "hobbies": [
  13.             { "name": "swimming", "howFavorite": 10 },
  14.             { "name": "biking", "howFavorite": 6 },
  15.             { "name": "programming", "howFavorite": 4 }
  16.         ]
  17.     }
  18. ]

Return the hobbies sub-structure only:

  1. RETURN oneDocument.hobbies
  1. [
  2.     [
  3.         { "name": "swimming", "howFavorite": 10 },
  4.         { "name": "biking", "howFavorite": 6 },
  5.         { "name": "programming", "howFavorite": 4 }
  6.     ]
  7. ]

Return the hobbies and the address:

  1. RETURN {
  2.     hobbies: oneDocument.hobbies,
  3.     address: oneDocument.address
  4. }
  1. [
  2.     {
  3.         "hobbies": [
  4.             { "name": "swimming", "howFavorite": 10 },
  5.             { "name": "biking", "howFavorite": 6 },
  6.             { "name": "programming", "howFavorite": 4 }
  7.         ],
  8.         "address": {
  9.             "city": "Gotham",
  10.             "street": "Road To Nowhere 1"
  11.         }
  12.     }
  13. ]

Return the first hobby only:

  1. RETURN oneDocument.hobbies[0].name
  1. [
  2.     "swimming"
  3. ]

Return a list of all hobby strings:

  1. RETURN { hobbies: oneDocument.hobbies[*].name }
  1. [
  2.     { "hobbies": ["swimming", "biking", "programming"] }
  3. ]

More complex array and object manipulations can be done using AQL functions and operators.