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 can create and manage schema using any of the following:

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!