Menu Close Get Metabase

Learn Metabase

Searching your tables and questions

Adding filters to your questions can make searching text in your questions a breeze. You can follow along with the examples in this article using the Sample Dataset included with each Metabase installation.

Search within a question

We’ll start by clicking the Browse data button, selecting the Sample Dataset, and opening the Products table.

To search simple questions, we use a customizable filter. We’ll click the purple Filter button and select the column we want to filter by.

Let’s select “Title.” Then use the dropdown menu that currently says “Is.” A popup with the text filter options will appear:

  • Is
  • Is not
  • Contains
  • Does not contain
  • Is empty
  • Not empty
  • Starts with
  • Ends with

We’ll select “Starts with” and type “rustic” in the search box on the Filter sidebar. As we’re typing “rustic”, a dropdown will give us a preview of our search results. We’ll click Add filter to, well, add the filter to our question.

<em>Fig. 1</em>. Creating a search widget by adding a filter to a question.
Fig. 1. Creating a search widget by adding a filter to a question.

With the filter applied, our question will only list product titles that start with “rustic”.

Update your search filter

Now that we’ve added a filter, our search filter is at the top of our table.

We can click on the purple filter description, which says “Title starts with rustic.”

<em>Fig. 2</em>. Close up picture of the simple question <strong>search filter</strong>.
Fig. 2. Close up picture of the simple question search filter.

In the popup, we can use the text box to replace the current search term, say with “Marble.” We can also change how the widget filters the results by clicking the dropdown menu that currently says “Starts with”, for example by changing our text filter option to “contains.” In figure 3, we update our search widget from “Title starts with rustic” to “Title contains Marble”, and make the search case sensitive.

Figure 3. Using the <strong>search widget</strong> to change what we're searching for in the Title column of the Products table.
Figure 3. Using the search widget to change what we're searching for in the Title column of the Products table.

Advanced search with Custom Expressions

Custom Expressions are a way to make advanced queries with numbers or text. Let’s say we want to filter results by products that contain either “Rustic” or “Clock” in their title. Let’s create a Custom Expression by clicking the Filter button and selecting Custom Expression at the bottom of the sidebar. In the popup, scroll down to the section labeled functions (figure 4).

<em>Fig. 4</em>. The Reviews table with the <strong>Custom Expression sidebar</strong> open.
Fig. 4. The Reviews table with the Custom Expression sidebar open.

The Custom Expression sidebar contains tools to create advanced filters or add custom columns to a question. Let’s try out the contains option.

<em>Fig. 5</em>. The <strong>Custom Expression sidebar</strong> is open and an expression beginning with `contains()` is visible.
Fig. 5. The Custom Expression sidebar is open and an expression beginning with `contains()` is visible.

We’ll write our base query like so: (contains([Title] , "Rustic")).

  • [Title] is the column to filter
  • "Rustic" is the term to filter for

We’ll add a space and select “OR” from the popup, then we’ll write another text search function: OR contains([Title], "clock"), and click Add filter.

Figure 6. Creating a <strong>Custom Expression</strong> that searches through the Title column of the Products table for the words 'Rustic' or 'Clock.'
Figure 6. Creating a Custom Expression that searches through the Title column of the Products table for the words 'Rustic' or 'Clock.'

Searching on foreign keys

We can use foreign keys to search a table based on data from a connected table. Admins will need to set up our data model settings to match figure 7, so we can search on foreign keys with strings as well as ID numbers.

In our example, the Product_ID column is set up as a foreign key, with “search box” filter settings, and a display value of “Title.” This way when we filter on the Product_ID we can filter by title instead of ID number, and the table will display the title of each product instead of its ID number. You can learn more about editing metadata in our documentation.

<em>Fig. 7</em>. In the data model, the admin has saved the filtering settings as 'Search box' and the display value as 'Title.'
Fig. 7. In the data model, the admin has saved the filtering settings as 'Search box' and the display value as 'Title.'

With our metadata set, we’ll return to the home page, click the Browse data button, select the Sample Dataset, and open the Reviews table. We’ll add a search widget by clicking the Filter button and selecting Product ID in the Filter sidebar. In our new filter widget, we’ll type “Practical”, and select one of the values in the dropdown, like “Practical Bronze Computer”.

<em>Fig. 8</em>. Creating a <strong>search widget</strong> for the Reviews table using the Title of the Product instead of the ID.
Fig. 8. Creating a search widget for the Reviews table using the Title of the Product instead of the ID.

Add a search widget to your SQL question

In a normal database, we’d need to craft a new SQL query for every search term, but Metabase makes searching our database easy, even when using SQL queries. All we need to do is insert a variable in the WHERE clause of a query.

In the upper right of the main navigation bar, we’ll click on the Write SQL icon.

SELECT *
FROM REVIEWS
[[WHERE UPPER(Body) LIKE UPPER(CONCAT('%', {{search_term}}, '%'))]]

As soon as we start typing within the curly brackets {{}}, the search bar will appear above the SQL input area Metabase will kick out the variables sidebar.

<em>Fig. 9</em>. The <strong>native query editor</strong> with the example query typed into the SQL text area.
Fig. 9. The native query editor with the example query typed into the SQL text area.

SQL question breakdown

In plain English, this SQL query is saying, “Show me reviews where the body of the review includes the search term, whether or not the two are capitalized in the same way.”

Let’s break down the elements in line three:

  • [[]] The brackets make the filter optional. If no search term is supplied to the variable, the query will return unfiltered rows.
  • WHERE filters the results.
  • UPPER() capitalizes both the text being searched and the search term, so they will match even if there is a difference in case.
  • BODY is the column to filter.
  • LIKE searches for a pattern.
  • CONCAT() (short for concatenate) combines strings.
  • '%' A percent symbol is a wild card. If positioned before the search term additional text can come before the term. Wrapping the search term with % searches for the term even if it’s surrounded by text.
  • {{}} The double curly brackets wrap a variable.
  • search_term The variable that the filter widget will supply with text to search for.

Variables sidebar

In figure 9, above, the variable type has selected “Text”, but there are several other options as well:

  • Number
  • Date
  • Field filter

Filters can be both complex and insanely useful; check out Dashboard Filters and Field Filters to get a sense for the many ways they can provide valuable insights.

Below Variable type is Filter widget label, which automatically uses the text we type between the curly brackets as our label.

For additional examples and advice on typing SQL queries that support a search widget, click the “Help” tab towards the top of the sidebar.

<em>Fig. 10</em>. The variable type options, creating a new placeholder for the widget label, and scrolling through the 'Help' tab.
Fig. 10. The variable type options, creating a new placeholder for the widget label, and scrolling through the 'Help' tab.