Filtering

In most cases you do not need the full dataset, but just a portion, related to the entity or range you are interested in.

Filtering can be applied to queries and subscriptions:

• in query, filter defines what part of the dataset you need in results
• with subscription, filter also determine when the updated data will be sent to you. If the new data does not match filter, update will not be triggered

tip

Use filters in subscription for notification services on specific type of events matching filter criteria

Filters are defined on the cube element level (Blocks, Transactions, so on) as a where attribute.

Using the OR Condition

In certain cases, you might want to execute a query that filters results based on one condition OR another. This type of query can be particularly useful when you need to retrieve records that meet at least one of multiple criteria. This can be achieved with the any operator.

The below query for example, retrieves blocks from the Ethereum archive dataset where the block number is greater than 19111970 OR the transaction count within a block is greater than 100. You can run the query (here)[https://ide.bitquery.io/using-OR-condition-example-V2]

{
  EVM(dataset: archive, network: eth) {
    Blocks(
      where: {any: [{Block: {Number: {gt: "19111970"}}}, {Block: {TxCount: {gt: 100}}}]}
      limit: {count: 10}
    ) {
      Block {
        Bloom
        Date
        Time
        Root
        TxCount
      }
    }
  }
}

Examples

{
  EVM {
    Blocks(where: { Block: { GasUsed: { ge: "14628560" } } }) {
      Block {
        Number
      }
    }
  }
}

returns block numbers with gas used exceeding certain level. where attribute is structured, with the same levels as the query schema. This allows to build complex filters by combining criteria, as in the following example:

{
  EVM {
    Transactions(
      where: {
        Block: { GasUsed: { ge: "26000000" } }
        Transaction: { Gas: { ge: "16000000" } }
      }
    ) {
      Block {
        GasUsed
      }
      Transaction {
        Gas
      }
    }
  }
}

note

filters are combined by AND principles, result set is an intersection of all criteria defined in the where attribute

Dynamic Where Filter

You can pass the WHERE clause as a parameter to set dynamic conditions for filtering the response. In the below example, we are passing the WHERE clause as a parameter, where we use 'currency' as a filter.

query ($where: EVM_DEXTradeByToken_Filter) {
  EVM(dataset: archive) {
    DEXTradeByTokens(
      limit: {count: 10}
      where: $where
      orderBy: {descending: Block_Date}
    ) {
      Block {
        Date
      }
      sum(of: Trade_PriceInUSD)
    }
  }
}
<!-- Parameters -->
{
  "where": {
    "Trade": {
      "Currency": {
        "Symbol": {
          "is": "PEPE"
        }
      }
    }
  }
}

Note: This currently works only for chains on EAP.

Passing Each Criterion as a Filter

Each condition can be passed as a parameter to allow for highly customizable queries.

For example, in the below query:

query(
  $network: evm_network
  $mempool: Boolean
    $currency_filter: EVM_DEXTradeByToken_Input_Trade_Currency_InputType
  $amount_usd_filter: EVM_Amount_With_Decimals
  $price_usd_filter: OLAP_Float
  $price_assymetry_filter: OLAP_Float
) {
  EVM(network: $network mempool: $mempool) {
    DEXTradeByTokens(
      orderBy: {descending: Block_Number}
      limit: {count: 35}

      where: {

        Trade: {
          Currency: $currency_filter
          AmountInUSD: $amount_usd_filter
          PriceInUSD: $price_usd_filter
          PriceAsymmetry: $price_assymetry_filter
        }

      }

    ) {

      Block {
        Time
      }

      Transaction {
        Hash
      }

      Trade {
        Buyer
        Seller
        Amount
        AmountInUSD
        Currency {
          Symbol
          SmartContract
        }
        Price
        PriceInUSD
        PriceAsymmetry
        Side {
          Currency {
            SmartContract
            Symbol
          }
        }

      }

    }
  }
}
<!-- Parameter -->

{
  "network": "matic",
  "mempool": false,
  "currency_filter": { "SmartContract": { "is": "0x53e0bca35ec356bd5dddfebbd1fc0fd03fabad39"}},
  "amount_usd_filter": {"ge": "2000.0"},
  "price_usd_filter": {"ge": 13.59},
  "price_assymetry_filter": {"ge": 0.001}
}

we have the following filters:

• $currency_filter: Filters trades by the currency involved using criteria based on the smart contract address.
• $amount_usd_filter: Filters trades by the amount in USD.
• $price_usd_filter: Filters trades by the price of the currency in USD.
• $price_assymetry_filter: Filters trades by the price asymmetry value.

Filter Types

Depending on the data type of the element used in where filter, different operators can be applied.

Numeric Filter Types

For numeric data, the following operators applicable:

• eq equals to
• ne not equals to
• ge greater or equal
• gt greater than
• le less or equal
• lt less than

tip

If you need to define the range of value, use ge and le together:

          GasUsed: {
            ge: "26000000"
            le: "60000000"
          }

note

Almost all numeric values in blockchain lies above the 32-bit boundary defined for numbers in GraphQL and JSON. So the string values used instead to define any number with not limited precision.

String Filter Types

For string data, the following operators applicable:

• is equals to
• not not equals to

danger

not and ne filters do not prevent to query large amount of data, consider use them only with some other filters

Date and Time Filter Types

For date and timestamp data, the following operators applicable:

• is date equals to
• not date not equals to
• after after certain date (not including it)
• since after including date
• till before including date
• before before not including date

Array Filter Types

Array fields can be filtered using the following conditions:

• length condition on array length;
• includes if array include item defined by the condition;
• excludes if array exclude item defined by the condition;
• startsWith if array starts with the item defined by the condition;
• endsWith if array ends with the item defined by the condition;
• notStartsWith if array does not start with the item defined by the condition;
• notEndsWith if array does not end with the item defined by the condition;

note

Note that all conditions on items can be a list, they all applied to selecting the item in AND manner.

Example of the condition is the following:

{
  EVM {
    Calls(
      where: {
        Arguments: {
                    length: {eq: 2}
          includes: {
            Index: {eq: 0}
            Name: {is: "recipient"}
          }
        }
      }
      limit: {count: 10}) {
      Arguments {
        Index
        Name
        Type
      }
      Call {
        Signature {
          Signature
        }
      }
    }
  }
}

Filter selects calls which have 2 arguments, and the first argument name is "recipient"

Condition can combine conditions on the items:

Arguments: {
          includes: [
            {
            Index: {eq: 0}
            Name: {is: "recipient"}
            Value: {Address: {is: "0xa7f6ebbd4cdb249a2b999b7543aeb1f80bda7969"}}
           }
           {
            Name: {is: "amount"}
            Value: {BigInteger: {ge: "1000000000"}}
           }
          ]
        }
      }

