Menu Close Get Metabase

Field Filters: create smart filter widgets for SQL questions

Aug 14, 2020 by The Metabase Team

This article shows how to add smart filter widgets to your SQL questions in Metabase using a special type of variable called a Field Filter.

Introduction to Field Filters

Figure 1. A <strong>Field Filter</strong> is a special type of variable that can wire up a variable in your SQL code to a field (column) in a table, which enables it to create a 'smart' filter widget.
Figure 1. A Field Filter is a special type of variable that can wire up a variable in your SQL code to a field (column) in a table, which enables it to create a 'smart' filter widget.

For Metabase questions written in SQL, we can use basic variable types — Text, Number, and Date variables — to create simple filter widgets. To create “smarter” filter widgets that can display options specific to the data in the filtered column, such as to create a dropdown menu of values, we can use a special variable type called a Field Filter.

Figure 2. To create a Field Filter, add a variable to your SQL code by enclosing the variable in double braces (Mustache style), and select Field Filter as the <strong>Variable type</strong> from the <strong>Variables</strong> sidebar.
Figure 2. To create a Field Filter, add a variable to your SQL code by enclosing the variable in double braces (Mustache style), and select Field Filter as the Variable type from the Variables sidebar.

Field Filters can initially confuse some people, because 1) Field Filters only work with certain fields, and 2) people expect Field Filters to behave like basic input variables (which they don’t).

Field Filters are well worth learning, as you can use them to create much more sophisticated filter widgets. In this article, we’ll go through Field Filters in depth, but first let’s discuss the main practical differences that distinguish Field Filter variables from basic Text, Number, and Date variables.

Distinguishing Field Filters from simple Text, Number, and Date variables

  1. Field Filters are optional by default. If no value is given, the SQL query will run as if the Field Filter didn’t exist. You do, however, have the option to require a value.
  2. Field Filters won’t work with table aliases. Since Field Filters rely on metadata about columns in your tables (and the specific names of those tables), the filters can’t “know” that you’ve aliased a table. And depending on the database you’re using, you may need to include the full schema path in the FROM clause.
  3. Field Filters use a special syntax so they can handle SQL code behind the scenes. You simply supply a Field Filter to a WHERE clause (without a column or operator), and the Field Filter will manage the SQL code for you, which allows the code to account for multiple selections people make in the filter widget.

Point #3 can be especially confusing, so let’s unpack it with an example.

Creating a filter widget with a dropdown menu

We’ll use the Sample Dataset included with Metabase to add a filter widget with a dropdown menu to a question written in SQL.

Let’s say we want to create a SQL question that grabs all the orders from the orders table, but we want to give people the option to filter the results by category in the products table.

Now, we could create a products.category filter with a basic input variable, like so:

SELECT *
FROM orders
LEFT JOIN products
ON orders.product_id = products.id
[[WHERE products.category = {{category}}]];

In this case, we enclose the WHERE clause in double brackets to make the input optional, and use the Variables sidebar to set the Variable type to Text and the Filter widget label to Category.

This approach works, but it’s not ideal:

  • In order to filter the data, people would have to know which categories exist (and spell them correctly when they input them).
  • Plus, they can’t select multiple categories at a time, as the {{category}} variable only accepts a single value.

By contrast, a Field Filter will map the variable to the actual column data. The filter widget connected to the variable then “knows” which categories are available, and can present a dropdown menu of those categories, like so:

Figure 3. A filter widget created by a Field Filter that's connected to the Category column in the Products table. The Field Filter allows the widget to display a dropdown list of the available product categories.
Figure 3. A filter widget created by a Field Filter that's connected to the Category column in the Products table. The Field Filter allows the widget to display a dropdown list of the available product categories.

A word on the widget: the dropdown menu is just one of the options available. In the case of fields of type Category, like our category field in the products table, we could also set the filter widget as a Search box or a Plain input box. Admins can configure Field settings in the Data Model page of the Admin Panel.

Figure 4. In the <strong>Data Model</strong> tab of the <strong>Admin Panel</strong>, Admins can edit the <strong>Field Settings</strong>. For fields of type <strong>Category</strong>, Admins can select three options for field widgets: <strong>Search box</strong>, <strong>A list of all values</strong> (dropdown), or <strong>Plain input box</strong>.
Figure 4. In the Data Model tab of the Admin Panel, Admins can edit the Field Settings. For fields of type Category, Admins can select three options for field widgets: Search box, A list of all values (dropdown), or Plain input box.

Note that Metabase will automatically use a Search box if the number of distinct values in the column is greater than 300, even if you select the dropdown option, A list of all values. Learn more about editing metadata in our documentation.

