AQL: Querying a nosql database the elegant & comfortable way

Query Language Tags: 6 Comments

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

About Jan Steemann

Jan is a member of ArangoDB's core development team. He is an expert in data modelling with nosql & relational databases and writing high performance web applications. For ArangoDB, he wrote much of AQL (ArangoDB's query language).
  • khurram

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

    • http://www.triagens.de/ Claudius Weinberger

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

  • khurram

    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.

    • http://www.triagens.de/ Claudius Weinberger

      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

        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.

        • http://www.triagens.de/ Claudius Weinberger

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