CODEWITHSUNDEEP
arangodb
augustin-s
augustin-s

Reputation: 683

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

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?

Upvotes: 5

Views: 1348

Answers (2)

raisercostin
raisercostin

Reputation: 9189

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
  }
]

Upvotes: 1

CodeManX
CodeManX

Reputation: 11855

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/

Upvotes: 2

Related Questions