ContextQMD
Back to Cube

Query format in the REST (JSON) API

docs-mintlify/reference/core-data-apis/rest-api/query-format.mdx

1.6.4319.3 KB
Original Source

Queries to the REST (JSON) API are plain JavaScript objects, describing an analytics query. The basic elements of a query (query members) are measures, dimensions, and segments.

The query member format name is cube_name.member_name, for example the email dimension in the users cube would have the users.email name.

In the case of a dimension of the time type, a granularity could be optionally added to the name, in the following format: cube_name.time_dimension_name.granularity_name, e.g., stories.time.week. It can be one of the default granularities (e.g., year or week) or a custom granularity.

The Cube client also accepts an array of queries. By default, it will be treated as a Data Blending query type.

Query Properties

A Query has the following properties:

  • measures: An array of measures.
  • dimensions: An array of dimensions.
  • filters: An array of objects, describing filters. Learn about filters format.
  • timeDimensions: A convenient way to specify a time dimension with a filter. It is an array of objects in timeDimension format.
  • segments: An array of segments. A segment is a named filter, created in the data model.
  • limit: A row limit for your query.
  • total: If set to true, Cube will run a total query and return the total number of rows as if no row limit or offset are set in the query. The default value is false.
  • offset: The number of initial rows to be skipped for your query. The default value is 0.
  • order: An object, where the keys are measures or dimensions to order by and their corresponding values are either asc or desc. For time dimensions, a granularity can be optionally provided, e.g., orders.created_at.month. The order of the fields to order on is based on the order of the keys in the object. If not provided, the default ordering is applied. If an empty object ([]) is provided, no ordering is applied.
  • timezone: A time zone for your query. You can set the desired time zone in the TZ Database Name format, e.g., America/Los_Angeles.
  • renewQuery: If renewQuery is set to true, Cube will renew all refreshKey for queries and query results in the foreground. However, if the refreshKey (or refreshKey.every) doesn't indicate that there's a need for an update this setting has no effect. The default value is false.
<Info>

Cube provides only eventual consistency guarantee. Using a small refreshKey.every value together with renewQuery to achieve immediate consistency can lead to endless refresh loops and overall system instability.

</Info>
  • ungrouped: If set to true, Cube will run an ungrouped query.
  • joinHints: Query-time join hints, provided as an array of two-element arrays of cube names.
json
{
  "measures": ["stories.count"],
  "dimensions": ["stories.category"],
  "filters": [
    {
      "member": "stories.isDraft",
      "operator": "equals",
      "values": ["No"]
    }
  ],
  "timeDimensions": [
    {
      "dimension": "stories.time",
      "dateRange": ["2015-01-01", "2015-12-31"],
      "granularity": "month"
    }
  ],
  "limit": 100,
  "offset": 50,
  "order": {
    "stories.time": "asc",
    "stories.count": "desc"
  },
  "timezone": "America/Los_Angeles"
}

Default order

If the order property is not specified in the query, Cube sorts results by default using the following rules:

  • The first time dimension with a granularity, ascending. If no time dimension with a granularity exists...
  • The first measure, descending. If no measure exists...
  • The first dimension, ascending.

Alternative order format

Also you can control the ordering of the order specification, Cube support alternative order format - array of tuples:

json
{
  "order": [
      ["stories.time", "asc"],
      ["stories.count", "asc"]
    ]
  }
}

Filters Format

A filter is a JavaScript object with the following properties:

  • member: Dimension or measure to be used in the filter, for example: stories.isDraft. See below on difference between filtering dimensions vs filtering measures.
  • operator: An operator to be used in the filter. Only some operators are available for measures. For dimensions the available operators depend on the type of the dimension. Please see the reference below for the full list of available operators.
  • values: An array of values for the filter. Values must be of type String. If you need to pass a date, pass it as a string in YYYY-MM-DD format.

Filtering Dimensions vs Filtering Measures

Filters are applied differently to dimensions and measures.

When you filter on a dimension, you are restricting the raw data before any calculations are made. When you filter on a measure, you are restricting the results after the measure has been calculated.

Filters Operators

Only some operators are available for measures. For dimensions, the available operators depend on the type of the dimension.

equals

Use it when you need an exact match. It supports multiple values.

  • Applied to measures.
  • Dimension types: string, number, time.
json
{
  "member": "users.country",
  "operator": "equals",
  "values": ["US", "Germany", "Israel"]
}
<Info>

If you would like to check if a value is NULL, use the notSet operator instead.

</Info>

notEquals

The opposite operator of equals. It supports multiple values.

  • Applied to measures.
  • Dimension types: string, number, time.
json
{
  "member": "users.country",
  "operator": "notEquals",
  "values": ["France"]
}
<Info>

If you would like to check if a value is not NULL, use the set operator instead.

</Info>

contains

The contains filter acts as a wildcard case-insensitive LIKE operator. In the majority of SQL backends it uses ILIKE operator with values being surrounded by %. It supports multiple values.

  • Dimension types: string.
