Changing AQL so that sub-query result is flattened into two seperate arrays

Question

This query works:

FOR person IN 1..1 INBOUND @companyID employed_by
    LET age = DATE_DIFF(person.age * 1000, @currentTime * 1000, 'y')
    COLLECT label = age WITH COUNT INTO value
        RETURN {data: label, frequency: value}

And gives me this:

[
    {
        data: 18,
        frequency: 69
    },
    {
        data: 19,
        frequency: 73
    },
    {
        data: 20,
        frequency: 86
    }
]

But what i really want is something like this

{
    data: [18, 19, 20]
    frequency: [69, 73, 86]
}

I was expecting the following query to work - but the PUSH statements fail (syntax error), i tried a bunch of PUSH statements in FOR loops but can't get them to work as i expect, which would imply i am doing something absolutely mental

LET data = []
LET frequency = []
LET temp = 
        (
            FOR person IN 1..1 INBOUND @companyID employed_by
                LET age = DATE_DIFF(person.age * 1000, @currentTime * 1000, 'y')
                COLLECT label = age WITH COUNT INTO value
                    data = PUSH(data, label)
                    frequency = PUSH(frequency, value)
                    RETURN true
        )
RETURN {data: data, frequency: frequency}

Any advice would be great!

You figured it out, kudos! A note on PUSH: it lets you append an element, but you can't re-define an existing variable - what you tried is to change the variables `data` and `frequency` (multiple times), but it's not allowed (not side-effect free). AQL is not a full programming language, and this is on purpose: queries can be optimized a lot because of AQL's inherent limitations. There are usually pure AQL solutions though, using sub-queries for instance. — CodeManX, May 14 '16 at 12:16
since you found the answer to your problem already, can you mark your answer as accepted? ;-) — dothebart, May 17 '16 at 08:44

score 3 · Accepted Answer · answered May 13 '16 at 18:31

And, after some more fiddling - this seems to be exactly what I needed:

LET temp = 
    (
        FOR person IN 1..1 INBOUND @companyID employed_by
            LET age = DATE_DIFF(person.age * 1000, @currentTime * 1000, 'y')
            COLLECT label = age WITH COUNT INTO value
                RETURN {data: label, frequency: value}
    )
RETURN {data: temp[*].data, frequency: temp[*].frequency}

Changing AQL so that sub-query result is flattened into two seperate arrays

1 Answers1