3

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?

robertosh
  • 93
  • 4

1 Answers1

2

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.

Community
  • 1
  • 1
stj
  • 9,037
  • 19
  • 33
  • even with `.r.value`, the iteration over the aggregated results with `FOR l IN last RETURN l` returns null for average_value. By the way, `RETURN last` will output the average values correctly. It is only with the FOR-LOOP, which will return null values for averages/sums – robertosh Mar 08 '15 at 12:27
  • The modified query (with `.r.value` instead of `.r[*].value`) works fine for me in 2.3 and 2.4. However, I don't have access to the data in your collection so I don't know on what kind of data you run the query on. Here is my setup for which the query returned averages and differences: `db._create("res"); for (i = 0; i < 100; ++i) { db.res.save({ date: (new Date(1420500000000 + 5000000000 * Math.random()).toISOString()), name: "test" + (i % 10), value: Math.random() * 100 }); }` – stj Mar 09 '15 at 13:33
  • @stj it doesn't work for me with r.value. My data structure is : [code] { "inst": 'A', "min": 3, "max": 4, "sum":10 }, { "inst": 'B', "min": 2, "max": 3, "sum":20 } [/code] and the Query is : var query = "LET xx = (FOR m in raw COLLECT mm = m.inst INTO g RETURN {'inst': mm, 'max':max(FLATTEN(g[*].m.max)),'min':min(FLATTEN(g[*].m.min)), 'sum':sum(FLATTEN(g[*].m[*].sum))}) for a in xx return a" – Deepak Agarwal Jun 18 '15 at 19:43
  • I guess all the `*`s in the previous comment got lost. Please note that code fragments should be included in backticks, not `[code]...[/code]`. `*` characters outside code sections will lead to italic or bold formatting and the `*` chars themselves being not printed. – stj Jun 19 '15 at 11:51
  • 1
    Revisiting this issue: I think there is an issue if there's a `COLLECT...INTO` statement producing aggregates in a subquery, which itself is followed by other statements than `RETURN` ,for example `LET values = (FOR ... COLLECT ... INTO ... RETURN ...) FOR v IN values RETURN v`. In this case, the sub-attributes in the result produced by `COLLECT INTO` may have wrong attribute name, so accessing them with their correct attribute name will produce `null`. This was just fixed. The fix will be available in 2.5.6 and 2.6.0 stable. – stj Jun 19 '15 at 12:14
  • Until then, the workaround will be to move the `COLLECT...INTO` out of the subquery, or not follow up the subquery will other statements than `RETURN`. – stj Jun 19 '15 at 12:15
  • I am using the result of the subquery to insert the documents. `FOR a in xx INSERT a in yy` Any suggestion how to do that with that single query. – Deepak Agarwal Jun 19 '15 at 14:45
  • Interestingly If I add a bad function after 'sum' like following I get the desired result: `, 'avg':average([g[*].m[*].min, g[*].m[*].max])` – Deepak Agarwal Jun 19 '15 at 14:54
  • Is it possible to try this with a 2.6.0? It should be fixed in there so all these queries should work. – stj Jun 23 '15 at 20:28