Filtering queries

Ledger V2 comes with a number of operations allowing you to list resources such as transactions or accounts. In some cases, you may want to filter the results to only include resources that match certain criteria. This is where filtering comes in.

Filtering syntax

Filtering is done either:

by using a query parameter named query and containing a JSON object describing the filter criteria.
by sending the filter criteria as a JSON object in the body of the request.

Here is a value example:

{
  "$match": { "source": "order::pending" }
}

In this example, we are filtering on a ledger account. The naming convention set on this account is the following: order:XXXX:pending In order to get all accounts, with unique value XXXX of order segment, we use this regex-like syntax order::pending.

An address is divided into segments delimited by colons :. You can use wildcards for partial address matching:

Empty segment matches any single segment: order::pending matches order:123:pending, order:456:pending, etc.
Trailing colon matches any remaining segments: order: matches order:123, order:123:pending, etc.
Trailing ... explicitly matches any remaining segments: wallet:user123:... matches wallet:user123:main, wallet:user123:pending:hold, etc.

For a metadata filter, the syntax is the following:

{ "$match": { "metadata[foo]": "bar"} }

In this example, we are filtering on a metadata field named foo with the value bar.

Combining filters

The root of the filter object has always a single key. This key is the operation to apply to the filter. In order to combine filters, you can use the $and and $or operations. Here is an example:

{
  "$and": [
    { "$match": { "source": "order::pending" } },
    { "$match": { "metadata[foo]": "bar" } }
  ]
}

In this example, we are combining two filters using the $and operation. The result will only include resources that match both filters.

Cross-posting filter behavior

When filtering transactions based on posting fields (like source and destination addresses), filters apply across all postings within a transaction, not just individual postings.

How it works

If you filter for transactions where:

source = "world" AND
destination = "user:123"

The transaction will match if any of its postings have source="world" AND any of its postings have destination="user:123". The source and destination don’t need to be in the same posting.

Example

Consider this transaction with multiple postings:

{
  "postings": [
    {
      "source": "world",
      "destination": "order:abc:payment",
      "amount": 100,
      "asset": "USD/2"
    },
    {
      "source": "order:abc:payment",
      "destination": "platform:fee",
      "amount": 1,
      "asset": "USD/2"
    },
    {
      "source": "order:abc:payment",
      "destination": "user:123",
      "amount": 99,
      "asset": "USD/2"
    }
  ]
}

This transaction would match the filter source="world" AND destination="user:123" because:

The first posting has source="world" (matches source filter)
The third posting has destination="user:123" (matches destination filter)

Even though no single posting has both source="world" AND destination="user:123", the transaction matches because the criteria are met across multiple postings.

This behavior can lead to unexpected results when filtering multi-posting transactions. If you need to match source and destination within the same posting, you may need to query at a more granular level or use additional criteria.

Filtering operations reference

Operations

The following operations are supported:

$and/$or: Takes expression arrays as parameters.
$match: Equality operator. Takes an object containing a single key/value pair as parameter.
$lt: Lower. Same format as $match.
$lte: Lower or equal. Same format as $match.
$gt: Greater. Same format as $match.
$gte: Greater than or equal to. Same format as $match.
$exists: test the existence of a metadata
$not: Inverse the result of the expression. It takes an expression as parameter and returns the opposite decision. To invert the result of a $match operation, you can use the $not operation like this:
```
{
  "$not": { "$match": { "source": "order::pending" } }
}
```

Filterable fields

Depending on the resource you are querying, you can filter on different fields. Here are the exhaustive list of fields you can filter on:

Accounts

address: The address of the account.
first_usage: The timestamp of when the account was first used. You can use the $lt, $lte, $gt, $gte, or $match operators to filter on this field.
metadata[XXX]: The metadata field XXX of the account. You can use the $exists operator to test the existence of a metadata field, or the $match operator to filter on the value of a metadata field.
metadata: The account metadata. You can use the $exists operator to test whether the account has metadata or not.
balance[ASSET]: The balance of the account for the asset ASSET. You can use the $lt, $lte, $gt, $gte, or $match operators to filter on the balance.
insertion_date: The timestamp of when the account was inserted in the ledger.
updated_at: The timestamp of when the account was last updated in the ledger.

