Runtime filters

Table of Contents

Overview
Runtime filters in Visual Embed SDK
Runtime filters via REST APIs
- REST API v2.0 endpoints
- REST API v1 endpoints
Apply runtime filters on a Liveboard or visualization URL
Limitations of runtime filters

Runtime filters allow you to apply filters on a Liveboard, Answer, visualization, or conversation session with Spotter. These filters are not persistent filters saved in the object, but are applied dynamically at runtime. You can use runtime filters to pre-filter content for your application users based on context and embedding app’s requirements.

Overview🔗

Runtime filters can be applied using one of the following options:

Via Visual Embed SDK
Use the runtimeFilters property in the Visual Embed SDK for embedded objects.
The SDK also provides host events to update filters.
Via REST APIs Use REST API requests to apply runtime overrides on Liveboard, Answer, or visualization data.
Via URL query parameters
Pass filter properties as query parameters in the URL.

Important

The runtime filters operation returns an error if the URL exceeds 2000 characters.
Attempting to override existing filter values with runtime filters while exporting a Liveboard will result in an error.
If a Model has a column alias, do not use the alias in runtime filters. Instead, use the underlying base column name.

Runtime filter properties🔗

A runtime filter consists of the following attributes:

column name: String. Name of the column to filter on. 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 values to filter by. 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🔗

Operator Description Number of Values

Operator	Description	Number 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

EQ

equals

NE

Not equal to

LT

less than

LE

less than or equal to

GT

greater than

GE

greater than or equal to

CONTAINS

contains

BEGINS_WITH

begins with

ENDS_WITH

ends with

BW_INC_MAX

between inclusive of the higher value

BW_INC_MIN

between inclusive of the lower value

BW_INC

between inclusive

BW

between non-inclusive

IN

is included in this list of values

multiple

NOT_IN

is not included in this list of values

multiple

Runtime filters in Visual Embed SDK🔗

If you are embedding Spotter, Liveboard, visualization or the full application 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.

const liveboardEmbed = new LiveboardEmbed('#tsEmbed', {
    ... // other embed view config
    liveboardId: '133e6c5f-e522-41a0-b0ad-b9c3b066e276',
    vizId: '28b73b4a-1341-4535-ab71-f76b6fe7bf92',
    runtimeFilters: [{
        columnName: 'Revenue',
        operator: RuntimeFilterOp.EQ,
        values: ['100000']
    }]
});

In the following example, runtime filters are applied on a Spotter conversation session with a pre-defined search query string:

const spotterEmbed = new SpotterEmbed('#tsEmbed', {
    ... // other embed view config
    worksheetId: "cd252e5c-b552-49a8-821d-3eadaa049cca",
    searchOptions: {
        searchQuery: 'sales data for west coast',
    },
    runtimeFilters: [{
        columnName: 'Item type',
        operator: RuntimeFilterOp.EQ,
        values: ['jackets']
    }]
});

Spotter applies the specified filters to the data and returns an answer for the initial query passed in the embed code. Unless the filters are explicitly overridden, runtime filters will continue to be applied to all answers generated for subsequent queries within the same conversation session.

Applying multiple runtime filters🔗

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

