Use AQL Variables e.g. for counting (LET sum = sum + 1)

Question

According to https://www.arangodb.com/2014/07/13/arangodb-2-2-0-released it shall be possible to use statements like this:

LET sum = 0
FOR v IN values
  SORT v.year
  LET sum = sum + v.value
  RETURN { year: v.year, value: v.value, sum: sum }

I currently use version 2.4 but am not able to use it, e.g. in such a statement:

LET sum = 0
FOR i in memoryColl
    LET sum = sum + 1
    // sum = sum + 1
    RETURN { "i": i, "sum": sum }

I got the error [1511] variable 'sum' is assigned multiple times (while parsing)

Can somebody tell me if such a statemtn should in principle work, and how exactly?

score 2 · Accepted Answer · answered Jan 13 '15 at 16:06

As explained in the upgrading docs for 2.3, it's no longer possible to update variables in queries:

Previous versions of ArangoDB allowed the modification of variables inside AQL queries [...]

While this is admittedly a convenient feature, the new query optimizer design did not allow to keep it.

Additionally, updating variables inside a query would prevent a lot of optimizations to queries that we would like the optimizer to make. Additionally, updating variables in queries that run on different nodes in a cluster would like cause non-deterministic behavior because queries are not executed linearly.

To enumerate documents, you could do

LET range = 0..LENGTH(memoryColl)-1

FOR i IN range
    RETURN {i: i+1, doc: memoryColl[i]}

but it looks like a really bad idea to me. Better return the documents and let the client enumerate them.

If you actually want to count the number of documents, you may use a sub-query:

LET result = (
    FOR doc IN memoryColl
        FILTER True // add some condition here for instance
        RETURN doc
)

RETURN LENGTH(result)

In 2.4, it is also possible to count more efficiently:
http://jsteemann.github.io/blog/2014/12/12/aql-improvements-for-24/

Thanks for your explanation. So, the topic is closed for me. But just as a remark: of course, counting can be done in other ways, too - this example was just to simplify the question. I originally thought about examples where such updateable variables could significantly help to express/shortify the necessary calculations, esp. in combination with filters which stop the calculation when a variable gets a certain value and so on. But its clear to me that this would disable other optimizations and parallelizations, so also your priorities are clear for me. ==> THANKS again for your answer. — augustin-s, Jan 15 '15 at 08:29
You may wanna check on ArangoDB actions / Foxx if you really need such functionality, they allow for custom JavaScript code running in the database - with more or less the same drawbacks regarding performance I worry however. — CodeManX, Jan 15 '15 at 22:18

score 1 · Answer 2 · answered May 11 '20 at 21:27

On arango 3.7 in 2020 you could do something like described here

LET values = [
    { year: 2019, value: 35 },
    { year: 2017, value: 8 },
    { year: 2018, value: 17 },
    { year: 2020, value: 84 }
]

LET sortedValues = (FOR v IN values SORT v.year RETURN v)
FOR i IN 0..LENGTH(sortedValues)-1
    LET v = sortedValues[i]
    LET sum = sortedValues[i].value + SUM(SLICE(sortedValues, 0, i)[*].value)
    RETURN {year:v.year,value:v.value,sum:sum}

This returned

[
  {
    "year": 2017,
    "value": 8,
    "sum": 8
  },
  {
    "year": 2018,
    "value": 17,
    "sum": 25
  },
  {
    "year": 2019,
    "value": 35,
    "sum": 60
  },
  {
    "year": 2020,
    "value": 84,
    "sum": 144
  }
]

Use AQL Variables e.g. for counting (LET sum = sum + 1)

2 Answers2