Runtime filters

Runtime filters

Runtime filters provide the ability to apply filters to a Liveboard or Answer by passing filter properties as query parameters in a Liveboard or visualization URL. You can also apply runtime filters in REST API requests when querying data from a Liveboard, Answer, or a visualization object. On embedded instances, you can use the Visual Embed SDK to apply filters to an embedded Liveboard or Answer object, and also update filters using events.

How runtime filters work🔗

The runtime filters can be specified in the ThoughtSpot object URL as query parameters. When you apply a runtime filter to a Liveboard, ThoughtSpot will try to find a matching column in the Liveboard visualizations to filter data. The runtime filter requires the following attribute-value pairs.

column name

String. Name of the column to filter by. For example, item type or product. This attribute is defined as col1, col2, col3 in the object URLs and REST API requests, and as columnName in the runtimeFilters array in the Visual Embed SDK.

operator

String. The runtime filter operator. For example, EQ or IN. In the Visual Embed SDK, operators are specified as operator in the runtimeFilters array, whereas in the object URLs and REST API requests, you can define the operator as op1, op2, and op3.

values

String, Integer, or Boolean. The list of operands. For example, if the column name is defined as State, the value can be the name of the state like Michigan.

This attribute is defined as val1, val2, val3 in the object URLs and REST API requests, and as values in the runtimeFilters array in the Visual Embed SDK.

Some operators like EQ and LE accept a single operand, whereas BW_INC_MAX, BW_INC_MIN, BW_INC, BW, and IN accept multiple operands.

Supported data types🔗

You can apply filters on these data types:

  • VARCHAR

  • BIGINT

  • INT

  • FLOAT

  • DOUBLE

  • BOOLEAN

  • DATE

  • DATE_TIME

  • TIME

Important

For the DATE and DATE_TIME data types, you must provide the date and time values in the Epoch time format. The Epoch time is also referred to as POSIX or Unix time. Epoch time is an integer value representing the number of seconds elapsed since 1 JAN 1970 00:00:00 UTC. You may have to convert time zones to calculate the appropriate timestamp.

For example, if you want to filter data for 2020-05-22, you can specify the date value in the Epoch time format as 1590192000, or use the following JavaScript method to calculate the timestamp:

new Date('2020-05-22').getTime() / 1000

Supported filter operators🔗

OperatorDescriptionNumber of Values

EQ

equals

1

NE

Not equal to

1

LT

less than

1

LE

less than or equal to

1

GT

greater than

1

GE

greater than or equal to

1

CONTAINS

contains

2

BEGINS_WITH

begins with

1

ENDS_WITH

ends with

1

BW_INC_MAX

between inclusive of the higher value

2

BW_INC_MIN

between inclusive of the lower value

2

BW_INC

between inclusive

2

BW

between non-inclusive

2

IN

is included in this list of values

multiple

NOT_IN

is not included in this list of values

multiple

Apply runtime filters on embedded objects🔗

If you are embedding a Liveboard or visualization without using the Visual Embed SDK, you can append the filters in the embedded object URL as shown in these examples:

https://{ThoughtSpot-Host}/?embedApp=true&col1=State&op1=EQ&val1=michigan#/embed/viz/{Liveboard_id}/{visualization_id}
https://{ThoughtSpot-Host}/?embedApp=true&col1=State&op1=EQ&val1=michigan&col2=product&op2=BEGINS_WITH&val2=Travel#/embed/viz/{Liveboard_id}/{visualization_id}

Runtime filters in Visual Embed SDK🔗

If you are embedding a Liveboard, visualization, or Answer using Visual Embed SDK, you can apply filters using the runtimeFilters property. In the full app embed mode, ThoughtSpot applies runtime filters on all Liveboard, visualization, and Answer objects in the embedded app.

The following example shows how to apply runtime filters on an embedded visualization in the SDK. Here, the runtime filter is operating on the Revenue column to filter the data matching 100000.

liveboardEmbed.render({
    liveboardId: '133e6c5f-e522-41a0-b0ad-b9c3b066e276',
    vizId: '28b73b4a-1341-4535-ab71-f76b6fe7bf92',
      runtimeFilters: [{
      columnName: 'Revenue',
      operator: RuntimeFilterOp.EQ,
      values: ['100000' ]
      }]
  });

Apply multiple runtime filters in the SDK🔗

