ArangoDB how apply 'Group by' (COLLECT or ...?)

Question

How can I group my data in ArangoDB with AQL? For example, my structure is:

[
    {name: "karl", id: "1", timestamp: "11112"},
    {name: "migele", id": "2", timestamp: "11114"},
    {name: "martina", id": "2", timestamp: "11116"},
    {name: "olivia", id": "3", timestamp: "11118"},
    {name: "sasha", id": "4", timestamp: "111120"},
]

I want to receive the data with a unique ID and actual timestamp:

{
    karl,
    martina (because martina timestamp > migele timestamp and his ids is equals),
    olivia,
    sasha
}

Sibbie

Answer 1

To group, you can use COLLECT:

FOR doc IN collection
  COLLECT id = doc.id INTO g
  RETURN { id: id, docs: LENGTH(g) }

This will provide a list with unique ids, and for each unique id you will receive the number of documents with the id.

To now get the document with in each group with the highest value in timestamp, you first need to sort each group by timestamp:

FOR doc IN collection
  COLLECT id = doc.id INTO g 
  LET names = (FOR value IN g[*].doc SORT value.timestamp DESC RETURN value.name) 
  RETURN names

Finally, to receive just the member with the highest timestamp value, use names[0] (and you can also apply a LIMIT before because you'll only be interested in the first item):

FOR doc IN collection
  COLLECT id = doc.id INTO g 
  LET names = (FOR value IN g[*].doc SORT value.timestamp LIMIT 1 DESC RETURN value.name) 
  RETURN names[0]

Oystercatcher