How to perform complex operations on an AQL query

[Fabien Antoine]

Hi all,

I'm quite new to ArangoDB and I try to find how to write some AQL queries.

Currently I'm trying to compute some count stats from a list of entries.

As an example, take a list of items with this schema:

{
  author: "users/...", // An _id of a user
  kind: 1, // 1 or 2
}

And a list of users with this schema:

{
  username: "...",
  kind: 1, // 1 or 2
}

I want to be able to count:

• How many items are listed,
• How many items are of kind 1 and how many of kind 2,
• How many users (joined as `item author`) are of kind 1 and how many of kind 2

I started with the following query and the first count is easy to get thanks to `COLLECT WITH COUNT`.

However I don't know how to count the others since it introduces a conditional counter.

FOR item IN items

    FILTER item.owner == @user

    LET author = (

        FOR author IN users

            FILTER item.author == author._id

            LIMIT 1

            RETURN author

    )

    COLLECT WITH COUNT INTO total

    RETURN {

        total: total,

        users: {

            kind1: <?>,

            kind2: <?>

        },

        stats: {

            stat1: <?>,

            stat2: <?>

        }

    }

Is it possible to handle this directly into Arango?

Thanks

[Fabien Antoine]

I found a workaround by creating a temporary list, then grouping on it but I'm pretty sure it can be optimised:

LET list = (FOR item IN items

   FILTER item.owner == @user

   LET author = (

       FOR author IN users

           FILTER item.author == author._id

           LIMIT 1

           RETURN author

   )
   RETURN {
      userKind1: (author[0].kind == 1 ? 1 : 0),
      userKind2: (author[0].kind == 2 ? 1 : 0),
      stat1: (item.kind == 1 ? 1 : 0),
      stat2: (item.kind == 2 ? 1 : 0)
   }
)

FOR entry IN list

   COLLECT tmp = entry.nomatter INTO c
   RETURN {

       total: LENGTH(c),

       users: {

           kind1: SUM(c[*].userKind1),

           kind2: SUM(c[*].userKind2)

       },

       stats: {

           stat1: SUM(c[*].stat1),

           stat2: SUM(c[*].stat2)

       }

   }

I can see multiple bad things on this request:

• I need to create a temporary list
• I need to collect on null to group all items of the list.
• I need to use multiple SUM on the same list to calculate all stats.
• Surely more...

It could be great if you have any tips to improve this request!!

Thanks

[sim...@arangodb.com]

Hey Fabien, welcome to ArangoDB. I hope you like it so far!

Based on your description, I created some test data and tried to formulate a query that solves your problem.

It's hopefully what you are looking for, comments in-line and my test data at the end:

// sub-query is required in order to do multiple things with the results
LET kinds = (
    FOR item IN items
        FILTER item.owner == @owner
        LET authorKind = (
            FOR user IN users
                FILTER item.author == user._id // join item <-> user
                LIMIT 1
                RETURN user.kind // no need to return the whole user document
        )
        RETURN {
            itemKind: item.kind, // no need to return the whole item document
            userKind: authorKind[0] // query results are always arrays
        }
)


RETURN {
    total: LENGTH(kinds), // in above sub-query, we return one result per *item*
    users: (
        FOR k IN kinds
            COLLECT kind = k.userKind WITH COUNT INTO kc // count how often every user kind appears
            RETURN { [ CONCAT("kind", kind) ]: kc } // instead of a literal name, compute it dynamically
    ),
    stats: (
        FOR k IN kinds
            COLLECT kind = k.itemKind WITH COUNT INTO kc
            RETURN { [ CONCAT("stat", kind) ]: kc }
            // you could also return a different data structure, with static attribute keys:
            //RETURN {kind: kind, count: kc}
    )
}

Query result for my example data:

[
  {
    "total": 11,

    "stats": [
      {
        "stat1": 7
      },
      {
        "stat2": 4
      }
    ],
    "users": [
      {
        "kind0": 5
      },
      {
        "kind1": 6
      }
    ]
  }
]

Test data (_key and _rev omitted):

