AQL aggregate functions

[Simon R]

How do you use aggregate functions in AQL?
Something Like:

For u IN users
return MAX(u.age)



What is the best way to get the latest document of a large collection?

[Jan Steemann]

Putting MAX() into the RETURN clause will make MAX() work on a scalar
value (the age), which will not work. This is because MAX() expects a
list as its argument.

It can be used like this:

RETURN MAX(users[*].age)

Of course this will enumerate all documents in the users collection and
put them into a temporary list. MAX() will then pick the maximum value
from the list.
If you can pre-filter the list of users it would be much better.


To return the "latest" document, I assume we can use the document with
the highest revision number in the collection.
This can generally be found with this query:

FOR u IN users SORT u._rev DESC LIMIT 1 RETURN u

However, this won't be efficient as it will enumerate all documents in
the collection, fetch their _rev value and sort by that.
There is currently no special construct to return just the "first" or
the "last" document from a collection.
I already thought about adding such functionality (for some different
purpose though) but there hasn't been time to do this yet.

Please feel free to submit a feature request so the issue won't vanish
from the radars.

Best regards
Jan

> --
> You received this message because you are subscribed to the Google
> Groups "ArangoDB" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to arangodb+u...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.

[Simon R]

Please disregard latest post by me. It was faulty

[Jan Steemann]

No problem. I hope you're getting deterministic results now.


Btw it looks like the problem could have been caused by the double
underscore in the attribute name ("__key") used in the sort clause. As
there is no __key attribute, the result order should have been undefined.

Best regards
Jan

[Nydamgård Grundejerforening]

Thank you Jan for providing a nice example.
Just in case someone else encounter this thread in a search,  its worth to note that _key are treated (maybe even stored?) as a string. Even if keyOption type set to autoincrement when the collection is created. This means that 19 comes before 2.
To make it work as intended it must be converted to a number:

FOR u IN users SORT TO_NUMBER(u._key) DESC LIMIT 1 RETURN u

I guess that makes searching for the latest document a rather heavy task in a large collection. But it works.

[Jan Steemann]

Exactly. This may be fixed eventually but at the moment it would need to
fetch all keys and sort them just to get the one you're looking for.