Iterating over aggregated results with AQL returns null values for expected average/sum values

Question

I would like to execute an AQL query on my arangoDB (2.3.1) instance to compute the delta between two averages (average_value) (with res possibly being another subquery result):

LET last = (FOR r in res
    FILTER DATE_MONTH(r.date) == 1 AND DATE_YEAR(r.date) == 2015
    COLLECT name = r.name INTO g
    RETURN {"name":name,"average_value":AVERAGE(g[*].r[*].value)}
    )
LET current = (FOR r in res
    FILTER DATE_MONTH(r.date) == 2 AND DATE_YEAR(r.date) == 2015
    COLLECT name = r.name INTO g
    RETURN {"name":name,"average_value":AVERAGE(g[*].r[*].value)}
    )
FOR l IN last
    FOR c IN current
         FILTER c.name == l.name
         RETURN {"name":c.name,"delta":c.average_value-l.average_value}

But even with just

FOR l IN last
    RETURN l

I do get the "name" but "average_value" will be null. Is this working as designed or how can I access aggregated values from a subquery?

I am also having the same problem. Is there any workaround for this. — Deepak Agarwal

stj stj · Accepted Answer · 2015-03-04T09:34:55

Without having access to the data, it seems to me that .r[*].value is causing the problem.

Inside the two FOR loops in which r is defined, r is a reference to a single document. Using the expand operator ([*]) on a single document will produce null, because the [*] operator can be used on lists/arrays only.

What you can do instead is to use just .r.value instead of .r[*].value. Then the arangodb database should do what you want.

Iterating over aggregated results with AQL returns null values for expected average/sum values

1 Answers