Parametrizing Queries Using Jinja

Prev Next

What is Jinja Templating?

Jinja Templating is a web template engine for Python. It uses a text-based template language that can be used to generate both markup and source code. The inclusion of Jinja increases flexibility in Preset functionality and features multiple use cases, such as:

  • Implement access controls based on the currently logged in user’s data (user_id, username, user_email, user_roles, etc).

  • Apply dashboard filters directly on the dataset's inner query.

  • Use a filter component to filter a query when the name of a filter column doesn't match one in the current query.

  • Apply filter constraints to dashboards via the URL.

  • Dynamically modify specific elements of your SQL query (such as calculations, aggregations, etc) based on dashboard filter conditions.

  • Advanced controls over operators.

  • Personalized dashboards.

On Preset, you can use Jinja on SQL Lab, Chart Controls, Virtual Datasets and also on Row Level Security filters to:

  • Add pre-defined macros to your query to return dynamic data.

  • Execute logic statements (such as if, for, etc).

Upgrading to 6.1?

       

The {{ from_dttm }} and {{ to_dttm }} variables were removed in v6.1. Use get_time_filter() instead. See Upgrading to v6.1.x.

   

Macros

Jinja macros are declared inside double curly brackets ({{ }}). Parameters can be passed inside the parenthesis:

{{ $macro_name($parameters) }}

These are the available macros:

  • current_username()

  • current_user_id()

  • current_user_email()

  • current_user_roles()

  • current_user_rls_rules()

  • url_param()

  • filter_values()

  • get_filters()

  • get_time_filter()

  • metric()

  • dataset()

  • table_columns

By default, the results of the user macros (current_username(), current_user_id(), current_user_email(), current_user_roles()) and of url_param() are added to the cache key. This prevents users who get different macro results from sharing cached results. To turn this off for one of these macros, pass add_to_cache_keys=False, for example {{ current_username(add_to_cache_keys=False) }}. Other macros don't accept this parameter, and passing it to them causes an error.

Let's take a closer look at them:

Current Username

The current_username() macro returns the logged in user's username value. This value is global (the same across all Workspaces), and can't be modified.

On Preset, the username can be an internal identifier rather than a readable name or the email address. To match users by email, use current_user_email().

Example:

SELECT * FROM sales WHERE employee = '{{ current_username() }}'

Current User ID

Similar to the current_username() macro, the current_user_id() returns the logged in user's ID value. Note that the ID is local to the Workspace (a user could have distinct IDs on each Workspace), and also can't be modified.

Example:

SELECT * FROM sales WHERE employee_id = {{ current_user_id() }}

Current User Email

The current_user_email() macro is another alternative to retrieve user-specific information through Jinja. It returns the email address of the logged-in user:

SELECT * FROM sales WHERE employee = '{{ current_user_email() }}'

Current User Roles

The current_user_roles() macro returns a list of roles the user is added to:

SELECT '{{ current_user_roles()|tojson }}' as current_user_roles

Current User RLS Rules

The current_user_rls_rules() macro returns a list of RLS rules currently applied for the user in the dataset:

SELECT '{{ current_user_rls_rules()|tojson }}' as current_user_rls_rules

URL Parameter

The url_param($param_name) macro fetches parameter values from the dashboard URL. When using this macro, you need to specify the parameter name. Multiple parameters can be specified in the URL using &. Use the default parameter to set the value returned when the parameter isn't in the URL, for example {{ url_param('location', default='US') }}.

Example:

  • Dashboard URL: https://abcd1234.us2a.app.preset.io/superset/dashboard/1/?location=US&month=8

SELECT * FROM "Vehicle Sales"
WHERE country = '{{ url_param('location') }}' -- location is the parameter name specified in the URL
AND month = {{ url_param('month') }} -- month is the parameter name specified in the URL

Filter Values

The filter_values($column_name) macro returns information from a dashboard filter. To use it, you must specify the name of the column powering the filter. The result is a list of all applied values (["first_value_applied", "second_value_applied", ...]). You can return only a particular position (for example, use [0] to return the first value applied), or use |where_in to return comma-separated values with quotes.

Example:

SELECT * FROM "Vehicle Sales"
WHERE country = '{{ filter_values('country')[0] }}'
-- country is the name of the column used on the dashboard filter.

