Parametrizing Queries Using Jinja

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 and/or username).
• 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, 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).

Syntax

Jinja Macros

Jinja macros should be added inside double curly brackets ({{ }}). Parameters are also added inside parenthesis:

{{ macro_name(parameters) }}

These are the available macros:

• current_username()
• current_user_id()
• url_param()
• filter_values()

Let's take a closer look on 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.

Example: 

SELECT * FROM salesWHERE 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 (one user would have different IDs on each Workspace), and also can't be modified.

Example:

SELECT * FROM SALESWHERE employee_id = {{ current_user_id() }}

URL Parameter

The url_param() macro would fetch 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 &.

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() macro return information from a dashboard filter. To use it, it's required to specify the column name powering the filter. The result would be an array of all applied values (["first_value_applied", "second_value_applied", ...]). It's possible to 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 in ({{ filter_values('country')|where_in }}) 
-- country is the name of the column used on the dashboard filter.
-- |where_in would return the results in the correct SQL syntax -> 'USA', 'Belgium', 'Canada' instead of ['USA', 'Belgium', 'Canada']

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') == 'In' %} -- in case the first condition isn't met, check if the filter 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