ArangoDB AQL grouping

[Andrey Guskov]

Hi there, I am new to ArangoDB and want to learn it. This is a rather specific question I am trying to wrap my head around (I try to figure stuff out applying AQL to problems I encounter at work for which we use SQL). So here is an interesting one.

I have a graph with the following structure:

                  .....CLIENT-.....
                  |       |       |
                  |       |       |
                  |       |       |
                 FEE     FEE      FEE
                  |       |         |
                  |100    |200      |300
                  |       |         |
               BANK ACCOUNT A     BANK ACCOUNT B

I have a bunch of clients who pay fees and the money from these fees are deposited into bank accounts; one fee can be split into multiple bank accounts and a fee can have only one client. There are two relations: ClientHasFee {} - empty and FeeAccoutAllocation {allocation: 100 dollar} - has the allocation; these are stored in relation collections. I want to know how much money is help for each client, per bank account. I came up with this query:

FOR fee IN ANY 'my client id' ClientHasFee
    return (for bank, link in any fee._id FeeAccoutAllocation
        collect bank_id = bank._id, alloc = link.allocation
        return {bank_id, alloc})

which give me results like this:
[
  [
    [
      {
        "bank_id": "BankAccounts/13859963",
        "alloc": 500
      }
    ],
    [
      {
        "bank_id": "BankAccounts/13859963",
        "alloc": 500
      }
    ]
  ]
]

which is ok... I can then process it in code and get the sums per client but I was hoping to get something close to this:
{
 client_id: 'client ID',
 allocations:
  [
    bank_a: 300,
    bank_b: 300
  ]
}
Anybody can give a hint how to achieve this? Thanks you

[Jan]

Hi there,

it would be very helpful if you could provide the collections and some example data for this so we can find out how to do it with your data schema.
Some setup script such as

    db._create("BankAccounts");
    db.BankAccount.insert({ _key: "..." });

    db._createEdgeCollection("FeeAccountAllocations");
    db.FeeAccountAllocations.insert({ _from: "...", _to: "..." });

    db._createEdgeCollection("ClientHasFee");
    db.ClientHasFee.insert({ _from: "...", _to: "..." });

for all collections that will participate in the query would be very helpful.
Thanks!
J