This macro supports additional parameters:

  • remove_filter (boolean): When set to true, marks the filter as processed. This is useful when you’re using the filter value in the inner query and don’t want it to also get added in the outer query. Default value = false.

  • default (string): Specifies a default return value for the macro, in case there isn’t any filter applied. The string is returned in a list, so for example {{ filter_values('country', default='USA') }} returns ['USA'].

Get Filters

The get_filters($column_name) macro returns a list of filters applied to a column, including the operator. For example: [{'op': 'IN', 'col': 'year', 'val': [2005]}]. This macro also supports the remove_filter parameter.

Get Time Filter

The get_time_filter() macro returns the time range applied to a temporal column, so you can use it inside a virtual dataset. By default, a chart's time filter is added to the outer query. Applying it inside the dataset instead lets you filter before aggregations or window functions run, and many databases can optimize the query better.

get_time_filter() replaces the {{ from_dttm }} and {{ to_dttm }} variables, which were removed in v6.1. See Upgrading to v6.1.x.

Parameters

  • column: name of the temporal column the time filter applies to. It must match the dataset column name exactly, including case. Without column, the macro reads the time range from a dashboard's native Time range filter.

  • default: time range to use when no time filter is applied, or the filter is set to No filter. Examples: "Last week", "Last quarter", "2024-01-01 : 2024-02-01".

  • target_type: SQL type used to format the dates (TIMESTAMP, DATE, DATETIME, …). When a time filter is applied to column, the column's type is used automatically. Set target_type whenever you use default: when the default range is used, there is no applied filter to take the type from.

  • strftime: a Python strftime format, for example "%Y-%m-%d". The result is a plain string, not a SQL expression, so wrap it in quotes. When set, target_type is ignored.

  • remove_filter: when True, the time filter on column is not added again to the outer query. It only takes effect when the macro is used in a virtual dataset's SQL.

Returned properties

  • from_expr / to_expr: the start and end of the range as complete SQL expressions, formatted for your database (on PostgreSQL, for example, TO_TIMESTAMP('2003-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.US')). Use them as they are, without quotes. They are None when no time range applies.

  • time_range: the applied time range as text, for example Last week or No filter.

Example 1: filter inside the dataset, and skip the condition when there is no time range

This virtual dataset calculates each order's share of the sales in the selected period. The window function must only see rows in that period, so the time filter has to be applied inside the dataset:

{% set tf = get_time_filter("order_date", remove_filter=True) %}
SELECT
  order_date,
  product_line,
  sales,
  sales / SUM(sales) OVER () AS share_of_period_sales
FROM public."Vehicle Sales"
WHERE 1 = 1
{% if tf.from_expr %}  AND order_date >= {{ tf.from_expr }}{% endif %}
{% if tf.to_expr %}  AND order_date < {{ tf.to_expr }}{% endif %}

With a time range of 2003-01-01 : 2003-07-01 on order_date, Preset runs this on PostgreSQL:

SELECT ...
FROM (
  SELECT order_date, product_line, sales,
         sales / SUM(sales) OVER () AS share_of_period_sales
  FROM public."Vehicle Sales"
  WHERE 1 = 1
    AND order_date >= TO_TIMESTAMP('2003-01-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.US')
    AND order_date < TO_TIMESTAMP('2003-07-01 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.US')
) AS virtual_table
GROUP BY ...

Because of remove_filter=True, the outer query doesn't filter on order_date again. When no time range is applied, the {% if %} blocks drop both conditions and the dataset returns all rows. Without them, the query would contain order_date >= None and fail.

Example 2: fall back to a default range

{% set tf = get_time_filter("order_date", default="Last quarter", target_type="TIMESTAMP", remove_filter=True) %}
SELECT order_date, product_line, sales
FROM public."Vehicle Sales"
WHERE order_date >= {{ tf.from_expr }}
  AND order_date < {{ tf.to_expr }}

When a time range is applied to order_date, that range is used. Otherwise, the default (Last quarter) is used. target_type is required here: without it, the default range renders as None.

Common mistakes

Symptom

Cause

... >= None, or an error mentioning None

No time range applied: add {% if %} guards, or set both default and target_type

Time filter ignored, time_range shows No filter

column doesn't match the filtered column exactly (names are case-sensitive)

Date parsing error

from_expr / to_expr wrapped in quotes

Same filter applied twice (inner and outer query)

remove_filter=True missing

"malformed template parameters"

Macro misspelled, for example get_time_filters

