Query Syntax

Query Syntax Documentation for Ortege API

The Ortege API allows for flexible and powerful querying of blockchain data through various operators and syntax options. This section provides a reference for building queries to retrieve data efficiently and accurately.

---

Basic Query Structure

Each query consists of:

• Measures: Aggregated data points, such as counts or sums.

• Dimensions: Non-aggregated attributes for grouping and filtering.

• Time Dimensions: Time-based dimensions for filtering by date or time ranges.

• Filters: Conditions to include or exclude specific data points.

Example Query:

{
  "measures": ["stacks_pox_rewards.total_rewards"],
  "dimensions": ["stacks_pox_rewards.cycle"],
  "timeDimensions": [
    {
      "dimension": "stacks_pox_rewards.date",
      "dateRange": "last 30 days"
    }
  ],
  "filters": [
    {
      "dimension": "stacks_pox_rewards.status",
      "operator": "equals",
      "values": ["completed"]
    }
  ]
}

---

Operators

The filters parameter allows you to specify various operators to control how data is retrieved. Here are some common operators available in the Ortege API:

• equals: Matches values exactly.

  {
    "dimension": "bitcoin_l2_tx.type",
    "operator": "equals",
    "values": ["reward"]
  }

• notEquals: Excludes values that match exactly.

  {
    "dimension": "bitcoin_l2_tx.type",
    "operator": "notEquals",
    "values": ["change"]
  }

• contains: Matches values that contain a specific substring.

  {
    "dimension": "bitcoin_l2_tx.script_asm",
    "operator": "contains",
    "values": ["OP_RETURN"]
  }

• startsWith: Matches values that start with a specific substring.

  {
    "dimension": "stacks_pox_rewards.address",
    "operator": "startsWith",
    "values": ["SP"]
  }

• endsWith: Matches values that end with a specific substring.

  {
    "dimension": "stacks_pox_rewards.address",
    "operator": "endsWith",
    "values": ["111"]
  }

• gt (Greater Than): Matches values greater than a specified value.

  {
    "dimension": "bitcoin_l2_tx.block_number",
    "operator": "gt",
    "values": ["850000"]
  }

• gte (Greater Than or Equal To): Matches values greater than or equal to a specified value.

  {
    "dimension": "bitcoin_l2_tx.block_number",
    "operator": "gte",
    "values": ["850000"]
  }

• lt (Less Than): Matches values less than a specified value.

  {
    "dimension": "bitcoin_l2_tx.block_number",
    "operator": "lt",
    "values": ["860000"]
  }

• lte (Less Than or Equal To): Matches values less than or equal to a specified value.

  {
    "dimension": "bitcoin_l2_tx.block_number",
    "operator": "lte",
    "values": ["860000"]
  }

• inDateRange: Matches dates within a specified range.

  {
    "dimension": "stacks_pox_rewards.date",
    "operator": "inDateRange",
    "values": ["2024-01-01", "2024-12-31"]
  }

---

Combining Multiple Filters

You can apply multiple filters within a query to refine the data retrieved further. For example, to get PoX rewards from cycle 94 with amount values between 0.01 BTC and 1 BTC, you could use:

{
  "measures": ["stacks_pox_rewards.total_amount"],
  "dimensions": ["stacks_pox_rewards.cycle"],
  "filters": [
    {
      "dimension": "stacks_pox_rewards.cycle",
      "operator": "equals",
      "values": ["94"]
    },
    {
      "dimension": "stacks_pox_rewards.amount",
      "operator": "gte",
      "values": ["0.01"]
    },
    {
      "dimension": "stacks_pox_rewards.amount",
      "operator": "lte",
      "values": ["1"]
    }
  ]
}

---

Date Ranges and Granularity

Time Dimensions can be specified with custom date ranges and granularity, making it easy to group data by hour, day, month, etc.

Example using dateRange and granularity:

{
  "measures": ["bitcoin_l2_tx.amount"],
  "timeDimensions": [
    {
      "dimension": "bitcoin_l2_tx.date",
      "dateRange": "last 7 days",
      "granularity": "day"
    }
  ]
}

This example groups the amount measure by day over the last seven days.

---

Additional Resources

For more details on advanced query syntax and Cube’s query format, refer to the Cube documentation: Cube Query Format.