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 dialectof SQL (Structured Query Language).

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

To get started with AQL, have a look at our detailedcomparison 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 paperSwitching from Relational Databases to ArangoDBon 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, usingSELECT * FROM table, or select a subset of the columns. The list of tablecolumns 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 toreturning 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 muchmore flexibility in the long run. It lets you handle arbitrarily structureddocuments 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 canreturn the whole document, or just parts of it. Given that oneDocument isa 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 andobject manipulations can be done usingAQL functions and operators.