BQL Reference

BQL (Beancount Query Language) is a SQL-like language for querying beancount ledgers.

Basic Syntax

SELECT columns
WHERE condition
GROUP BY columns
ORDER BY columns
LIMIT n

SELECT Clause

Columns

-- Basic columns
SELECT account, date, narration, payee, position

-- All columns
SELECT *

-- Expressions
SELECT account, sum(position) AS total

-- Functions
SELECT year(date), month(date), sum(cost(position))

Column Aliases

SELECT account AS acc, sum(position) AS balance

WHERE Clause

Comparison Operators

WHERE date = 2024-01-15
WHERE date > 2024-01-01
WHERE date >= 2024-01-01
WHERE date < 2024-12-31
WHERE date <= 2024-12-31
WHERE date != 2024-01-15

String Matching

-- Exact match
WHERE account = "Assets:Bank:Checking"

-- Regex match (case-insensitive by default)
WHERE account ~ "Assets:Bank"
WHERE narration ~ "coffee"
WHERE payee ~ "Amazon"

Logical Operators

WHERE account ~ "Assets" AND date >= 2024-01-01
WHERE account ~ "Income" OR account ~ "Expenses"
WHERE NOT account ~ "Equity"

NULL Checks

WHERE payee IS NOT NULL
WHERE payee IS NULL

IN Operator

WHERE account IN ("Assets:Bank", "Assets:Cash")
WHERE "vacation" IN tags

GROUP BY Clause

SELECT account, sum(position)
GROUP BY account

-- Multiple columns
SELECT year(date), month(date), sum(position)
GROUP BY year(date), month(date)

-- By position (1-indexed)
SELECT year(date), sum(position)
GROUP BY 1

ORDER BY Clause

ORDER BY date
ORDER BY date DESC
ORDER BY account ASC

-- Multiple columns
ORDER BY year(date), month(date)

-- By expression
ORDER BY sum(position) DESC

LIMIT Clause

LIMIT 10
LIMIT 100

PIVOT BY Clause

Pivot results to create columns from row values:

-- Expenses by category and year, pivoted by year
SELECT root(account, 2), year(date), sum(cost(position))
WHERE account ~ "Expenses"
GROUP BY 1, 2
PIVOT BY 2

Note: PIVOT BY must reference a SELECT output column, either by name or by its 1-indexed position; pivoting by an arbitrary expression (for example, PIVOT BY YEAR(date)) is not supported.

Aggregate Functions

Function	Description
sum(position)	Sum positions
count(*)	Count rows
first(x)	First value
last(x)	Last value
min(x)	Minimum value
max(x)	Maximum value

Examples

SELECT account, sum(position) GROUP BY account
SELECT count(*) WHERE account ~ "Expenses"
SELECT min(date), max(date)

Scalar Functions

Date Functions

Function	Description	Example
year(date)	Extract year	2024
month(date)	Extract month	3
day(date)	Extract day	15
quarter(date)	Extract quarter	1
weekday(date)	Day of week (0=Mon)	4
today()	Current date	2024-03-15

Amount Functions

Function	Description
cost(position)	Convert to cost basis
units(position)	Get units (number)
currency(position)	Get currency
number(amount)	Extract number from amount

String Functions

Function	Description
length(str)	String length
upper(str)	Convert to uppercase
lower(str)	Convert to lowercase
root(account, n)	First n account segments
leaf(account)	Last account segment
parent(account)	All but last segment

Examples

-- Expense by category
SELECT root(account, 2) AS category, sum(cost(position))
WHERE account ~ "Expenses"
GROUP BY category

-- Monthly totals
SELECT year(date) AS y, month(date) AS m, sum(cost(position))
GROUP BY y, m
ORDER BY y, m

Position and Amount

BQL distinguishes between:

• Position: Amount with cost basis (e.g., 10 AAPL {150.00 USD})
• Amount: Simple number with currency (e.g., 1500.00 USD)

Converting Positions

-- Get cost in operating currency
SELECT sum(cost(position))

-- Get units (ignoring currency)
SELECT sum(units(position))

-- Get currency
SELECT currency(position)

Date Literals

Dates without quotes:

WHERE date = 2024-01-15
WHERE date >= 2024-01-01 AND date < 2024-04-01

Tags and Links

Filtering by Tag

WHERE "vacation" IN tags
WHERE "project" IN tags

Filtering by Link

WHERE "trip-2024" IN links

Subqueries

Not currently supported. Use multiple queries or shell piping.

Examples

Account Balances

SELECT account, sum(position)
GROUP BY account
ORDER BY account

Monthly Expenses

SELECT year(date), month(date), sum(cost(position))
WHERE account ~ "Expenses"
GROUP BY 1, 2
ORDER BY 1, 2

Top Spending Categories

SELECT root(account, 2), sum(cost(position))
WHERE account ~ "Expenses"
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10

Transactions with Payee

SELECT date, payee, narration, account, position
WHERE payee ~ "Amazon"
ORDER BY date DESC

Net Worth

SELECT sum(cost(position))
WHERE account ~ "Assets" OR account ~ "Liabilities"

Year-over-Year

-- Run separate queries for each year
SELECT root(account, 2), sum(cost(position)) AS "2023"
WHERE account ~ "Expenses" AND year(date) = 2023
GROUP BY 1
ORDER BY 1

SELECT root(account, 2), sum(cost(position)) AS "2024"
WHERE account ~ "Expenses" AND year(date) = 2024
GROUP BY 1
ORDER BY 1

Output Formats

# Text (default)
rledger query ledger.beancount "SELECT ..."

# CSV
rledger query -f csv ledger.beancount "SELECT ..."

# JSON
rledger query -f json ledger.beancount "SELECT ..."

Tips

Use Regex for Account Matching

-- Match all bank accounts
WHERE account ~ "Assets:Bank"

-- Match any asset
WHERE account ~ "^Assets:"

Group by Account Hierarchy

-- Top-level categories
SELECT root(account, 1), sum(position) GROUP BY 1

-- Two levels deep
SELECT root(account, 2), sum(position) GROUP BY 1

Date Range Filtering

-- This year
WHERE year(date) = year(today())

-- This month
WHERE year(date) = year(today()) AND month(date) = month(today())

-- Specific date range
WHERE date >= 2024-01-01 AND date < 2024-02-01

See Also

• query command - Running queries
• Common Queries - Useful query examples