Aggregation: GROUP BY & FLATTEN

Basalt's DQL engine supports SQL-style relational transformations: grouping rows into summaries, computing aggregate statistics (sum, avg, min, max, count), and expanding nested arrays into individual rows using FLATTEN.

GROUP BY

GROUP BY <field> collapses multiple matching notes into single rows partitioned by distinct field values.

TABLE status, count(rows) AS "Total Notes"
FROM #project
GROUP BY status

The rows Variable

When a query contains GROUP BY, rows within each group are collected into an internal array named rows.

• To access the grouping key itself, use the field name directly (e.g. status).
• To access values from the individual notes within the group, reference rows.<field>:

TABLE category, rows.file.name AS "Notes", avg(rows.rating) AS "Average Rating"
FROM #books
GROUP BY category

In the example above, rows.file.name renders as an array list of links to all book notes in that category, while avg(rows.rating) computes the numeric average.

Aggregate Functions

Aggregate functions only evaluate inside a GROUP BY context and operate across the rows collection:

Function	Signature	Description	Example
count	count(rows)	Returns total notes in the group	count(rows)
length	length(rows)	Equivalent to count(rows)	length(rows)
sum	sum(rows.<field>)	Sums numeric values, ignoring nulls	sum(rows.cost)
avg / average	avg(rows.<field>)	Arithmetic mean of numeric values	avg(rows.score)
min	min(rows.<field>)	Smallest numeric value or earliest date	min(rows.due)
max	max(rows.<field>)	Largest numeric value or latest date	max(rows.priority)

Example: Project Budget Summary

TABLE department, 
      count(rows) AS "Projects", 
      sum(rows.budget) AS "Total Allocated",
      avg(rows.budget) AS "Avg Budget",
      min(rows.deadline) AS "Next Deadline"
FROM "Projects"
GROUP BY department
SORT sum(rows.budget) DESC

---

FLATTEN

FLATTEN <expr> [AS <alias>] expands an array or list field so that each item becomes its own separate row in the query results.

If a note has tags: [work, client, q3], standard queries display the array in a single row. FLATTEN tags AS tag splits that single note into three distinct rows.

Example: Unrolling Tags

TABLE file.name, tag
FROM "Notes"
FLATTEN file.tags AS tag
WHERE tag != null

Using FLATTEN for Calculated Columns

FLATTEN can also compute derived expressions once per row and give them a readable alias for filtering or sorting:

TABLE file.name, word_count
FROM "Essays"
FLATTEN length(file.name) AS name_len
WHERE name_len > 15

---

Combining FLATTEN and GROUP BY

A common query pattern in PKM is calculating tag frequencies across an entire vault. By combining FLATTEN and GROUP BY, you can explode all tags and immediately aggregate them:

TABLE tag, count(rows) AS "Usage Count"
FROM ""
FLATTEN file.tags AS tag
WHERE tag != null
GROUP BY tag
SORT count(rows) DESC
LIMIT 20

1. FROM "" matches all notes in the vault.
2. FLATTEN file.tags AS tag emits one row for every tag occurrence.
3. WHERE tag != null discards untagged notes.
4. GROUP BY tag gathers rows sharing the same tag.
5. count(rows) counts occurrences per tag.
6. SORT count(rows) DESC sorts from most used to least used.