Beancount Query Language

Query Structure and Execution

Basic Query Format

SELECT <target1>, <target2>, ...
FROM <entry-filter-expression>
WHERE <posting-filter-expression>;

Two-Level Filtering System

1. Transaction Level (FROM)

   • Filters whole transactions
   • Maintains double-entry integrity
   • Affects all related postings

2. Posting Level (WHERE)

   • Filters individual postings
   • For presentation purposes
   • Can break transaction integrity

Data Model

Transaction Structure

Transaction
├── date
├── flag
├── payee
├── narration
├── tags
├── links
└── Postings[]
    ├── account
    ├── units
    ├── cost
    ├── price
    └── metadata

Available Column Types

1. Transaction Attributes

SELECT
    date,           -- datetime.date
    year,          -- int
    month,         -- int
    day,           -- int
    flag,          -- str
    payee,         -- str
    narration,     -- str
    tags,          -- set(str)
    links          -- set(str)

2. Posting Attributes

SELECT
    account,       -- str
    position,      -- Position
    units,         -- Amount
    cost,          -- Cost
    price,         -- Amount
    weight,        -- Amount
    balance        -- Inventory

Query Functions

Aggregation Functions

-- Basic aggregation
SELECT COUNT(*)
SELECT SUM(position)
SELECT FIRST(date)
SELECT LAST(date)
SELECT MIN(position)
SELECT MAX(position)

-- With grouping
SELECT account, SUM(position) GROUP BY account

Position/Inventory Functions

-- Units extraction
SELECT UNITS(position)       -- Show only number and currency
SELECT COST(position)        -- Show total cost
SELECT WEIGHT(position)      -- Show balance amount
SELECT VALUE(position)       -- Show market value

-- Complex analysis
SELECT
    account,
    COST(SUM(position)) as total_cost,
    VALUE(SUM(position)) as market_value
GROUP BY account

Advanced Features

Balance Reports

-- Simple balance sheet
BALANCES FROM close ON 2024-01-01
WHERE account ~ "^Assets|^Liabilities"

-- Income statement
BALANCES FROM
    OPEN ON 2024-01-01
    CLOSE ON 2024-12-31
WHERE account ~ "^Income|^Expenses"

Journal Reports

-- Account activity
JOURNAL "Assets:Checking" AT COST

-- Multi-account journal
JOURNAL "Assets:.*:401k" AT UNITS

Print Operations

-- Print filtered transactions
PRINT FROM year = 2024
WHERE account ~ "Assets:Investments"

-- Debug transaction
PRINT FROM id = "8e7c47250d040ae2b85de580dd4f5c2a"

Filtering Expressions

-- Complex date filtering
SELECT * FROM
    year = 2024 AND month >= 6
WHERE account ~ "Expenses:Travel"

-- Tag and link filtering
SELECT * FROM
    "vacation-2024" IN tags OR
    "business" IN links

Performance Considerations

1. Data Loading

   • Transactions sorted chronologically
   • Full parse before query execution
   • In-memory processing

2. Query Optimization

   • FROM filters applied first
   • WHERE clause filters postings
   • Aggregations performed last

3. Memory Usage

   • Position objects held in memory
   • Inventory aggregation efficient
   • No disk-based operations

Best Practices

1. Query Organization

-- Clear structure
SELECT
    date,
    account,
    position
FROM
    year = 2024
WHERE
    account ~ "Expenses"
ORDER BY
    date DESC;

2. Debugging

-- Explain query
EXPLAIN SELECT date, account, position;

-- Test filters
SELECT DISTINCT account
WHERE account ~ "^Assets:.*";

3. Balance Assertions

-- Verify account balance
SELECT account, sum(position)
FROM close ON 2024-01-01
WHERE account = "Assets:Checking";

This technical guide provides a comprehensive overview of Beancount's query capabilities while maintaining accuracy and technical detail.