Aggregation in arangodb using AQL

Question

I'm attempting a fairly basic task in arangodb, using the SUM() aggregate function.

Here is a working query which returns the right data (though not yet aggregated):

FOR m IN pkg_spp_RegMem
FILTER m.memberId == "40289"
COLLECT member = m.memberId INTO g
RETURN { "memberId" : member, "amount" : g[*].m[*].items }

This returns the following results:

[
  {
    "memberId": "40289",
    "amount": [
      [
        {
          "amount": 50,
          "description": "some description"
        }
      ],
      [
        {
          "amount": 50,
          "description": "some description"
        },
        {
          "amount": 500,
          "description": "some description"
        },
        {
          "amount": 0,
          "description": "some description"
        }
      ],
      [
        {
          "amount": 0,
          "description": "some description"
        },
      ]
    ]
  }
]

I am using Collect to group the results because a given memberId may have multiple'RegMem' objects. As you can see from the query/results, each object has a list of smaller objects called 'items', with each item having an amount and a description.

I want to SUM() the amounts by member. However, adjusting the query like this does not work:

FOR m IN pkg_spp_RegMem
FILTER m.memberId == "40289"
COLLECT member = m.memberId INTO g
RETURN { "memberId" : member, "amount" : SUM(g[*].m[*].items[*].amount) }

It returns 0 because it apparently can't find a field in the expanded items list called amount.

Looking at the results I can sort of understand why: the results are being returned such that items is actually a list, of lists of objects with amount/description. But I don't understand how to reference or expand the un-named list correctly to return the amount field values for the SUM() function.

Ideally the query should return the memberId and total amount, one row per member such that I can remove the filter and execute for all members.

Many thanks in advance if you can help! Martin

PS I've worked through the AQL tutorial on the arangodb website and checked out the manual but what would really help me is loads more example queries to look through. If anyone knows of a resource like that or wants to share some of their own, 'much obliged. Cheers!

Insurance

Answer 1

Edited: Misread the question the first time. The first one can be seen in theedit history, as it also contains some hints:

I replicated your data by creating some documents in this format (and some with only one item):

{
  "memberId": "40289",
  "items": [
    {
      "amount": 50,
      "description": "some description"
    },
    {
      "amount": 500,
      "description": "some description"
    }
  ]
}

Based on some of those types of documents, your non-summarized query should indeed be looking like this:

FOR m IN pkg_spp_RegMem
FILTER m.memberId == "40289"
COLLECT member = m.memberId INTO g

RETURN { "memberId" : member, "amount" :  g[*].m[*].items }

The data returned:

[
  {
    "memberId": "40289",
    "amount": [
      [
        {
          "amount": 50,
          "description": "some description"
        },
        {
          "amount": 0,
          "description": "some description"
        }
      ],
      [
        {
          "amount": 50,
          "description": "some description"
        },
        {
          "amount": 0,
          "description": "some description"
        }
      ],
      [
        {
          "amount": 50,
          "description": "some description"
        }
      ],
      [
        {
          "amount": 50,
          "description": "some description"
        },
        {
          "amount": 500,
          "description": "some description"
        }
      ],
      [
        {
          "amount": 0,
          "description": "some description"
        }
      ],
      [
        {
          "amount": 50,
          "description": "some description"
        },
        {
          "amount": 500,
          "description": "some description"
        }
      ]
    ]
  }
]

Based on the non summarized version, you need to loop through the items of the groups that have been generated by the collect function and do your SUM() there. In order to be able to SUM the items you must FLATTEN() them into a single list, before summarizing them.

FOR m IN pkg_spp_RegMem
FILTER m.memberId == "40289"
COLLECT member = m.memberId INTO g

RETURN { "memberId" : member, "amount" :  SUM(
                                              FLATTEN(
                                                       (
                                                         FOR r in g[*].m[*].items
                                                         RETURN r[*].amount
                                                       )
                                                     )
                                             )
       }

This results in:

[
  {
    "memberId": "40289",
    "amount": 1250
  }
]

Allistir

Thanks so much for your help! I didn't know about the flatten function but I'm getting an error when invoking it: "[1540] usage of unknown function 'FLATTEN()'". Is it user-defined? In any case, I am accepting your answer with gratitude as I was able to modify the query you provided in the following way to get the desired result: FOR m IN pkg_spp_RegMem FILTER m.memberId == "40289" COLLECT member = m.memberId INTO g RETURN { "memberId" : member, "amount" : SUM((FOR r IN g[].m[].items FOR i IN r RETURN i.amount)) } – Insurance

FLATTEN() is definitely a standard array function. Starting with v2.7, you will also be able to flatten with the new multi-star operator: arangodb.com/2015/06/aql-improvements-for-2-7 – Berty