ArangoDB how to apply "Group by" (COLLECT or ...?)

Question

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

[ {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 get data with a unique identifier and an actual timestamp:

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

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 identifiers, and for each unique identifier you will get the number of documents with an identifier.

To get the document 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 get only the member with the highest timestamp value, use names[0] (and you can also apply LIMIT earlier because you are only interested in the first element):

 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]