The following examples show how to apply multiple runtime filters on Liveboard visualizations using the SDK:

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
            columnName: 'item type', // eg: color
            operator: RuntimeFilterOp.EQ,
            values: ['Jackets'] // eg: red
        },
        {
            columnName: 'Region',
            operator: RuntimeFilterOp.IN,
            values: ['Midwest', 'East', 'West']
        },
        {
            columnName: 'Date',
            operator: RuntimeFilterOp.EQ,
            values: ['1656680400']
        }
    ]
});

AND and OR conditions in filters🔗

If the Liveboard or Answer already has one or more filters applied, runtime filters will act as an AND condition. This means that all filter conditions, including those supplied in the runtime filters and Liveboard filter, must match to get the desired data.

In the following example, the OR condition is applied; That is, if at least one condition matches, the Liveboard returns data.

Example for OR condition
runtimeFilters: [{
    columnName: 'product name',
    operator: RuntimeFilterOp.CONTAINS,
    values: ['bag', 'jackets']
}]

However, when multiple runtime filters are applied, or when the Liveboard already has a filter applied, the data must match all filter conditions.

Example for AND condition
runtimeFilters: [{
        columnName: 'product name',
        operator: RuntimeFilterOp.CONTAINS,
        values: ['vest']
    },
    {
        columnName: 'product name',
        operator: RuntimeFilterOp.CONTAINS,
        values: ['hoody']
    }
]

Runtime filter operator examples🔗

OperatorExample (Visual Embed SDK)

EQ
equals
Number of values allowed: 1

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
        columnName: 'state',
        operator: RuntimeFilterOp.EQ,
        values: ['california']
    }]
});

NE
Not exactly or Not equal to
Number of values allowed: 1

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
        columnName: 'item type',
        operator: RuntimeFilterOp.NE,
        values: ['jackets']
    }]
});

LT
less than
Number of values allowed: 1

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
        columnName: 'revenue',
        operator: RuntimeFilterOp.LT,
        values: ['1000000']
    }]
});

LE
less than or equal to
Number of values allowed: 1

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
        columnName: 'revenue',
        operator: RuntimeFilterOp.LE,
        values: ['5000000']
    }]
});

GT
greater than
Number of values allowed: 1

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
        columnName: 'revenue',
        operator: RuntimeFilterOp.GT,
        values: ['1000000']
    }]
});

GE
greater than or equal to
Number of values allowed: 1

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
        columnName: 'revenue',
        operator: RuntimeFilterOp.GE,
        values: ['5000000']
    }]
});

CONTAINS
contains
Number of values allowed: 2

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
        columnName: 'item type',
        operator: RuntimeFilterOp.CONTAINS,
        values: ['Bags']
    }]
});

See also, AND/OR conditions in filters, for information about the AND and OR condition for filters.

BEGINS_WITH
begins with
Number of values allowed: 1

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
     runtimeFilters: [{
          columnName: 'product',
          operator: RuntimeFilterOp.BEGINS_WITH,
          values: ['travel']
     }],

});

ENDS_WITH
ends with
Number of values allowed: 1

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
        columnName: 'item type',
        operator: RuntimeFilterOp.ENDS_WITH,
        values: ['shirts']
    }]
});

BW_INC_MAX
between inclusive of the higher value
Number of values allowed: 2

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
        columnName: 'revenue',
        operator: RuntimeFilterOp.BW_INC_MAX,
        values: ['25','30']
    }]
});

BW_INC_MIN
between inclusive of the lower value
Number of values allowed: 2

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
        columnName: 'revenue',
        operator: RuntimeFilterOp.BW_INC_MIN,
        values: ['25','50']
    }]
});

BW_INC
between inclusive
Number of values allowed: 2

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
        columnName: 'sales',
        operator: RuntimeFilterOp.BW_INC,
        values: ['10','50']
    }]
});

BW
between non-inclusive
Number of values allowed: 2

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
        columnName: 'sales',
        operator: RuntimeFilterOp.BW,
        values: ['25','50']
    }]
});

IN
is included in this list of values
Number of values allowed: multiple

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
        columnName: 'item type',
        operator: RuntimeFilterOp.IN,
        values: ['jackets', 'bags', 'shirts']
    }]
});

NOT_IN
is not included in this list of values

liveboardEmbed.render({
    liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
    runtimeFilters: [{
        columnName: 'item type',
        operator: RuntimeFilterOp.NOT_IN,
        values: ['skirts', 'bags']
    }]
});

Example video🔗

The following video shows how to apply multiple runtime filters on a Liveboard.

Copy sample code Try it out in Playground