json
{
  "member": "posts.title",
  "operator": "contains",
  "values": ["serverless", "aws"]
}

notContains

The opposite operator of contains. It supports multiple values.

  • Dimension types: string.
json
{
  "member": "posts.title",
  "operator": "notContains",
  "values": ["ruby"]
}

This operator adds IS NULL check to include NULL values unless you add null to values. For example:

json
{
  "member": "posts.title",
  "operator": "notContains",
  "values": ["ruby", null]
}

startsWith

The startsWith filter acts as a case-insensitive LIKE operator with a wildcard at the end. In the majority of SQL backends, it uses the ILIKE operator with % at the end of each value. It supports multiple values.

  • Dimension types: string.
json
{
  "member": "posts.title",
  "operator": "startsWith",
  "values": ["ruby"]
}

notStartsWith

The opposite operator of startsWith.

endsWith

The endsWith filter acts as a case-insensitive LIKE operator with a wildcard at the beginning. In the majority of SQL backends, it uses the ILIKE operator with % at the beginning of each value. It supports multiple values.

  • Dimension types: string.
json
{
  "member": "posts.title",
  "operator": "endsWith",
  "values": ["ruby"]
}

notEndsWith

The opposite operator of endsWith.

gt

The gt operator means greater than and is used with measures or dimensions of type number.

  • Applied to measures.
  • Dimension types: number.
json
{
  "member": "posts.upvotes_count",
  "operator": "gt",
  "values": ["100"]
}

gte

The gte operator means greater than or equal to and is used with measures or dimensions of type number.

  • Applied to measures.
  • Dimension types: number.
json
{
  "member": "posts.upvotes_count",
  "operator": "gte",
  "values": ["100"]
}

lt

The lt operator means less than and is used with measures or dimensions of type number.

  • Applied to measures.
  • Dimension types: number.
json
{
  "member": "posts.upvotes_count",
  "operator": "lt",
  "values": ["10"]
}

lte

The lte operator means less than or equal to and is used with measures or dimensions of type number.

  • Applied to measures.
  • Dimension types: number.
json
{
  "member": "posts.upvotes_count",
  "operator": "lte",
  "values": ["10"]
}

set

Operator set checks whether the value of the member is not NULL. You don't need to pass values for this operator.

  • Applied to measures.
  • Dimension types: number, string, time.
json
{
  "member": "posts.author_name",
  "operator": "set"
}

notSet

An opposite to the set operator. It checks whether the value of the member is NULL. You don't need to pass values for this operator.

  • Applied to measures.
  • Dimension types: number, string, time.
json
{
  "member": "posts.author_name",
  "operator": "notSet"
}

inDateRange

<Warning>

From a pre-aggregation standpoint, inDateRange filter is applied as a generic filter. All pre-aggregation granularity matching rules aren't applied in this case. It feels like pre-aggregation isn't matched. However, pre-aggregation is just missing the filtered time dimension in dimensions list. If you want date range filter to match timeDimension please use timeDimensions dateRange instead.

</Warning>

The operator inDateRange is used to filter a time dimension into a specific date range. The values must be an array of timestamps in the ISO 8601 format, for example 2025-01-02 or 2025-01-02T03:04:05.067Z. If only one timestamp is specified, the filter would be set exactly to this timestamp.

There is a convenient way to use date filters with grouping - learn more about the timeDimensions property here

  • Dimension types: time.
json
{
  "member": "posts.time",
  "operator": "inDateRange",
  "values": ["2015-01-01", "2015-12-31"]
}

notInDateRange

<Warning>

From a pre-aggregation standpoint, notInDateRange filter is applied as a generic filter. All pre-aggregation granularity matching rules aren't applied in this case. It feels like pre-aggregation isn't matched. However, pre-aggregation is just missing the filtered time dimension in dimensions list.

</Warning>

An opposite operator to inDateRange, use it when you want to exclude specific timestamps. The values must be in the same format as for inDateRange.

  • Dimension types: time.
json
{
  "member": "posts.time",
  "operator": "notInDateRange",
  "values": ["2015-01-01", "2015-12-31"]
}

beforeDate

<Warning>

From a pre-aggregation standpoint, beforeDate filter is applied as a generic filter. All pre-aggregation granularity matching rules aren't applied in this case. It feels like pre-aggregation isn't matched. However, pre-aggregation is just missing the filtered time dimension in dimensions list.

</Warning>

Use it when you want to retrieve all results before some specific timestamp. The values should be an array of one element in the same format as for inDateRange.

  • Dimension types: time.
json
{
  "member": "posts.time",
  "operator": "beforeDate",
  "values": ["2015-01-01"]
}

beforeOrOnDate

<Warning>

From a pre-aggregation standpoint, beforeOrOnDate filter is applied as a generic filter. All pre-aggregation granularity matching rules aren't applied in this case. It feels like pre-aggregation isn't matched. However, pre-aggregation is just missing the filtered time dimension in dimensions list.

</Warning>

