home shape

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 */
Jan Steemann

Jan Steemann

After more than 30 years of playing around with 8 bit computers, assembler and scripting languages, Jan decided to move on to work in database engineering. Jan is now a senior C/C++ developer with the ArangoDB core team, being there from version 0.1. He is mostly working on performance optimization, storage engines and the querying functionality. He also wrote most of AQL (ArangoDB’s query language).

7 Comments

  1. khurram on April 15, 2014 at 11:53 am

    excellent thread!!
    i need to know that is there any windows based tool for arangoDB?

    • Claudius Weinberger on April 15, 2014 at 3:54 pm

      Sorry, I did not know any windows based tools, but have you tried the admin interface.

  2. khurram on April 16, 2014 at 4:16 pm

    Thank you for your reply. Yes i tried the web interface, but it is not that easy as SQL Management Studio or WorkBench or Navicat. I am desperate to learn “noSQL” and i found by my R&D “ArangoDB” best among “mongoDB”, “Neo4j” etc. Please help me out here. Thank you.

    • Claudius Weinberger on April 17, 2014 at 10:47 am

      I’m so sorry, but at the moment, I didn’t know such a tool.
      What exactly did you miss at the web interface?

      • khurram on April 18, 2014 at 6:36 am

        Thanks for your reply. It is not easy for me to create a database,documents,graph,nodes with the web interface. I want to integrate my recent project with Arango but how can I do it? and there is no help on the internet (as far as I have searched). You are an expert with modelling Data. I am very desperate in learning Arango.

        • Claudius Weinberger on April 18, 2014 at 5:59 pm

          All this things are possible with the web interface.
          If you need i’m so sorry but I can’t help you.

          • khurram on May 3, 2014 at 2:03 pm

            would you please provide me some good learning stuff for ArangoDB? thank you in advance.



Leave a Comment





Get the latest tutorials, blog posts and news: