[SOLVED] CrateDB aggregate all array elements

CrateDB aggregate all array elements

I am working to aggregate (sum) all returned array values.
The target column contains objects that contains an array of objects. e.g.

Object
- person: "bob"
- children: 
  - zaphod
    - age: 42 
  - ford
    - age: 42

In the above example, I attempt an aggregation over the ages like so, to no avail:

SELECT SUM(people['children']['age'])
FROM mydatabase
WHERE people IS NOT null
LIMIT 100;

The error I see is "UnsupportedFeatureException".
I can accept if this feature is unsupported. But, I am curious if I am simply going about this the wrong way. Can this be done or am I better served managing the data by a downstream process?

Solution

Version 4.6 of CrateDB (released on 2021-07-13) includes a new scalar function array_sum which can do this:

SELECT ARRAY_SUM(people['children']['age'])
FROM mydatabase
WHERE people IS NOT null
LIMIT 100;