Transactions

id: The ID of the transaction
reference: The reference of the transaction.
timestamp: The timestamp of the transaction. Note that it is the time at which the transaction has occurred, not the time at which it has been recorded in the ledger. See the reference documentation about bi-temporality for more information.
reverted: Whether the transaction has been reverted or not. You can use the $match with the value true or false to filter on this field. Compensatory transactions created by reverts have the metadata key com.formance.spec/state/reverts. See reverting transactions for more details.
reverted_at: The timestamp of the reversal
account: Either the source or the destination of the transaction. You can use the $match operator to filter on this field.
source: The source account address of the transaction.
destination: The destination account address of the transaction.
metadata[XXX]: The metadata field XXX of the transaction. You can use the $exists operator to test the existence of a metadata field, or the $match operator to filter on the value of a metadata field.
metadata: The transaction metadata. You can use the $exists operator to test whether the transaction has metadata or not.
inserted_at: The timestamp of when the transaction was inserted in the ledger.
updated_at: The timestamp of when the transaction was last updated in the ledger.

Volumes

account: The account address of the volume. You can use the $match operator to filter on this field.
first_usage: The timestamp of when the account was first used. You can use the $lt, $lte, $gt, $gte, or $match operators to filter on this field.
metadata[XXX]: The metadata field XXX of the volume. You can use the $exists operator to test the existence of a metadata field, or the $match operator to filter on the value of a metadata field.
metadata: The volume metadata. You can use the $exists operator to test whether the volume has metadata or not.
balance[ASSET]: The balance of the volume for the asset ASSET. You can use the $lt, $lte, $gt, $gte, or $match operators to filter on the balance.

Using the Volumes Endpoint

For performance, consider using the /volumes endpoint instead of /transactions when you only need balance information. The volumes endpoint is optimized for aggregate balance queries.

The volumes endpoint supports additional query parameters for time-based filtering:

Parameter	Description
`pit`	Point In Time - retrieve volumes as they were at this timestamp
`startTime`	Filter volumes from this timestamp
`endTime`	Filter volumes until this timestamp
`groupBy`	Group volumes by a specific field (e.g., account segment)

Example: Querying volumes at a point in time

GET /api/ledger/v2/{ledger}/volumes?pit=2024-01-15T00:00:00Z

Example: Querying volumes within a time range

GET /api/ledger/v2/{ledger}/volumes?startTime=2024-01-01T00:00:00Z&endTime=2024-01-31T23:59:59Z

When working with bi-temporality, the pit parameter respects transaction timestamps, not insertion timestamps. This allows you to query the ledger state as it was at any point in time.

Accounting Model

Working with the ledger

Example Implementations

Advanced topics

Filtering syntax

Combining filters

Cross-posting filter behavior

How it works

Example

Filtering operations reference

Operations

Filterable fields

Accounts

Transactions

Volumes

Using the Volumes Endpoint

Example: Querying volumes at a point in time

Example: Querying volumes within a time range

Accounting Model

Working with the ledger

Example Implementations

Advanced topics

​Filtering syntax

​Combining filters

​Cross-posting filter behavior

​How it works

​Example

​Filtering operations reference

​Operations

​Filterable fields

​Accounts

​Transactions

​Volumes

​Using the Volumes Endpoint

​Example: Querying volumes at a point in time

​Example: Querying volumes within a time range

Filtering syntax

Combining filters

Cross-posting filter behavior

How it works

Example

Filtering operations reference

Operations

Filterable fields

Accounts

Transactions

Volumes

Using the Volumes Endpoint

Example: Querying volumes at a point in time

Example: Querying volumes within a time range