Adjust runtime filters using SDK events🔗

Runtime filters can be set prior to the load within the configuration object of the loaded embed component:

 const embed = new LiveboardEmbed('#embed-container', {
     ... // other options
     runtimeFilters: [{
         columnName: 'value',
         operator: RuntimeFilterOp.EQ,
         values: ['string' | 123 | true],
     }, ],
 })

After loading the embedded object, runtime filters can be adjusted using the HostEvent.UpdateRuntimeFilters event:

liveboardEmbed.trigger(HostEvent.UpdateRuntimeFilters, [{
        columnName: "state",
        operator: RuntimeFilterOp.EQ,
        values: ["michigan"]
    },
    {
        columnName: "item type",
        operator: RuntimeFilterOp.EQ,
        values: ["Jackets"]
    }
])

For more information, see UpdateRuntimeFilters and Filters in embedded UI.

URL format when embedding without SDK🔗

If embedding a ThoughtSpot Liveboard without the SDK, ensure that add the runtime filters before #/path in the URL as shown in the following example:

https://{ThoughtSpot-Host}/?embedApp=true&col1=State&op1=EQ&val1=michigan#/embed/viz/{Liveboard_id}/{visualization_id}

For more information, see Embed without SDK.

Apply runtime filters via REST APIs🔗

REST API v1 endpoints🔗

To apply runtime filters on a Liveboard object in a REST API request, add the runtime filters to the API request URL as shown here:

URL format
https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/pinboarddata?id={Liveboard_id}&col1={column-name}&op1={operator}&val1={value}
Example
https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/pinboarddata?id=e36ee65e-64be-436b-a29a-22d8998c4fae&col1=State&op1=EQ&val1=California

The following example shows how to apply a runtime filter on a visualization object of a Liveboard:

URL format
https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/pinboarddata?id={Liveboard_id}&vizid={visualization_id}&col1={column-name}&op1={operator}&val1={value}
Example
https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/pinboarddata?id=543619d6-0015-4667-b257-eff547d13a12&vizid=%5B%224ff5b939-453d-40ff-8fc2-a1d972047c86%22%5D&col1=State&op1=EQ&val1=California

The following is another example of a REST API request URL with a filter. Here the runtime filter is operating on the column Category and returning values that are equal to mfgr%2324.

https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/pinboarddata?
id=e36ee65e-64be-436b-a29a-22d8998c4fae&col1=Category
&op1=EQ&val1=mfgr%2324

Apply additional filters🔗

You can add additional filters by incrementing the number at the end of each parameter in the runtime filter for each filter you add, for example, col2, op2, val2, and so on. To add additional filters on a particular column, you can specify multiple values by separating them with an ampersand (&) as shown in the example here:

val1=foo&val1=bar

You can also use the IN operator for multiple values, as shown in this example:

col1=<column_name>&op1=IN&val1=<value>&val1=<value>

The following example passes multiple variables to a single column as well as multiple columns. It shows that the data values are returned as epochs.

col1=region&op1=IN&val1=midwest&val1=south&val1=northeast&col2=date&op2=BET&val2=<epoch_start>&val2=<epoch_end>

REST API v2.0 endpoints🔗

The v2.0 Data and Report endpoints support runtime filters in REST API requests. Note that you can add additional filters by incrementing the number at the end of each parameter: for example, col2, op2, val2. Some operators, such as CONTAINS and IN, allow passing multiple values in the val attribute.

Report APIs🔗

  • POST /api/rest/2.0/report/liveboard

    Allows downloading Liveboard data in PDF, XLSX, CSV, and PNG format.

    curl -X POST \
  --url 'https://{ThoughtSpot-Host}/api/rest/2.0/report/liveboard' \
  -H 'Authorization: Bearer {access-token} '\
  -H 'Content-Type: application/json' \
  --data-raw '{
  "metadata_identifier": "0c68a0a1-930b-4ba0-b7a0-59ea49b09848",
  "file_format": "PDF",
  "runtime_filter": {
    "col1": "item type",
    "op1": "CONTAINS",
    "val1": "Shirts",
    "col2": "region",
    "op2": "EQ",
    "val2": "West",
    "col3": "state",
    "op3": "IN",
    "val3": [
      "California",
      "Nevada"
    ]
  }
}'

  • POST /api/rest/2.0/report/answer

    Allows downloading Answer data in PDF, XLSX, CSV, and PNG format.

    curl -X POST \
  --url 'https://{ThoughtSpot-Host}/api/rest/2.0/report/answer' \
  -H 'Authorization: Bearer {access-token} '\
  -H 'Content-Type: application/json' \
  --data-raw '{
  "metadata_identifier": "fa68ae91-7588-4136-bacd-d71fb12dda69",
  "file_format": "XLSX",
  "runtime_filter": {
    "col1": "item type",
    "op1": "CONTAINS",
    "val1": [
      "Bags",
      "Shirts"
    ],
    "col2": "state",
    "op2": "EQ",
    "val2": "California"
  }
}'