"unexpected keyword argument"

Wrong parameter name, for example column_name= instead of column=

Metric

The metric($metric_key) macro can be used to retrieve the metric SQL syntax from a dataset. This can be useful for different purposes:

  • Override the metric label in the chart level (without impacting the dataset).

  • Combine multiple metrics in a calculation.

  • Retrieve a metric syntax in SQL lab.

  • Re-use metrics across datasets.

This macro avoids copy/paste, allowing users to centralize the metric definition in the dataset layer.

SELECT {{ metric('count') }} as count -- count is the key of the metric that is being used
FROM "Vehicle Sales"

When using this macro in the Chart Builder, by default the metric will be searched in the dataset being used. However, it’s possible to also specify the dataset ID (when using the macro in SQL Lab or in case you want to fetch a metric syntax from another dataset):

SELECT {{ metric('count', 3) }} as count -- count is the key of the metric that is being used. 3 is the dataset ID
FROM "Vehicle Sales"

Dataset

The dataset($dataset_id) macro returns the corresponding SELECT statement for a dataset. It’s useful to get a dynamic reference to a dataset definition:

SELECT * from {{ dataset(1) }}
UNION ALL
SELECT * from {{ dataset(2) }}

Table Columns

The table_columns Jinja variable is not intended for direct use in SQL Lab or creating datasets. Instead, it is used within the context of SQL templating in Superset/Preset, particularly in calculated columns, virtual datasets, and specific parts of the dashboard setup.

table_columns is commonly used in a for loop, where each item ({{ column }} in the examples below) is a column name:

{% for column in table_columns %}
    {{ column }}{% if not loop.last %}, {% endif %}
{% endfor %}

Another example: A requirement to create a calculated column that concatenates all string columns:

{% set string_columns = table_columns | selectattr("is_string", "equalto", true) %}
{% for column in string_columns %}
  {{ column }}{% if not loop.last %} || ' ' || {% endif %}
{% endfor %}

Filters

Filters are used to modify values. They are applied using the pipe (|) symbol and can be chained together.

Preset supports Jinja built-in filters, and also custom filters:

Where In

Converts a list (['First', 'Second', 'Third']) into a compatible SQL syntax to filter data: ('First', 'Second', 'Third'). For example:

SELECT * FROM "Vehicle Sales"
WHERE country in {{ filter_values('country')|where_in }}

To Datetime

Converts a string into a Python datetime object, using a strftime format (format="%Y-%m-%d %H:%M:%S" by default). This is useful for date calculations inside the template, for example combined with get_time_filter(strftime=...).

This example groups by month when the selected time range is longer than 100 days, and by day otherwise:

{% set tf = get_time_filter("order_date", default="Last year", strftime="%Y-%m-%d") %}
{% set days = (tf.to_expr|to_datetime(format="%Y-%m-%d") - tf.from_expr|to_datetime(format="%Y-%m-%d")).days %}
SELECT
  {% if days > 100 %}DATE_TRUNC('month', order_date){% else %}DATE_TRUNC('day', order_date){% endif %} AS period,
  SUM(sales) AS sales
FROM public."Vehicle Sales"
WHERE order_date >= '{{ tf.from_expr }}'
  AND order_date < '{{ tf.to_expr }}'
GROUP BY 1
ORDER BY 1

Note the quotes around tf.from_expr and tf.to_expr: with strftime, they are plain strings.

Logic Statements

Logic statements can be used to perform different actions based on validation results, and also perform iteration loops. The logic statement should be added inside curly brackets and percentage sign ({% %}). It's always required to start and end the block:

{% if something %}
     SQL syntax
{% else %}
     SQL syntax
{% endif %}

Example:

SELECT * FROM "Vehicle Sales"
WHERE country
{% if filter_values('operation')[0] == 'Equals' %} -- execute in case the first value applied to this dashboard filter is "Equals"
     = '{{ filter_values('location')[0] }}'
{% elif filter_values('operation')[0] == 'In' %} -- in case the first condition isn't met, check if the first value is "In"
     in {{ filter_values('location')|where_in }}
{% else %} -- execute in case none of the conditions above are met
     not in {{ filter_values('location')|where_in }}
{% endif %} -- end block



Video References

How to Apply an OR Filter using a Jinja Template

Jinja URL Parameters

Using a Jinja Template to Create Filters in Array Elements