AQL: Querying a nosql database the elegant & comfortable way

Having a long history with relational databases and having worked for a lot of years with SQL some people find it a bit inconvenient querying nosql databases e.g. via REST. Others have rather complex data models and need nevertheless an elegant and convenient way for querying. And we all love clean and simple interfaces.

ArangoDB comes with a couple of options for querying the data, among offer it implements the “ArangoDB Query Language” (AQL).

AQL is a declarative query language for simple and also very complex queries. Unless like in other nosql databases you can also query across collections, aggregate results, do some geo location stuff and even iterate over graphs.

So if you like the comfort of SQL but also the freedom of a schema free database, AQL is for you.

If you are interested in learning more about the concepts of ArangoDB checkout Jan’s talk and slides.

But let’s stop beating around the bush and rather have a look at specific examples.

Find the 5 regions in state CA with the most inhabitants:

FOR u IN users                                               /* iterate over all documents in collection 'users' */
  FILTER u.contact.address.state == "CA"                     /* filter on state attribute */
  COLLECT region = u.contact.region INTO group               /* group by region attribute */
  SORT LENGTH(group) DESC                                    /* sort by number of matches found, descending */
  LIMIT 0, 5                                                 /* get top 5 */
  RETURN { "region" : region, "count" : LENGTH(group) }      /* return a projection */

Find the other top 5 hobbies of male users that also like running

FOR likes IN (                                               /* iterate over result of subquery */
  FOR u IN users                                             /* iterate over all users */
    FILTER u.gender == "male" && "running" IN u.likes        /* filter on gender & likes contains "running" */
    FOR value IN u.likes                                     /* iterate over user's individual like values */
      FILTER value != "running"                              /* filter out "running" here */
      RETURN value
)
COLLECT what = likes INTO group                              /* group by like name */
SORT LENGTH(group) DESC                                      /* sort by number of matches found, descending */
LIMIT 0, 5                                                   /* get top 5 */
RETURN { "what" : what, "count" : LENGTH(group) }            /* return a projection */

Find the 10 nearest larger airports around Cologne

FOR a IN NEAR(airports, 50.67, 6.9, 200, "distance")         /* iterate over proximity search result */
  FILTER a.type == "large_airport"                           /* filter on airport type */
  SORT a.distance ASC                                        /* sort by distance, ascending */
  LIMIT 0, 10                                                /* get top 10 */
  RETURN { "name" : a.name, "code" : a.iata_code, "country" : a.iso_country, "city" : a.municipality, "distance" : CONCAT(TO_STRING(CEIL(a.distance/1000)), ' km') }

Find some users with their friends.

FOR u IN users                                               /* iterate over all users */
  FILTER u.gender == "female"                                /* filter on gender */
  FILTER u.contact.address.state == "CA"                     /* filter on state */
  LIMIT 0, 5                                                 /* limit the result */
  FOR fr IN (FOR f IN friendships                            /* iterate over friends */
    FILTER f.user == u._id                                   /* of current user */
    RETURN f.friends                                         /* to get the list of friend ids */
  )
  LET friendnames = (
    FOR f IN fr                                              /* loop over list of friend ids */
      FOR u2 IN users                                        /* join with users collection again */
        FILTER u2._id == f                                   /* restrict on user with friend id */
        RETURN u2.name                                       /* return friend name */
  )
  RETURN { "user" : u.name, "friends" : friendnames }        /* return some merged data */