Group By: Aggregate data in Fauna
SQL’s GROUP BY
operation lets you organize rows into groups based on the value
of specific columns.
GROUP BY
is commonly used to aggregate data. For example, you can use GROUP
BY
to:
-
Calculate total sales by category
-
Count customers by region
-
Find average prices by product category.
Create a groupBy()
function
FQL doesn’t provide a built-in GROUP BY
operation. However, you use array.fold()
and set.fold()
in an
FQL function or a
user-defined function (UDF) to
achieve the same result.
As an FQL function:
// Defines an anonymous `groupBy()` function.
// `groupBy()` two arguments:
// * `set`: Set or Array containing data to group
// * `key_fn`: Grouping key for each element
let groupBy = (set, key_fn) => {
// Calls the `fold()` function on the `set`
// Set or Array.
let data: Any = set
data.fold(
// Start with an empty object.
{},
(acc, val) => {
// For each value, get a key using the `key_fn` arg.
let key = key_fn(val)
let existing_group = acc[key] ?? []
// Append the current value to the Set or
// Array for that key.
let new_group = existing_group.append(val)
let new_entry = Object.fromEntries([
[key, new_group]
])
// Return an object with grouped results.
Object.assign(acc, new_entry)
}
)
}
// Call the `groupBy()` function.
// Groups `Product` documents by category name.
groupBy(Product.all(), .category!.name)
You can also define a groupBy()
UDF. This lets you reuse
the function across multiple queries.
You create and manage a UDF as an FSL function schema:
// Defines the `groupBy()` UDF.
// `groupBy()` two arguments:
// * `set`: Set or Array containing data to group
// * `key_fn`: Grouping key for each element
function groupBy (set, key_fn) {
// Calls the `fold()` function on the `set`
// Set or Array.
let data: Any = set
data.fold(
// Start with an empty object.
{},
(acc, val) => {
// For each value, get a key using the `key_fn` arg.
let key: String = key_fn(val)
let existing_group = acc[key] ?? []
// Append the current value to the Set or
// Array for that key.
let new_group = existing_group.append(val)
let new_entry = Object.fromEntries([
[key, new_group]
])
// Return an object with grouped results.
Object.assign(acc, new_entry)
}
)
}
You save and manage function schema in Fauna using the
Fauna Dashboard or the
Fauna CLI's
fauna schema push
command.
Usage examples
This section contains examples using the previously defined groupBy()
UDF.
Group numbers by range
let numbers = [1, 2, 3, 4, 5, 6, 7, 8, 9]
groupBy(numbers, i => if(i < 5) { "low" } else { "high" })
{
low: [
1,
2,
3,
4
],
high: [
5,
6,
7,
8,
9
]
}
Group objects by property
let items = [
{ val: 1 },
{ val: 2 },
{ val: 3 }
]
groupBy(items, .val.toString())
{
"1": [
{
val: 1
}
],
"2": [
{
val: 2
}
],
"3": [
{
val: 3
}
]
}
Group query results
// Get the `frozen` and `produce` categories.
let frozen = Category.byName("frozen").first()
let produce = Category.byName("produce").first()
// Get products for the `frozen` and `produce`
// categories.
let items = (
Product.byCategory(frozen)
.concat(Product.byCategory(produce)) {
id,
category: .category!.name
})
.take(5)
.toArray()
// Group products by category name.
groupBy(items, .category)
{
frozen: [
{
id: "333",
category: "frozen"
}
],
produce: [
{
id: "444",
category: "produce"
},
{
id: "555",
category: "produce"
},
{
id: "666",
category: "produce"
},
{
id: "777",
category: "produce"
}
]
}
Is this article helpful?
Tell Fauna how the article can be improved:
Visit Fauna's forums
or email docs@fauna.com
Thank you for your feedback!