---
title: "Parametrizing Queries Using Jinja"
slug: "parametrizing-queries-using-jinja"
updated: 2026-05-07T11:19:11Z
published: 2026-05-07T11:19:11Z
canonical: "docs.preset.io/parametrizing-queries-using-jinja"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.preset.io/llms.txt
> Use this file to discover all available pages before exploring further.

# 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`, `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](/v1/docs/querying-and-analysis), Chart Controls, [Virtual Datasets](/v1/docs/virtual-datasets) and also on [Row Level Security filters](https://docs.preset.io/docs/row-level-security-rls) to:

- Add pre-defined macros to your query to return dynamic data.
- Execute logic statements (such as `if`, `for`, etc).

## Macros

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

```python
{{ $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 result returned from these macros is added to the cache computation, to avoid sharing cache across users that would get different macro results. You can disable this behavior by passing `add_to_cache_keys=False` as a parameter (supported by all macros).

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:**

```sql
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:**

```sql
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:

```sql
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:

```sql
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:

```sql
SELECT '{{ current_user_rls_rules()|tojson }}' as current_user_roles
```

### URL Parameter

The `url_param($param_name)` 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`

```sql
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 return information from a dashboard filter. To use it, it's required to specify the column name powering the filter. The result would be a list 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:**

```sql
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 Filters

The `get_time_filters($column_name)` macro returns the time filter applied to a specific column. This is useful if you want to handle time filters inside the virtual dataset, as by default the time filter is placed on the outer query. Supported parameters:

- `column_name`: Name of the temporal column. Leave undefined to reference the time range from a dashboard.
- `default`: The default value to fall back to if the time filter is not present, or has the value No filter
- `target_type`: The target temporal type as recognized by the target database (e.g. `TIMESTAMP`, `DATE` or `DATETIME`). If `column_name` is defined, the format will default to the type of the column. This is used to produce the format of the `from_expr` and `to_expr` properties of the returned `TimeFilter` object.
- `strftime`: format using the `strftime` method of `datetime` for custom time formatting. When defined, `target_type` is ignored.
- `remove_filter`: When set to `true`, mark the filter as processed, removing it from the outer query.

The return type has the following properties:

- `from_expr`: the start of the time filter (if any).
- `to_expr`: the end of the time filter (if any).
- `time_range`: Final time range.

Example:

```sql
{% set time_filter = get_time_filter("dttm", default="Last week", remove_filter=True) %}
{% set from_expr = time_filter.from_expr %}
{% set to_expr = time_filter.to_expr %}
{% set time_range = time_filter.time_range %}
SELECT
  *,
  '{{ time_range }}' as time_range
FROM logs
WHERE
dttm >= {{ from_expr }}
AND dttm < {{ to_expr }}
```

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

```sql
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):

```sql
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:

```sql
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.

The `table_columns` is commonly it is used in conjunction with the other macros like `{{column}}` to get column names.

```sql
{% 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:

```sql
{% 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 support [Jinja builtin filters](https://tedboy.github.io/jinja2/templ13.html), 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:

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

## 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:

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

**Example:**

```sql
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](https://www.youtube.com/watch?v=dE1QlhunWMs)

[Jinja URL Parameters](https://www.youtube.com/watch?v=5v0keE_RlSA)

[Using a Jinja Template to Create Filters in Array Elements](https://www.youtube.com/watch?v=blhWKmxkIHE)

---

###