Use it when you want to retrieve all results before or on a specific timestamp. The values should be an array of one element in the same format as for inDateRange.

  • Dimension types: time.
json
{
  "member": "posts.time",
  "operator": "beforeOrOnDate",
  "values": ["2015-01-01"]
}

afterDate

<Warning>

From a pre-aggregation standpoint, afterDate filter is applied as a generic filter. All pre-aggregation granularity matching rules aren't applied in this case. It feels like pre-aggregation isn't matched. However, pre-aggregation is just missing the filtered time dimension in dimensions list.

</Warning>

The same as beforeDate, but is used to get all results after a specific timestamp. The values should be an array of one element in the same format as for inDateRange.

  • Dimension types: time.
json
{
  "member": "posts.time",
  "operator": "afterDate",
  "values": ["2015-01-01"]
}

afterOrOnDate

<Warning>

From a pre-aggregation standpoint, afterOrOnDate filter is applied as a generic filter. All pre-aggregation granularity matching rules aren't applied in this case. It feels like pre-aggregation isn't matched. However, pre-aggregation is just missing the filtered time dimension in dimensions list.

</Warning>

The same as beforeOrOnDate, but is used to get all results after or on a specific timestamp. The values should be an array of one element in the same format as for inDateRange.

  • Dimension types: time.
json
{
  "member": "posts.time",
  "operator": "afterOrOnDate",
  "values": ["2015-01-01"]
}

measureFilter

The measureFilter operator is used to apply an existing measure's filters to the current query.

This usually happens when you call ResultSet.drilldown(), which will return a query for the drill members. If the original query has a filter on a measure, that filter will be added as otherwise the drilldown query will lose that context. Not supported by pre-aggregations.

  • Applied to measures.
json
{
  "member": "Orders.count",
  "operator": "measureFilter"
}

Boolean logical operators

Filters can contain or and and logical operators. Logical operators have only one of the following properties:

  • or An array with one or more filters or other logical operators
  • and An array with one or more filters or other logical operators
json
{
  "or": [
    {
      "member": "visitors.source",
      "operator": "equals",
      "values": ["some"]
    },
    {
      "and": [
        {
          "member": "visitors.source",
          "operator": "equals",
          "values": ["other"]
        },
        {
          "member": "visitor_checkins.cards_count",
          "operator": "equals",
          "values": ["0"]
        }
      ]
    }
  ]
}

You can not put dimensions and measures filters in the same logical operator. When Cube generates a SQL query to the data source, dimension and measure filters are translated to expressions in WHERE and HAVING clauses, respectively.
In other words, dimension filters apply to raw (unaggregated) data and measure filters apply to aggregated data, so it's not possible to express such filters in SQL semantics.

Time Dimensions Format

Since grouping and filtering by a time dimension is quite a common case, Cube provides a convenient shortcut to pass a dimension and a filter as a timeDimension property.

  • dimension: Time dimension name.
  • dateRange: An array of dates with the following format YYYY-MM-DD or in YYYY-MM-DDTHH:mm:ss.SSS format. Values should always be local and in query timezone. Dates in YYYY-MM-DD format are also accepted. Such dates are padded to the start and end of the day if used in start and end of date range interval accordingly. Please note that for timestamp comparison, >= and <= operators are used. It requires, for example, that the end date range date 2020-01-01 is padded to 2020-01-01T23:59:59.999. If only one date is specified it's equivalent to passing two of the same dates as a date range. You can also pass a string with a relative date range, for example, last quarter.
  • compareDateRange: An array of date ranges to compare measure values. See compare date range queries for details.
  • granularity: A granularity for a time dimension. It can be one of the default granularities (e.g., year or week) or a custom granularity. If you don't provide a granularity, Cube will only perform filtering by a specified time dimension, without grouping.
json
{
  "measures": ["stories.count"],
  "timeDimensions": [
    {
      "dimension": "stories.time",
      "dateRange": ["2015-01-01", "2015-12-31"],
      "granularity": "month"
    }
  ]
}

You can use compare date range queries when you want to see, for example, how a metric performed over a period in the past and how it performs now. You can pass two or more date ranges where each of them is in the same format as a dateRange:

javascript
// ...
const resultSet = await cubeApi.load({
  measures: ["stories.count"],
  timeDimensions: [
    {
      dimension: "stories.time",
      compareDateRange: ["this week", ["2020-05-21", "2020-05-28"]],
      granularity: "month"
    }
  ]
})

Relative date range

You can also use a string with a relative date range in the dateRange property, for example:

json
{
  "measures": ["stories.count"],
  "timeDimensions": [
    {
      "dimension": "stories.time",
      "dateRange": "last week",
      "granularity": "day"
    }
  ]
}

Some of supported formats:

  • today, yesterday, or tomorrow
  • last year, last quarter, or last 360 days
  • next month or last 6 months (current date not included)
  • from 7 days ago to now or from now to 2 weeks from now (current date included)
<Info>

Cube uses the Chrono library to parse relative dates. Please refer to its documentation for more examples.

</Info>