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. Useget_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_rolesCurrent 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_rulesURL 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 URLFilter 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 totrue, 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. Withoutcolumn, 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 toNo 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 tocolumn, the column's type is used automatically. Settarget_typewhenever you usedefault: 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_typeis ignored.remove_filter: whenTrue, the time filter oncolumnis 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 areNonewhen no time range applies.time_range: the applied time range as text, for exampleLast weekorNo 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 |
|---|---|
| No time range applied: add |
Time filter ignored, |
|
Date parsing error |
|
Same filter applied twice (inner and outer query) |
|
"malformed template parameters" | Macro misspelled, for example |
"unexpected keyword argument" | Wrong parameter name, for example |
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 1Note 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