Data APIs🔗

  • POST /api/rest/2.0/searchdata

    Allows searching data from a specific data source object.

    curl -X POST \
  --url 'https://{ThoughtSpot-Host}/api/rest/2.0/searchdata' \
  -H 'Authorization: Bearer {access-token}' \
  -H 'Accept: application/json'\
  -H 'Content-Type: application/json' \
  --data-raw '{
  "query_string": "[sales] [item type]",
  "logical_table_identifier": "cd252e5c-b552-49a8-821d-3eadaa049cca",
  "data_format": "COMPACT",
  "record_offset": 0,
  "record_size": 10,
  "runtime_filter": {
    "col1": "item type",
    "op1": "EQ",
    "val1": "Bags"
  }
}'

  • POST /api/rest/2.0/metadata/liveboard/data
    Gets data from a Liveboard.

    curl -X POST \
  --url 'https://{ThoughtSpot-Host}/api/rest/2.0/metadata/liveboard/data' \
  -H 'Authorization: Bearer {access-token}'\
  -H 'Accept: application/json'\
  -H 'Content-Type: application/json' \
  --data-raw '{
  "metadata_identifier": "3123341c-8ad4-43ad-8c94-52d75b96d8fb",
  "data_format": "COMPACT",
  "record_offset": 0,
  "record_size": 10,
  "runtime_filter": {
    "col1": "type",
    "op1": "EQ",
    "val1": "roasted",
    "col2": "tea",
    "op2": "EQ",
    "val2": "barley"
  }
}'

  • POST /api/rest/2.0/metadata/answer/data
    Gets data from a saved Answer.

    curl -X POST \
  --url 'https://{ThoughtSpot-Host}/api/rest/2.0/metadata/answer/data' \
  -H 'Authorization: Bearer {access-token}'\\
  -H 'Accept: application/json'\
  -H 'Content-Type: application/json' \
  --data-raw '{
  "metadata_identifier": "36ec5bde-e0c9-4a2b-9dc4-64758fac21d6",
  "data_format": "COMPACT",
  "record_offset": 0,
  "record_size": 10,
  "runtime_filter": {
    "col1": "type",
    "op1": "EQ",
    "val1": "roasted",
    "col2": "tea",
    "op2": "EQ",
    "val2": "mint"
  }
}'

Apply runtime filters on a Liveboard or visualization URL🔗

The following examples show the runtime filter query string in a Liveboard URL:

https://{ThoughtSpot-Host}/?col1=State&op1=EQ&val1=California#/pinboard/d084c256-e284-4fc4-b80c-111cb606449a

You can apply multiple filters in the same URL as shown in this example:

https://{ThoughtSpot-Host}/?col1=State&op1=EQ&val1=California&col2=product&op2=BEGINS_WITH&val2=Travel#/pinboard/d084c256-e284-4fc4-b80c-111cb606449a

You can also apply multiple filters with multiple values as shown in this example:

https://<thoughtspot-host>/?col1=State&op1=IN&val1=California&val1=Michigan&col2=item%20type&op2=EQ&val2=Jackets#/pinboard/d084c256-e284-4fc4-b80c-111cb606449a
Important

  • The runtime filters operation returns an error if the URL exceeds 2000 characters.

  • Ensure that you add the runtime filter parameters in the URL before #/<path> in the URL, so that the parameters persist.

  • Attempting to override existing filter values with runtime filters while exporting a Liveboard will result in an error.

Limitations of runtime filters🔗

  • The DATE and DATE_TIME data types must be specified as EPOCH time (Unix or POSIX time) in runtime filters.

  • Runtime filters work only on Answers and Liveboard visualizations built from Worksheets. Runtime filters on visualizations and Answers built directly from Tables, Views, and SQL Views do not work because the possibility of multiple join paths and join path choice is not supported as input in runtime filters.

  • Runtime filters do not allow you to apply HAVING filters in the URL parameters.