Now, let’s get back to our question. Here’s the syntax for the products.category Field Filter. And note the omission of the column and operator before the variable in the WHERE clause – more on Field Filter syntax below:

SELECT *
FROM orders
LEFT JOIN products
ON orders.product_id = products.id
WHERE {{category}};

With our variable in place in the WHERE clause, we can use the Variables sidebar to wire up our variable as a Field Filter. We’ll set:

  • Variable type to Field Filter.
  • Field to map to to Products -> Category. This setting tells Metabase to connect the variable in our SQL code to the category column of the products table.
  • Field widget type to category.
  • Field widget label to category.
Figure 5. Setting a variable's type as Field Filter, and mapping that Field Filter to the <strong>Category</strong> field (or column) of the <strong>Products</strong> table.
Figure 5. Setting a variable's type as Field Filter, and mapping that Field Filter to the Category field (or column) of the Products table.

We won’t require the variable, so no default value is necessary. If the query runs without a specified value in the filter widget, the query will return records from all categories.

Note that the WHERE clause does not specify which column the variable should be equal to. The reason for this implicit syntax (the hidden SQL code) is so that the Field Filter can handle the SQL code behind the scenes to accommodate multiple selections.

Creating sophisticated date filter widgets

We can create a basic input variable of type Date, which will add a filter widget with a simple date filter. If, instead, we use a Field Filter variable, we can connect that variable to a field (column) that contains dates, which unlocks a lot more options for configuring our filter widget.

Figure 6. Setting the <strong>Field to map to</strong> option to a field containing dates will open up a range of <strong>Filter widget types</strong>: Month and Year, Quarter and Year, Single Date, Date Range, Relative Date, and Date Filter.
Figure 6. Setting the Field to map to option to a field containing dates will open up a range of Filter widget types: Month and Year, Quarter and Year, Single Date, Date Range, Relative Date, and Date Filter.

Here are the different widget types for Field Filters mapped to Date fields:

  • Month and Year
  • Quarter and Year
  • Single Date
  • Date Range
  • Relative Date
  • Date Filter

Each widget type offers different ways for people to filter the results: Here are three examples:

Figure 7. The <strong>Month and Year</strong> widget type.
Figure 7. The Month and Year widget type.
Figure 8. The <strong>Relative Date</strong> widget type.
Figure 8. The Relative Date widget type.
Figure 9. The <strong>Date Filter</strong> widget type.
Figure 9. The Date Filter widget type.

The Date Filter widget type offers the most flexibility, allowing people to filter by relative dates and ranges.

Field Filter gotchas

There are a few places that people typically get stuck when trying to implement Field Filters.

Field Filters are incompatible with aliasing

As noted above, Field Filters won’t work if you use aliases in your SQL query. For example, this code (with aliases) will not work:

AVOID
SELECT *
FROM orders AS o
LEFT JOIN products AS p
ON o.product_id = p.id
WHERE {{category}};

Whereas this code without aliases WILL work:

CORRECT
SELECT *
FROM orders
LEFT JOIN products
ON orders.product_id = products.id
WHERE {{category}};

The reason is because Field Filters work by analyzing metadata about your data (e.g., the column names of your tables), and that metadata does not include the aliases you create in your SQL code. Note that some databases require the schema in the FROM clause. An example for Oracle would be FROM "schema"."table". In BigQuery, back ticks are needed: FROM `dataset_name.table`.

Omit the direct assignment in the WHERE clause

As stated above: the SQL code around Field Filters is not exactly street legal. You may be tempted to write:

AVOID
WHERE category = {{ category }}

because, in standard SQL, that is the correct syntax for a WHERE clause. But that syntax won’t work for Field Filters.

The correct syntax for Field Filters omits the = operator:

CORRECT
WHERE {{ category }}

The reason for this shorthand is so that Metabase can, behind the scenes, handle the SQL code for situations like multiple selections, e.g., when a user selects multiple categories from a dropdown.

Only certain fields are compatible with Field Filters

Here is the list of compatible fields.

Figure 10. Some fields are incompatible with Field Filters. For incompatible fields, you'll see the notice: There aren't any filter widgets for this type of field yet.
Figure 10. Some fields are incompatible with Field Filters. For incompatible fields, you'll see the notice: There aren't any filter widgets for this type of field yet.

You can find a list of incompatible field types in our documentation on Field Filters.

Learn more

Check out our guide to basic SQL input variables - Text, Number, and Date.

You can also read our documentation on:

Stay tuned

We’ll cover how to include filters widgets on dashboards, and how to wire them up to multiple questions so users can filter data across multiple questions at once.