const liveboardEmbed = new LiveboardEmbed('#tsEmbed', {
    ... // other embed view config
    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🔗

Operator Example (Visual Embed SDK)

Operator	Example (Visual Embed SDK)
`EQ` equals Number of values allowed: 1	`runtimeFilters: [{ columnName: 'state', operator: RuntimeFilterOp.EQ, values: ['california'] }]`
`NE` Not exactly or Not equal to Number of values allowed: 1	`runtimeFilters: [{ columnName: 'item type', operator: RuntimeFilterOp.NE, values: ['jackets'] }]`
`LT` less than Number of values allowed: 1	`runtimeFilters: [{ columnName: 'revenue', operator: RuntimeFilterOp.LT, values: ['1000000'] }]`
`LE` less than or equal to Number of values allowed: 1	`runtimeFilters: [{ columnName: 'revenue', operator: RuntimeFilterOp.LE, values: ['5000000'] }]`
`GT` greater than Number of values allowed: 1	`runtimeFilters: [{ columnName: 'revenue', operator: RuntimeFilterOp.GT, values: ['1000000'] }]`
`GE` greater than or equal to Number of values allowed: 1	`runtimeFilters: [{ columnName: 'revenue', operator: RuntimeFilterOp.GE, values: ['5000000'] }]`
`CONTAINS` contains Number of values allowed: 2	`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	`runtimeFilters: [{ columnName: 'product', operator: RuntimeFilterOp.BEGINS_WITH, values: ['travel'] }],`
`ENDS_WITH` ends with Number of values allowed: 1	`runtimeFilters: [{ columnName: 'item type', operator: RuntimeFilterOp.ENDS_WITH, values: ['shirts'] }]`
`BW_INC_MAX` between inclusive of the higher value Number of values allowed: 2	`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	`runtimeFilters: [{ columnName: 'revenue', operator: RuntimeFilterOp.BW_INC_MIN, values: ['25', '50'] }]`
`BW_INC` between inclusive Number of values allowed: 2	`runtimeFilters: [{ columnName: 'sales', operator: RuntimeFilterOp.BW_INC, values: ['10', '50'] }]`
`BW` between non-inclusive Number of values allowed: 2	`runtimeFilters: [{ columnName: 'sales', operator: RuntimeFilterOp.BW, values: ['25', '50'] }]`
`IN` is included in this list of values Number of values allowed: multiple	`runtimeFilters: [{ columnName: 'item type', operator: RuntimeFilterOp.IN, values: ['jackets', 'bags', 'shirts'] }]`
`NOT_IN` is not included in this list of values	`runtimeFilters: [{ columnName: 'item type', operator: RuntimeFilterOp.NOT_IN, values: ['skirts', 'bags'] }]`

EQ
equals
Number of values allowed: 1

 runtimeFilters: [{
     columnName: 'state',
     operator: RuntimeFilterOp.EQ,
     values: ['california']
 }]

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

 runtimeFilters: [{
     columnName: 'item type',
     operator: RuntimeFilterOp.NE,
     values: ['jackets']
 }]

LT
less than
Number of values allowed: 1

 runtimeFilters: [{
     columnName: 'revenue',
     operator: RuntimeFilterOp.LT,
     values: ['1000000']
 }]

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

 runtimeFilters: [{
     columnName: 'revenue',
     operator: RuntimeFilterOp.LE,
     values: ['5000000']
 }]

GT
greater than
Number of values allowed: 1

 runtimeFilters: [{
     columnName: 'revenue',
     operator: RuntimeFilterOp.GT,
     values: ['1000000']
 }]

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

 runtimeFilters: [{
     columnName: 'revenue',
     operator: RuntimeFilterOp.GE,
     values: ['5000000']
 }]

CONTAINS
contains
Number of values allowed: 2

 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

 runtimeFilters: [{
     columnName: 'product',
     operator: RuntimeFilterOp.BEGINS_WITH,
     values: ['travel']
 }],

ENDS_WITH
ends with
Number of values allowed: 1

 runtimeFilters: [{
     columnName: 'item type',
     operator: RuntimeFilterOp.ENDS_WITH,
     values: ['shirts']
 }]

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

 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

 runtimeFilters: [{
     columnName: 'revenue',
     operator: RuntimeFilterOp.BW_INC_MIN,
     values: ['25', '50']
 }]

BW_INC
between inclusive
Number of values allowed: 2

 runtimeFilters: [{
     columnName: 'sales',
     operator: RuntimeFilterOp.BW_INC,
     values: ['10', '50']
 }]

BW
between non-inclusive
Number of values allowed: 2

 runtimeFilters: [{
     columnName: 'sales',
     operator: RuntimeFilterOp.BW,
     values: ['25', '50']
 }]

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

 runtimeFilters: [{
     columnName: 'item type',
     operator: RuntimeFilterOp.IN,
     values: ['jackets', 'bags', 'shirts']
 }]

NOT_IN
is not included in this list of values

 runtimeFilters: [{
     columnName: 'item type',
     operator: RuntimeFilterOp.NOT_IN,
     values: ['skirts', 'bags']
 }]

Example🔗

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 liveboardEmbed = new LiveboardEmbed('#embed-container', {
     ... // other options
     runtimeFilters: [{
         columnName: 'state',
         operator: RuntimeFilterOp.EQ,
         values: ['california'],
     }, ],
 })

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

Note	Spotter embed does not support updating filters via host and embed events.

Apply runtime filters on embedded objects without the SDK🔗

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}

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.

Runtime filters via REST APIs🔗

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

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>

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

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 Models. 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.