Getting index of the resultset

Question

Is there a way to get the index of the results within an aql query?

Something like FOR user IN Users sort user.age DESC RETURN {id:user._id, order:{index?}}

score 2 · Answer 1 · answered Sep 04 '18 at 16:19

If you want to enumerate the result set and store these numbers in an attribute order, then this is possible with the following AQL query:

LET sorted_ids = (
  FOR user IN Users
    SORT user.age DESC
    RETURN user._key
)
FOR i IN 0..LENGTH(sorted_ids)-1
  UPDATE sorted_ids[i] WITH { order: i+1 } IN Users
  RETURN NEW

A subquery is used to sort users by age and return an array of document keys. Then a loop over a numeric range from the first to the last index of the that array is used to iterate over its elements, which gives you the desired order value (minus 1) as variable i. The current array element is a document key, which is used to update the user document with an order attribute.

Above query can be useful for a one-off computation of an order attribute. If your data changes a lot, then it will quickly become stale however, and you may want to move this to the client-side.

For a related discussion see AQL: Counter / enumerator

itsezc · Answer 2 · 2018-09-04T11:08:53.673

0

If I understand your question correctly - and feel free to correct me, this is what you're looking for:

FOR user IN Users
    SORT user.age DESC
    RETURN {
                id: user._id,
                order: user._key
            }

The _key is the primary key in ArangoDB.

If however, you're looking for example data entered (in chronological order) then you will have to have to set the key on your inserts and/or create a date / time object and filter using that.

Edit:

Upon doing some research, I believe this link might be of use to you for AI the keys: https://www.arangodb.com/2013/03/auto-increment-values-in-arangodb/

edited Sep 04 '18 at 11:08

answered Sep 04 '18 at 11:01

itsezc

692
2
7
20

Actually what I want is to get the rank of the result. Since it's sorted, first one would have rank 1, second rank 2, so on... – Sanka Darshana Sep 04 '18 at 12:42
1

@SankaDarshana Do you want the rank or actually enumerate the results? The ranks for a set of numbers 25, 25 and 47 would be 1, 1 and 3. The dense ranks would be 1, 1 and 2. Enumerated, you would get 1, 2, 3. Enumeration is the least useful in my opinion, because it is an indirect property of the result array. – CodeManX Sep 04 '18 at 13:28
@CoDEmanX Idea is to calculate the order of a results set once and save it for performance reasons. – Sanka Darshana Sep 04 '18 at 13:39

Getting index of the resultset

2 Answers2