---

title: BQL Reference description: Beancount Query Language syntax reference

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))

Available Columns

Each row in a default SELECT is one posting from one transaction. The columns below are evaluated against that pair. Posting-level columns vary across postings in the same transaction; transaction-level columns are identical for every posting in a given transaction. A ? after a type means the column may be NULL. StringSet columns are queried with IN / NOT IN, not = or ~.

Transaction columns

Column	Type	Description
date	Date	Transaction date
flag	String	Transaction flag (*, !, etc.)
payee	String?	Transaction payee, or NULL
narration	String	Transaction narration
description	String	"payee | narration" if payee set, else narration
tags	StringSet	All #tag values on the transaction
links	StringSet	All ^link values on the transaction
accounts	StringSet	The accounts of every posting in the transaction, sorted, each once
year, month, day	Integer	Date parts (shortcuts for year(date) etc.)
id	Integer?	Stable directive index (matches Python beancount's id)
type	String	Always "Transaction" for default SELECT
entry	Object	Whole transaction as a structured object (date, flag, payee, narration, tags, links, meta)

Posting columns

Column	Type	Description
account	String	This posting's account
other_accounts	StringSet	The accounts of every other posting in the transaction, sorted, each once
position	Position?	Units + cost (when present)
units	Amount?	Just the units (number + currency)
number	Number?	The numeric part of units
currency	String?	The currency part of units
cost	Amount?	Per-posting cost basis, total
cost_number	Number?	Per-unit cost ({} syntax)
cost_currency	String?	Cost currency
cost_date	Date?	Cost lot date
cost_label	String?	Cost label
has_cost	Boolean	Whether this posting carries a cost
weight	Amount?	Cost-converted amount used for balancing
price	Amount?	Posting's @/@@ price annotation
posting_flag	String?	Per-posting flag, distinct from the transaction's flag
meta	Metadata	This posting's metadata (see Metadata Functions)

Cumulative / contextual columns

Column	Type	Description
balance	Inventory?	Cumulative running total across WHERE-filtered postings (matches bean-query)
account_balance	Inventory?	Per-account running balance, independent of WHERE. Within a transaction it follows booking: a sale never reduces a lot bought earlier in the same transaction (see below)

account_balance realizes lots through the booking engine, the same way report balances does. Booking reads each sale against the inventory before its transaction plus the transaction's earlier sales, never its purchases, so the row for a posting shows the account after the transaction's postings up to and including it, read that way. For an AVERAGE account or a {*} sale, a purchase written earlier in the same transaction therefore stays out of the pool the sale is taken from, matching the cost the journal books the sale at. balance is unaffected: it adds positions as bean-query does.

Source location columns

Column	Type	Description
filename	String?	Source file the directive came from
lineno	Integer?	Line number in that file
location	String?	"filename:lineno" shortcut

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

IN does scalar membership against a literal list, and set membership against a StringSet column.

-- Scalar in a literal set
WHERE account IN ("Assets:Bank", "Assets:Cash")
WHERE currency IN ("USD", "EUR")

-- Scalar in a column-valued set
WHERE "vacation" IN tags
WHERE "Assets:Investments:Cash-USD" IN other_accounts

-- Negated form
WHERE "draft" NOT IN tags
WHERE account NOT IN ("Equity:Opening", "Equity:Closing")

Set columns

tags, links, accounts, and other_accounts are StringSet-typed. Equality (=) and regex (~) don't apply to sets; the only operators that work are IN and NOT IN. To filter a transaction by which other accounts appear on its postings, put the account literal on the left:

-- All dividend postings whose other side(s) include Cash-USD
SELECT date, narration, account, position
WHERE account ~ '^Income:Investment:Dividend'
  AND 'Assets:Investments:Cash-USD' IN other_accounts

other_accounts is the set of the accounts of every other posting in the same transaction: only the current posting is left out, so its own account still appears when another posting in the transaction also uses it, as in bean-query. That is what makes the "find rows where this posting was paired with X" query work.

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

Amounts sort by currency, then number, so each currency's values stay together: -5.00 EUR, 5.00 EUR, -5 USD, 5 USD. Positions sort by units currency (USD, EUR, JPY, CAD, GBP, AUD, NZD, CHF first, as in beancount, then the rest alphabetically), then cost, then units; an inventory by its positions sorted, compared in turn. NULL sorts first in ascending order.

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.

The new columns are the second column's values sorted by value, NULL first, as bean-query sorts them. The rows follow the query's ORDER BY, applied before the pivot, and are sorted by the first column's values when there is none. LIMIT counts the pivoted rows.

Aggregate Functions

Function	Description
sum(position)	Sum positions
count(*)	Count rows
first(x)	First value
last(x)	Last value
min(x)	Minimum value: the first value ORDER BY x would give (NULLs skipped)
max(x)	Maximum value: the first value ORDER BY x DESC would give (NULLs skipped)
avg(x)	Average value

Examples

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

min and max work on numbers, strings, dates, booleans (false < true), amounts, positions and inventories, in ORDER BY's order. Amounts compare by currency, then number, so across currencies the result follows the currency order, not size: max(units(position)) over 5 EUR and 3 USD is 3 USD. bean-query answers 5 EUR there; see section 15 of the compatibility notes.

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 name (Mon..Sun)	Fri
today()	Current date	2024-03-15
yearmonth(date)	First of that month, as a date	2024-03-01

Amount Functions

Function	Description
cost(position)	Convert to cost basis
units(position)	Get units (number + currency)
currency(position)	Get currency
commodity(amount)	Get the currency of an amount
number(amount)	Extract number from amount

commodity() and currency() overlap but are not interchangeable. commodity() takes an amount, which is how bean-query spells it, so commodity(units(position)) and commodity(cost(position)) let you ask for the units currency and the cost currency separately — on 2 HOOL {10.00 USD} they return HOOL and USD. currency(position) takes a position and always answers about its units; it is a rustledger extension that bean-query rejects. Prefer commodity() in queries you want to run under both.

cost(position) over the position column is what the posting's units cost, signed like its units, and matches its weight. A lot of 3 X bought at a total cost of 500 USD costs 500, though its per-unit cost is 500/3 rounded, and the sale that empties it costs -500, so an emptied account's sum(cost(position)) is exactly zero. bean-query multiplies the rounded per-unit cost back out and gives 500.0000000000000000000000001. BALANCES AT COST values a held lot at its total the same way, and JOURNAL ... AT COST shows each row's exact cost and a running balance of them. SELECT ... FROM #postings gives the same cost(position) and weight(position) as the default FROM, and so does a subquery that passes the posting column through, as in FROM (SELECT account, position), renamed, nested, or from its SELECT *. A column the subquery computes (units(position) AS position), or one from a DISTINCT or grouped subquery, is a value and has no posting to read: its weight skips any price (units × per-unit for a lot held at cost, else its units), and its cost is units × per-unit, rounded as the per-unit cost is.

cost() of an inventory, such as cost(sum(position)), is an inventory of what each lot cost, per currency, as in bean-query: an account holding 30 USD and 207 EUR of cost gives 207 EUR, 30 USD. sum(position) keeps each lot's exact total, so cost(sum(position)) equals sum(cost(position)) over the same rows. number() of it gives the number when there is one currency and NULL when there are several.

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
findfirst(regex, set)	First element of a set matching a regex, else NULL
has_account(regex)	True if any posting on the entry has a matching account

has_account() asks about the whole entry, not the current posting, so it is true on every row of a transaction that touches a matching account. It also works as an entry filter: BALANCES FROM has_account('Assets').

Metadata Functions

Beancount metadata can live on a transaction (the line under the header, before any postings) or on a posting (indented one level deeper than the posting itself). BQL exposes three lookup functions because the distinction matters at query time:

Function	Looks at	Use when
meta(key)	Posting metadata only	The key is set per-posting (e.g. a lot-id on one leg of a transfer)
entry_meta(key)	Transaction metadata only	The key is set on the transaction header (e.g. a budget-category shared by every posting)
any_meta(key)	Posting first, then transaction	You don't know or don't care which level it's set on

All three return NULL when the key is absent at the relevant level — so a WHERE meta('foo') = 'bar' filter quietly drops every row when foo was actually set on the transaction.

Commodity metadata is separate, because it lives on a commodity directive rather than on any transaction or posting:

Function	Returns
commodity_meta(currency)	The whole metadata map of that currency's commodity directive
commodity_meta(currency, key)	One key from it
currency_meta(...)	Alias for commodity_meta, same two forms

Both return NULL for a currency with no commodity directive, and for a key that directive does not set. A currency declared more than once resolves to the last declaration.

2024-01-15 * "Grocery Store"
  budget-category: "food"          ; on the transaction
  Expenses:Food:Groceries  85.00 USD
    receipt-id: "R-12345"          ; on this posting only
  Assets:Bank:Checking

-- Doesn't match: budget-category is on the transaction, not the posting.
SELECT account WHERE meta('budget-category') = 'food'

-- Matches every posting in the transaction.
SELECT account WHERE entry_meta('budget-category') = 'food'

-- Matches whichever level happens to carry the key.
SELECT account WHERE any_meta('budget-category') = 'food'

-- Matches only the Expenses posting, since receipt-id is per-posting.
SELECT account WHERE meta('receipt-id') = 'R-12345'

This mirrors Python bean-query's meta / entry_meta / any_meta exactly, so queries are portable between the two engines.

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 (number + 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

Date Arithmetic

A date takes a whole number of days on either side of +, and gives a date back:

WHERE date >= 2026-07-01 AND date < 2026-07-01 + 365
SELECT 2026-07-01 - 30 AS a_month_earlier

Subtracting one date from another gives the number of days between them:

SELECT max(date) - min(date) AS days_covered

The count has to be whole. 2026-07-01 + 0.5 is an error rather than a guess, because a date has day resolution; a whole-valued decimal such as 30.0 is fine. date_add(date, days) spells the same shift as a function, and date + interval(n, 'day') spells it with the unit named -- interval also takes week, month, quarter and year.

Tags and Links

Filtering by Tag

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

Filtering by Link

WHERE "trip-2024" IN links

Subqueries

A subquery can be used as the source of a FROM clause:

SELECT account, total
FROM (SELECT account, sum(position) AS total GROUP BY account)

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