How to group results in ArangoDb into single record?

Question

I have the list of events of certain type, structured on the following example:

{
 createdAt: 123123132,
 type: STARTED,
 metadata: {
     emailAddress: "foo@bar.com"
 }
}

The number of types is predefined (START, STOP, REMOVE...). Users produce one or more events during time.

I want to get the following aggregation:

For each user, calculate the number of events for each type.

My AQL query looks like this:

FOR event IN events
  COLLECT
    email = event.metadata.emailAddress,
    type = event.type WITH COUNT INTO count
  LIMIT 10
  RETURN {
      email,
      t: {type, count}
  }

This produces the following output:

{ email: '_84@example.com', t: { type: 'CREATE', count: 203 } }
{ email: '_84@example.com', t: { type: 'DEPLOY', count: 214 } }
{ email: '_84@example.com', t: { type: 'REMOVE', count: 172 } }
{ email: '_84@example.com', t: { type: 'START', count: 204 } }
{ email: '_84@example.com', t: { type: 'STOP', count: 187 } }
{ email: '_95@example.com', t: { type: 'CREATE', count: 189 } }
{ email: '_95@example.com', t: { type: 'DEPLOY', count: 173 } }
{ email: '_95@example.com', t: { type: 'REMOVE', count: 194 } }
{ email: '_95@example.com', t: { type: 'START', count: 213 } }
{ email: '_95@example.com', t: { type: 'STOP', count: 208 } }
...

i.e. I got a row for each type. But I want results like this:

{ email: foo@bar.com, count1: 203, count2: 214, count3: 172 ...}
{ email: aaa@fff.com, count1: 189, count2: 173, count3: 194 ...}
...

OR

 { email: foo@bar.com, CREATE: 203, DEPLOY: 214, ... }
 ...

i.e. to group again the results.

I also need to sort the results (not the events) by the counts: to return e.g. the top 10 users with max number of CREATE events.

How to do that?

ONE SOLUTION

One solution is here, check the accepted answer for more.

FOR a in (FOR event IN events
  COLLECT
    emailAddress = event.metadata.emailAddress,
    type = event.type WITH COUNT INTO count
  COLLECT email = emailAddress INTO perUser KEEP type, count
  RETURN MERGE(PUSH(perUser[* RETURN {[LOWER(CURRENT.type)]: CURRENT.count}], {email})))
SORT a.create desc
LIMIT 10
RETURN a

Answer 1

You could group by user and event type, then group again by user keeping only the type and already calculated event type counts. In the second aggregation, it is important to know into which groups the events fall to construct the result. An array inline projection can be used for that to keep the query short:

FOR event IN events
  COLLECT
    emailAddress = event.metadata.emailAddress,
    type = event.type WITH COUNT INTO count
  COLLECT email = emailAddress INTO perUser KEEP type, count
    RETURN MERGE(PUSH(perUser[* RETURN {[CURRENT.type]: CURRENT.count}], {email}))

Another way would be to group by user and keep event types, then group the types in a subquery. But it is significantly slower in my test (without any indexes defined at least):

FOR event IN events
  LET type = event.type
  COLLECT
    email = event.metadata.emailAddress INTO groups KEEP type
    LET byType = (
    FOR t IN groups[*].type
        COLLECT t2 = t WITH COUNT INTO count
        RETURN {[t2]: count}
    )
    RETURN MERGE(PUSH(byType, {email}))

Returning the top 10 users with the most CREATE events is much simpler. Filter for CREATE event type, then group by user and count the number of events, sort by this number in descending order and return the first 10 results:

FOR event IN events
    FILTER event.type == "CREATE"
    COLLECT email = event.metadata.emailAddress WITH COUNT INTO count
    SORT count DESC
    LIMIT 10
    RETURN {email, count}

EDIT1: Return one document per user with event types grouped and counted (like in the first query), but capture the MERGE result, sort by the count of one particular event type (here: CREATE) and return the top 10 users for this type. The result is the same as with the solution given in the question. It spares the subquery a la FOR a IN (FOR event IN events ...) ... RETURN a however:

FOR event IN events
  COLLECT
    emailAddress = event.metadata.emailAddress,
    type = event.type WITH COUNT INTO count
  COLLECT email = emailAddress INTO perUser KEEP type, count
  LET ret = MERGE(PUSH(perUser[* RETURN {[CURRENT.type]: CURRENT.count}], {email}))
  SORT ret.CREATE DESC
  LIMIT 10
  RETURN ret

EDIT2: Query to generate example data (requires a collection events to exist):

FOR i IN 1..100
    LET email = CONCAT(RANDOM_TOKEN(RAND()*4+4), "@example.com")
    FOR j IN SPLIT("CREATE,DEPLOY,REMOVE,START,STOP", ",")
        FOR k IN 1..RAND()*150+50
            INSERT {metadata: {emailAddress: email}, type: j} INTO events RETURN NEW