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

Note: The any filter must always be an array.

{
  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
      }
    }
  }
}

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"
        }
      }
    }
  }
}

Passing Each Criterion as a Filter

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

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
          }
        }
      }
    }
  }
}

Parameters:

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

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

String Filter Types

For string data:

• is
• not
• in
• notIn
• includes
• includesCaseInsensitive
• notIncludes
• notIncludesCaseInsensitive
• like
• likeCaseInsensitive
• notLike
• notLikeCaseInsensitive
• startsWith
• startsWithCaseInsensitive
• endsWith

Examples:

• % → Matches zero or more characters
• _ → Matches exactly one character

Date and Time Filter Types

• is
• not
• after
• since
• till
• before

Array Filter Types

• length
• includes
• excludes
• startsWith
• endsWith
• notStartsWith
• notEndsWith

Example:

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

Another example:

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

Filtering: Where vs selectWhere

The selectWhere parameter functions similarly to the HAVING clause in SQL.

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

This example filters based on aggregated sum.

Compare with:

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

where filters before aggregation, selectWhere filters after aggregation.