[
  [
    { "kind": 1, "username": "Heinz",    "_id": "users/heinz" },
    { "kind": 0, "username": "Jessica",  "_id": "users/jessica" },
    { "kind": 0, "username": "Walter",   "_id": "users/walter" },
    { "kind": 1, "username": "John",     "_id": "users/john" },
    { "kind": 1, "username": "Jessie",   "_id": "users/jessie" },
    { "kind": 2, "username": "Kingsley", "_id": "users/kingsley" },
    { "kind": 1, "username": "Max",      "_id": "users/max" },
    { "kind": 0, "username": "Caroline", "_id": "users/caroline" },
    { "kind": 1, "username": "Anna",     "_id": "users/anna" }
  ]
]

[
  [
    { "kind": 2, "author": "users/john",     "owner": "users/john",     "_id": "items/1020494" },
    { "kind": 1, "author": "users/jessie",   "owner": "users/kingsley", "_id": "items/1020480" },
    { "kind": 2, "author": "users/walter",   "owner": "users/kingsley", "_id": "items/1020474" },
    { "kind": 2, "author": "users/john",     "owner": "users/john",     "_id": "items/1020470" },
    { "kind": 1, "author": "users/jessica",  "owner": "users/kingsley", "_id": "items/1020482" },
    { "kind": 1, "author": "users/jessica",  "owner": "users/john",     "_id": "items/1020490" },
    { "kind": 2, "author": "users/heinz",    "owner": "users/john",     "_id": "items/1020472" },
    { "kind": 1, "author": "users/max",      "owner": "users/kingsley", "_id": "items/1020466" },
    { "kind": 2, "author": "users/jessie",   "owner": "users/john",     "_id": "items/1020496" },
    { "kind": 2, "author": "users/caroline", "owner": "users/kingsley", "_id": "items/1020498" },
    { "kind": 1, "author": "users/anna",     "owner": "users/kingsley", "_id": "items/1020476" },
    { "kind": 1, "author": "users/walter",   "owner": "users/kingsley", "_id": "items/1020478" },
    { "kind": 1, "author": "users/jessica",  "owner": "users/kingsley", "_id": "items/1020468" },
    { "kind": 2, "author": "users/walter",   "owner": "users/john",     "_id": "items/1020460" },
    { "kind": 2, "author": "users/john",     "owner": "users/kingsley", "_id": "items/1020488" },
    { "kind": 2, "author": "users/max",      "owner": "users/john",     "_id": "items/1020486" },
    { "kind": 1, "author": "users/jessie",   "owner": "users/kingsley", "_id": "items/1020464" },
    { "kind": 2, "author": "users/jessie",   "owner": "users/kingsley", "_id": "items/1020500" },
    { "kind": 2, "author": "users/jessica",  "owner": "users/john",     "_id": "items/1020492" },
    { "kind": 2, "author": "users/caroline", "owner": "users/john",     "_id": "items/1020484" }
  ]
]

If there's something you don't understand, or if I did this all wrong, just say a word :)

Best, Simran

[sim...@arangodb.com]

I forgot to mention the bind variable: it was users/kingsley:

{"owner": "users/kingsley"}

If I change that to the only other owner in my test data, users/john, then I get the following result:

[
  {
    "total": 9,

    "users": [
      {
        "kind0": 4
      },
      {
        "kind1": 5
      }
    ],
    "stats": [
      {
        "stat1": 1
      },
      {
        "stat2": 8
      }
    ]
  }
]

[Fabien Antoine]

Hi,

Thanks for your answer!

I successfully implemented it by using some more MERGE calls to get the object structure I wanted.

[Simran Brucherseifer]

You're welcome. I assume you changed it like this?

RETURN {
    total: LENGTH(kinds),
    users: MERGE(

        FOR k IN kinds
            COLLECT kind = k.

userKind WITH COUNT INTO kc
            RETURN { [ CONCAT("kind", kind) ]: kc }
    ),
    stats: MERGE(

        FOR k IN kinds
            COLLECT kind = k.itemKind WITH COUNT INTO kc
            RETURN { [ CONCAT("stat", kind) ]: kc }

    )
}

[Fabien Antoine]

Exactly :)