Filtering queries - Formance Documentation

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:

JSON

{
  "$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:

JSON

{ "$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:

JSON

{
  "$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:

JSON

{
  "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.

Using the `$in` operator#

The $in operator matches resources where a field equals any value in a provided list. This is useful when filtering by multiple specific values.

Instead of using multiple $or and $match filters:

JSON

{
  "$or": [
    { "$match": { "address": "account:1" } },
    { "$match": { "address": "account:2" } },
    { "$match": { "address": "account:3" } }
  ]
}

You can use the $in operator:

JSON

{
  "$in": {
    "address": ["account:1", "account:2", "account:3"]
  }
}

The $in operator only supports full addresses, not partial matches like account:. This is enforced at validation time.

Filtering operations reference#

Operations#

Name	Description
`$match`	Exact equality comparison
`$like`	SQL LIKE pattern matching with %. Applies on transactions.id, transactions.reference, logs.type, ledgers.name. On address-shaped fields it's treated as $match (segment-based, % ignored).
`$in`	Matches if field value is in the provided array
`$lt`	Less than comparison
`$gt`	Greater than comparison
`$lte`	Less than or equal comparison
`$gte`	Greater than or equal comparison
`$exists`	Checks if a key exists in a map field
`$and`	Logical AND - all conditions must be true
`$or`	Logical OR - at least one condition must be true

$like only does SQL LIKE on a few free-form string fields — transactions.id, transactions.reference, logs.type, ledgers.name. On address-shaped fields (address / account / source / destination) it's silently treated like $match; use the segment syntax above for partial-address matching, not %.

Filterable fields#

Depending on the resource you are querying, you can filter on different fields:

Accounts#

Field	Type	Operators
`address`	string	`$match` `$like` `$in`
`first_usage`	date	`$match` `$lt` `$gt` `$lte` `$gte`
`insertion_date`	date	`$match` `$lt` `$gt` `$lte` `$gte`
`updated_at`	date	`$match` `$lt` `$gt` `$lte` `$gte`
`balance`	numeric_map	`$match` `$lt` `$gt` `$lte` `$gte`
`balance[asset]`	numeric	`$match` `$lt` `$gt` `$lte` `$gte`
`metadata`	string_map	`$exists`
`metadata[key]`	string	`$match`

Transactions#

Field	Type	Operators
`id`	numeric	`$match` `$lt` `$gt` `$lte` `$gte`
`reference`	string	`$match` `$like` `$in`
`timestamp`	date	`$match` `$lt` `$gt` `$lte` `$gte`
`inserted_at`	date	`$match` `$lt` `$gt` `$lte` `$gte`
`updated_at`	date	`$match` `$lt` `$gt` `$lte` `$gte`
`reverted`	boolean	`$match`
`reverted_at`	date	`$match` `$lt` `$gt` `$lte` `$gte`
`account`	string	`$match` `$like` `$in`
`source`	string	`$match` `$like` `$in`
`destination`	string	`$match` `$like` `$in`
`metadata`	string_map	`$exists`
`metadata[key]`	string	`$match`

Volumes#

Field	Type	Operators
`address`	string	`$match` `$like` `$in`
`first_usage`	date	`$match` `$lt` `$gt` `$lte` `$gte`
`balance`	numeric_map	`$match` `$lt` `$gt` `$lte` `$gte`
`balance[asset]`	numeric	`$match` `$lt` `$gt` `$lte` `$gte`
`metadata`	string_map	`$exists`
`metadata[key]`	string	`$match`

Aggregated balances#

Field	Type	Operators
`address`	string	`$match` `$like` `$in`
`metadata`	string_map	`$exists`
`metadata[key]`	string	`$match`

Logs#

Field	Type	Operators
`date`	date	`$match` `$lt` `$gt` `$lte` `$gte`
`id`	numeric	`$match` `$lt` `$gt` `$lte` `$gte`
`type`	string	`$match` `$like` `$in`

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 the following query parameters:

Name	Type	Description
`pageSize`	integer	The maximum number of results to return per page.
`cursor`	string	Parameter used in pagination requests. Maximum page size is set to 15. Set to the value of next for the next page of results. Set to the value of previous for the previous page of results. No other parameters can be set when this parameter is set.
`endTime`	timestamp
`startTime`	timestamp
`insertionDate`	boolean	Use insertion date instead of effective date
`groupBy`	integer	Group volumes and balance by the level of the segment of the address

Example: Querying volumes up to a point in time#

curl -X GET $FORMANCE_API_URL/api/ledger/v2/my-ledger/volumes?endTime=2024-01-15T00:00:00Z

GET/api/ledger/v2/my-ledger/volumes

Example: Querying volumes within a time range#

curl -X GET $FORMANCE_API_URL/api/ledger/v2/my-ledger/volumes?startTime=2024-01-01T00:00:00Z&endTime=2024-01-31T23:59:59Z

GET/api/ledger/v2/my-ledger/volumes

When working with bi-temporality, pass insertionDate=true to filter the time range against each transaction's insertion timestamp rather than its effective (date) timestamp. The /volumes endpoint does not accept a pit query parameter — use endTime (optionally combined with insertionDate=true) to query the ledger state as it was at a given point in time.

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:

JSON

{
  "$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:

JSON

{ "$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:

JSON

{
  "$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"

Example#

Consider this transaction with multiple postings:

JSON

{
  "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.

Using the `$in` operator#

The $in operator matches resources where a field equals any value in a provided list. This is useful when filtering by multiple specific values.

Instead of using multiple $or and $match filters:

JSON

{
  "$or": [
    { "$match": { "address": "account:1" } },
    { "$match": { "address": "account:2" } },
    { "$match": { "address": "account:3" } }
  ]
}

You can use the $in operator:

JSON

{
  "$in": {
    "address": ["account:1", "account:2", "account:3"]
  }
}

The $in operator only supports full addresses, not partial matches like account:. This is enforced at validation time.

Filtering operations reference#

Operations#

Name	Description
`$match`	Exact equality comparison
`$like`	SQL LIKE pattern matching with %. Applies on transactions.id, transactions.reference, logs.type, ledgers.name. On address-shaped fields it's treated as $match (segment-based, % ignored).
`$in`	Matches if field value is in the provided array
`$lt`	Less than comparison
`$gt`	Greater than comparison
`$lte`	Less than or equal comparison
`$gte`	Greater than or equal comparison
`$exists`	Checks if a key exists in a map field
`$and`	Logical AND - all conditions must be true
`$or`	Logical OR - at least one condition must be true

Filterable fields#

Depending on the resource you are querying, you can filter on different fields:

Accounts#

Field	Type	Operators
`address`	string	`$match` `$like` `$in`
`first_usage`	date	`$match` `$lt` `$gt` `$lte` `$gte`
`insertion_date`	date	`$match` `$lt` `$gt` `$lte` `$gte`
`updated_at`	date	`$match` `$lt` `$gt` `$lte` `$gte`
`balance`	numeric_map	`$match` `$lt` `$gt` `$lte` `$gte`
`balance[asset]`	numeric	`$match` `$lt` `$gt` `$lte` `$gte`
`metadata`	string_map	`$exists`
`metadata[key]`	string	`$match`

Transactions#

Field	Type	Operators
`id`	numeric	`$match` `$lt` `$gt` `$lte` `$gte`
`reference`	string	`$match` `$like` `$in`
`timestamp`	date	`$match` `$lt` `$gt` `$lte` `$gte`
`inserted_at`	date	`$match` `$lt` `$gt` `$lte` `$gte`
`updated_at`	date	`$match` `$lt` `$gt` `$lte` `$gte`
`reverted`	boolean	`$match`
`reverted_at`	date	`$match` `$lt` `$gt` `$lte` `$gte`
`account`	string	`$match` `$like` `$in`
`source`	string	`$match` `$like` `$in`
`destination`	string	`$match` `$like` `$in`
`metadata`	string_map	`$exists`
`metadata[key]`	string	`$match`

Volumes#

Field	Type	Operators
`address`	string	`$match` `$like` `$in`
`first_usage`	date	`$match` `$lt` `$gt` `$lte` `$gte`
`balance`	numeric_map	`$match` `$lt` `$gt` `$lte` `$gte`
`balance[asset]`	numeric	`$match` `$lt` `$gt` `$lte` `$gte`
`metadata`	string_map	`$exists`
`metadata[key]`	string	`$match`

Aggregated balances#

Field	Type	Operators
`address`	string	`$match` `$like` `$in`
`metadata`	string_map	`$exists`
`metadata[key]`	string	`$match`

Logs#

Field	Type	Operators
`date`	date	`$match` `$lt` `$gt` `$lte` `$gte`
`id`	numeric	`$match` `$lt` `$gt` `$lte` `$gte`
`type`	string	`$match` `$like` `$in`

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 the following query parameters:

Name	Type	Description
`pageSize`	integer	The maximum number of results to return per page.
`cursor`	string	Parameter used in pagination requests. Maximum page size is set to 15. Set to the value of next for the next page of results. Set to the value of previous for the previous page of results. No other parameters can be set when this parameter is set.
`endTime`	timestamp
`startTime`	timestamp
`insertionDate`	boolean	Use insertion date instead of effective date
`groupBy`	integer	Group volumes and balance by the level of the segment of the address

Example: Querying volumes up to a point in time#

curl -X GET $FORMANCE_API_URL/api/ledger/v2/my-ledger/volumes?endTime=2024-01-15T00:00:00Z

GET/api/ledger/v2/my-ledger/volumes

Example: Querying volumes within a time range#

curl -X GET $FORMANCE_API_URL/api/ledger/v2/my-ledger/volumes?startTime=2024-01-01T00:00:00Z&endTime=2024-01-31T23:59:59Z

GET/api/ledger/v2/my-ledger/volumes