BQL Functions Reference¶

Complete reference for all BQL functions.

Position/Amount Functions¶

NUMBER¶

Extract numeric value from an amount or position.

NUMBER(amount) → Decimal
NUMBER(position) → Decimal

Examples:

SELECT NUMBER(units)                    -- 100.00
SELECT NUMBER(position)                 -- Units number from position

CURRENCY¶

Extract currency from an amount or position.

CURRENCY(amount) → String
CURRENCY(position) → String

Examples:

SELECT CURRENCY(units)                  -- "USD"

UNITS¶

Extract units (amount without cost) from a position.

UNITS(position) → Amount
UNITS(amount) → Amount
UNITS(inventory) → String

For inventories, returns a formatted string of all positions.

COST¶

Calculate total cost of a position or inventory.

COST(position) → Amount
COST(inventory) → Amount

Formula: |units.number| × cost.number

Returns NULL if no cost basis.

WEIGHT¶

Calculate balancing weight of a position.

WEIGHT(position) → Amount
WEIGHT(amount) → Amount

Weight calculation: - Position with cost: units × cost - Position without cost: units - Amount with price: handled at posting level

VALUE¶

Convert to market value using price database.

VALUE(position) → Amount
VALUE(position, currency) → Amount
VALUE(inventory) → Amount
VALUE(inventory, currency) → Amount

Uses most recent price as of transaction date.

Examples:

SELECT VALUE(position, "USD")           -- Convert to USD
SELECT VALUE(position)                  -- Use default operating currency

GETITEM / GET¶

Extract amount for specific currency from inventory.

GETITEM(inventory, currency) → Amount
GET(inventory, currency) → Amount

Returns NULL if currency not in inventory.

GETPRICE¶

Look up price from price database.

GETPRICE(base, quote) → Decimal
GETPRICE(base, quote, date) → Decimal

Returns NULL if no price found.

Date Functions¶

YEAR¶

Extract year from date.

YEAR(date) → Integer

Example:

SELECT YEAR(date)                       -- 2024

MONTH¶

Extract month from date (1-12).

MONTH(date) → Integer

DAY¶

Extract day of month from date (1-31).

DAY(date) → Integer

QUARTER¶

Extract quarter from date.

QUARTER(date) → String

Returns format like "2024-Q1" (year and quarter).

WEEKDAY¶

Extract day of week as abbreviated name.

WEEKDAY(date) → String

Returns "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", or "Sun".

String Functions¶

LENGTH¶

Get string length.

LENGTH(string) → Integer
LENGTH(set) → Integer

For sets (tags, links), returns number of elements.

UPPER¶

Convert to uppercase.

UPPER(string) → String

LOWER¶

Convert to lowercase.

LOWER(string) → String

SUBSTR / SUBSTRING¶

Extract substring.

SUBSTR(string, start) → String
SUBSTR(string, start, length) → String

0-indexed start position.

Account Functions¶

PARENT¶

Get parent account (remove last component).

PARENT(account) → String

Example:

PARENT("Assets:Bank:Checking")          -- "Assets:Bank"

LEAF¶

Get last account component.

LEAF(account) → String

Example:

LEAF("Assets:Bank:Checking")            -- "Checking"

ROOT¶

Get first N components of account.

ROOT(account, n) → String

Example:

ROOT("Assets:Bank:Checking", 2)         -- "Assets:Bank"

Aggregate Functions¶

COUNT¶

Count rows or non-NULL values.

COUNT(*) → Integer
COUNT(expr) → Integer

SUM¶

Sum values (works with amounts, positions, inventories).

SUM(amount) → Amount
SUM(position) → Inventory
SUM(number) → Decimal

Example:

SELECT account, SUM(position)
GROUP BY account;

MIN / MAX¶

Minimum or maximum value.

MIN(expr) → same type
MAX(expr) → same type

Works with numbers, dates, strings.

FIRST / LAST¶

First or last value in group (by sort order).

FIRST(expr) → same type
LAST(expr) → same type

Useful with ORDER BY:

SELECT account, FIRST(date), LAST(date)
GROUP BY account
ORDER BY date;

Inventory Functions¶

EMPTY¶

Check if inventory is empty.

EMPTY(inventory) → Boolean

Returns TRUE for NULL inventories.

FILTER_CURRENCY¶

Filter inventory to single currency.

FILTER_CURRENCY(inventory, currency) → Inventory

Example:

FILTER_CURRENCY(balance, "USD")         -- Only USD positions

POSSIGN¶

Adjust sign based on account type (debit-normal vs credit-normal).

POSSIGN(amount, account) → Amount

Credit-normal accounts (Liabilities, Equity, Income) have signs inverted.

Example:

SELECT POSSIGN(units, account)          -- Positive for debit-normal

Type Conversion¶

COALESCE¶

Return first non-NULL argument.

COALESCE(expr1, expr2, ...) → same type

Example:

COALESCE(payee, "Unknown")

ABS¶

Absolute value.

ABS(number) → Decimal
ABS(amount) → Amount

Metadata Access¶

META¶

Access metadata value by key.

META(key) → MetaValue

Example:

SELECT META("category")
WHERE META("reviewed") = TRUE;

Special Functions¶

ENTRY_META¶

Access transaction-level metadata (from posting context).

ENTRY_META(key) → MetaValue

ANY_META¶

Check if any metadata key matches pattern.

ANY_META(pattern) → Boolean