Interactivity & Filters

Advanced usage in SQL

The custom filter values can be used in several more ways in the SQL query to give additional capabilities.

The default usage matches both a column (or a complex expresssion in square brackets) with the selected filter values taking into account the number of selected values.

SELECT * FROM flights WHERE {origin=origin_airports};

would be executed as either of these depending on what the user selects in the filter bar:

SELECT * FROM flights WHERE 1=1;
SELECT * FROM flights WHERE origin='LAX';
SELECT * FROM flights WHERE origin IN ('LAX', 'SFO);

There are more ways to tap into the filter values:

  • {origin_airports.min} - gives access to the minimum of the selected values or NULL if no value is selected
  • {origin_airports.max} - gives maximum of the selected values or NULL if no value is selected
  • {origin_airports.count} - gives number of selected values (0, 1 or more)
  • {origin_airports.selected} - true or false if the filter has a value selected (TRUE or FALSE)
  • {origin_airports.value} - replaced with a value in case of single selected value or NULL in case 0 or more than 1 value is selected
  • {origin_airports.values} - replaced with braced list of selected values '(1,2,3)', empty list if no values are selected. This is useful for doing 'IN' WHERE clause in the SQL query
  • {origin_airports.values_array} - replaced with square-braced list of selected values '[1,2,3]', empty list if no values are selected. This is useful for databases that allow to work with array literal (e.g. BigQuery)
  • {origin_airports.values_concat} - replaced with a string value of all the selected values concatenated with comma (',') as separator. Empty string in case no values are selected.
  • {my_filter.value_raw} - replaced directly with the value of the filter without any manipulation - useful for instance if you want to specify dynamically the column to choose in the query

Here is an example of a query using the filter values in this way:

SELECT {origin_airports.min}, 
       {origin_airports.max}, 
       {origin_airports.count}, 
       {origin_airports.selected},
       {origin_airports.values_concat},
       {my_filter.value_raw}
FROM flights
WHERE  origin={origin_airports.value} OR origin IN {origin_airports.values}

You can also tap into the currently used timerange or aggregation values in the same way:

SELECT {timerange.value}, {aggregation.value}

or use it directly in the SQL for instance to access pre-aggregated tables by the aggregation:

SELECT * from aggregated_data_by_{aggregation.value_raw}

Related articles