Aggregate Array By Group | ARRAY_GROUP_AGGREGATE

Syntax

ARRAY_GROUP_AGGREGATE( [value], [key], [aggregation] )

Parameters

value
The ARRAY being aggregated. It should be an ARRAY of MAP / OBJECT records.

key
A single path or an array of paths used to select the value in each entry for grouping. If the path is an array, the items will be grouped by all the unique, ordered combinations of the values. For more information on the path syntax used by instalink.io see the dedicated documentation on that subject.

aggregation
A MAP that defines how the VALUE is to be aggregated. Each key in the MAP should be a path into the entries in the VALUE array. Each value in the map must be an aggregation operator. The valid operators are as follows:

• sum: Get the sum of the values in the entries.
• average: Get the average of the values in the entries.
• first: Get the value in the first entry.
• last: Get the value in the last entry.
• max: Get the largest of the values in the entries.
• min: Get the smallest of the values in the entries.
• push: Collect the items in each of the entries in an ARRAY.

Return Value

An array of MAP / OBJECT with the results of the aggregation performed on each grouped ARRAY of VALUE.

Description

Reduce an ARRAY of OBJECT / MAP records into an ARRAY of OBJECT according to a grouping key and a provided aggregation operation. This function is equivalent to calling ARRAY_GROUP_BY on an array and then calling ARRAY_AGGREGATE on each ARRAY group.

Examples

# VALUE is [
#   ["a" => 1, "b" => 2, "c" => "a"],
#   ["a" => 3, "b" => 4, "c" => "b"],
#   ["a" => 5, "b" => 6, "c" => "a"]
# ]
ARRAY_GROUP_AGGREGATE(VALUE, "c", ["a" => "sum", "b" => "average"])
# returns [["a" => 6, "b" => 4], ["a" => 3, "b" => 4]]

# VALUE is [
#   ["a" => 1, "b" => 2, "c" => "a"],
#   ["a" => 3, "b" => 4, "c" => "b"],
#   ["a" => 5, "b" => 6, "c" => "a"]
# ]
ARRAY_GROUP_AGGREGATE(VALUE, "c", ["a" => "first", "b" => "last"])
# returns [["a" => 1, "b" => 6], ["a" => 3, "b" => 4]]

# VALUE is [
#   ["a" => 1, "b" => 2, "c" => "a"],
#   ["a" => 3, "b" => 4, "c" => "b"],
#   ["a" => 5, "b" => 6, "c" => "a"]
# ]
ARRAY_GROUP_AGGREGATE(VALUE, "c", ["a" => "max", "b" => "min"])
# returns [["a" => 5, "b" => 2], ["a" => 3, "b" => 4]]

# VALUE is [
#   ["a" => 1, "b" => 2, "c" => "a"],
#   ["a" => 3, "b" => 4, "c" => "b"],
#   ["a" => 5, "b" => 6, "c" => "a"]
# ]
ARRAY_GROUP_AGGREGATE(VALUE, "c", ["a" => "push"])
# returns [["a" => [1, 5]], ["a" => [3]]

# VALUE is [
#   ["a" => ["b" => 1], "c" => "a"],
#   ["a" => ["b" => 4], "c" => "b"],
#   ["a" => ["b" => 10], "c" => "a"]
# ]
ARRAY_GROUP_AGGREGATE(VALUE, "c", ["a.b" => "average"])
# returns [["a" => ["b" => 5.5]], ["a" => ["b" => 4]]]