Press CTRL+K to open search

Using Jinja to filter and retrieve ArrayType Column data

New
  • Published on Sep 9, 2026
  • 2 minute(s) read
Prev Next

Overview

This article covers how to use Jinja Templating to retrieve filter selection that can be used directly to filter datasets that contains an ArrayType Column data.

The Process

This process consists of 3 steps:

  1. Create a Virtual Dataset from unique array values.
  2. Create a Virtual Dataset.
  3. Set up the Dashboard and Filters.

Let's have a closer look at each step.

Step 1: Create a Virtual Dataset from unique array values

For this example, we will use a Hobbies table with the following data:

{"name":"John","age":30,"hobbies":["reading","sports","gaming"]}
{"name":"Alice","age":25,"hobbies":["photography","cooking"]}
{"name":"Bob","age":35,"hobbies":["music","movies","traveling"]}
{"name":"Matthews","age":27,"hobbies":["sports"]}
{"name":"Claire","age":19,"hobbies":["photography","traveling"]}
{"name":"Maria","age":20,"hobbies":["gaming"]}

Here are the results when querying it via SQL Lab:
Image

Next, extract the Hobbies array column into all possible unique values.
Run the query below to create a Virtual Dataset that lists all unique values:

SELECT DISTINCT $new_column_name 
FROM $database_name.$table_name
CROSS JOIN UNNEST($column_name) AS t ($new_column_name);

Image
Once the results look right, click on the drop down arrow next to the "Save" option and select "Save dataset".

Fill in all the necessary fields and click save.

Step 2: Create a Virtual Dataset

Now, let’s create a Virtual Dataset to power the chart. Since filtering on array columns using contains($column_name, ‘$value') is not natively supported in Superset, we will need to use a Jinja condition within a Virtual Dataset to achieve this:

SELECT *
FROM preset_support_test.array
WHERE 1=1
{% if filter_values('unique_hobbies')|length %}
  and contains(hobbies, '{{filter_values('unique_hobbies')[0]}}')
{% endif %}

Following is some additional context on the above query:

  1. WHERE 1=1 is an “always true” filter condition, so that we can append the additional filter when needed.
  2. {% if filter_values('unique_hobbies')|length %} checks if there’s any value applied to a dashboard filter created from the unique_hobbies column.
  3. contains(hobbies, '{{filter_values('unique_hobbies')[0]}}') in case the IF statement is true, this line would be added to the dataset query. The filter_values() Jinja macro returns an array of selected items, so [0] indicates we would use the first value.
  4. {% endif %} is used in Jinja syntax to indicate that the IF statement is finished.

Step 3: Set up the Dashboard and Filters

Now, let's create a table chart using the virtual dataset from Step 2 and add it to a dashboard. Then, create a filter powered by the original dataset from Step 1.

When setting up the filter, keep two things in mind:

  • Deselect "Can select multiple values". This filter is designed to return results for a single selected value only.
    Image
  • Manually map the filter to the chart. Since the filter was created from a different dataset, it won't be mapped automatically. Go to the Scoping tab to configure this.
    Image

Great work! Once a hobby is selected in the filter, the chart will display all records containing that hobby.
Array List.gif