Menu Close Get Metabase

Searching your tables and questions

Sep 25, 2020 by The Metabase Team

Make searching text a breeze with the filters discussed in this article. At the end, you’ll know how to easily find a word or phrase within SQL questions or simple questions.

To skip to a certain section, use these links:

To decide whether a SQL question or custom question is a better fit, read SQL questions versus custom questions. The general rule is to build a custom question unless your question requires specific SQL commands or functions.

You can follow along with this article’s examples by using the Sample Dataset included with each Metabase installation.

Search within a question

Access the correct table by clicking the “Browse data” button and selecting the Sample Dataset. Open the Products table.

To search simple questions, we use a customizable filter. Click the purple “Filter” button and select the column you want to filter by.

To match our example, 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

Select “Starts with” and type “rustic” in the search box on the Filter sidebar. As you’re typing “rustic” a dropdown will give you a preview of your search results. If you see a specific item you’re looking for, you can select it from the list. If not, you can search the term generally.

When you’re done typing your search, click Add filter.

Figure 1. Creating a search widget by adding a filter to a question.
Figure 1. Creating a search widget by adding a filter to a question.

With the filter applied, only titles that start with “rustic” are listed in the table and the row count decreases from 200 to 14.

Update your search filter

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

Click on the purple filter description. In our example, it says “Title starts with rustic.”

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

In the popup, use the text box to replace the current search term with “Marble.” Then click the dropdown menu that currently says “Starts with” to change your text filter option to “contains.”

Use the case sensitive toggle on the bottom left to turn on case sensitivity.

In Figure 3, we use our search widget to change from “Title starts with rustic” (without case sensitivity) to “Title contains Marble” (with case sensitivity).

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. In this example, we’ll use the “OR” function.

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

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

The Custom Expression sidebar is filled with tools to create advanced filters or add custom columns to a question.

For this example, select the contains option.

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

Follow the example provided by the sidebar in Figure 5 to write your base query (contains([Title] , "Rustic")).

  • [Title] is the column that will be searched
  • "Rustic" is the term being searched for

Then add a space and select “OR” from the popup. Afterward you can write another text search function. We chose to write OR contains([Title], "clock").

When done, click the purple “Add filter” button at the bottom of the Filter sidebar.

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

To edit the custom expression, click the purple filter description and make your desired changes in the text box.

Searching on foreign keys

You can use foreign keys to search a table based on data from a connected table. After your data model settings match Figure 7, you can search on foreign keys with strings as well as ID numbers.

In the 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 displays the title of each Product instead of its ID number.

Learn how to edit your metadata by reading Editing Metadata.

Figure 7. In the data model, the admin has saved the filtering settings as 'Search box' and the display value as 'Title.'
Figure 7. In the data model, the admin has saved the filtering settings as 'Search box' and the display value as 'Title.'

Once your metadata is set correctly, return to the home page, click the “Browse data” button, select the Sample Dataset, and open the Reviews table.

Add a search widget by clicking the Filter button and selecting “Product ID” in the Filter sidebar.

Use the search bar that appears to type “Practical” and as you type a dropdown will appear of all your options. We select “Practical Bronze Computer” in the example.

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

If you want to add another Product to your results, select one of the other items in the dropdown. Results matching either Product title will appear in your results. In Figure 8 we additionally select “Practical Plastic Keyboard” and can see both items listed under Product ID.

Add a search widget to your SQL question

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

Click the “Ask a question” button and select “Native query.” Type in the query that appears in Figure 9 below.

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

As soon as you start typing within the curly brackets {{}} the search bar will appear above the SQL input area and the variables sidebar will appear to the right.

Figure 9. The <strong>native query editor</strong> with the example query typed into the SQL text area.
Figure 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; read 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 you type between the curly brackets as your label, but can be changed using this text box.

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

Figure 10 illustrates all three of these features.

Figure 10. Shows the variable type options, creates a new placeholder for the widget label, and scrolls through the 'Help' tab.
Figure 10. Shows the variable type options, creates a new placeholder for the widget label, and scrolls through the 'Help' tab.

Read more

To create search widgets and Custom Expressions using the notebook editor, check out Writing expressions in the notebook editor. To learn about searching with filters in dashboards read Dashboard Filters and Adding filters to dashboards with SQL questions.