It extends the previous example, selecting only calls that have all 4 conditions:

• the first argument named 'recipient'
• the first argument value of type address equal to '0xa7f6ebbd4cdb249a2b999b7543aeb1f80bda7969'
• any argument called "amount"
• argument named "amount" having value bigger than 1000000000

Filtering: Where vs selectWhere

The selectWhere parameter functions similarly to the HAVING clause in SQL, allowing users to filter on aggregated data.

To demonstrate the use of selectWhere, consider the following query that retrieves blocks from the EVM dataset and filters based on a transaction count greater than 1,500,000.

query {
  EVM(dataset: archive) {
    Blocks {
      Block {
        Date
      }
      sum(of: Block_TxCount selectWhere: {gt: "1500000"})
    }
  }
}

sum(of: Block_TxCount selectWhere: {gt: "1500000"}): Applies the sum aggregate function to Block_TxCount and filters for sums greater than 1,500,000.

In this example, the query retrieves block data and applies a filter on the sum of transaction counts to include only those blocks where the transaction count exceeds 1,500,000.

This is different from the where clause, which is used to filter the blocks based on the transaction count:

query {
  EVM(dataset: archive) {
    Blocks(where: {Block: {TxCount: {gt: 1500000}}}) {
      Block {
        Date
      }
    }
  }
}

• Blocks(where: {Block: {TxCount: {gt: 1500000}}}): Filters blocks directly where TxCount is greater than 1,500,000 before aggregation.

In this query, the where clause directly filters blocks with transaction counts greater than 1,500,000 before any aggregation occurs.