Newer
Older
Metabase has the flexible ability to allow variables in native (SQL) queries. This lets you dynamically replace values in your queries using filter widgets or through the query's URL.
Options and settings for your variables will appear in the `Variables` side panel of the native query builder once you've defined a variable. So, how do you define a variable?
### Defining Variables
Typing `{% raw %}{{variable_name}}{% endraw %}` in your native query creates a variable called `variable_name`. Variables can be given types in the side panel, which changes their behavior. All variable types other than "Field Filter" will cause a filter widget to be placed on this question corresponding to the chosen variable type. When a value is selected via a filter widget, that value replaces the corresponding variable in the SQL template, wherever it appears. If you have multiple filter widgets, you can click and drag on any of them to move and reorder them.
This example defines a variable called `cat`, allowing you to dynamically change the `WHERE` clause in this query:
If you're writing a native MongoDB query, your query would look more like this, with the `cat` variable being defined inside of the `match` clause.
{% raw %}[{ $match: { category: {{cat}} } }]{% endraw %}
```
### The Field Filter variable type
Setting a variable to the "Field Filter" type allows you to map it to a field in any table in the current database, and lets you display a dropdown filter widget filled with the values of the field you connected it to. Field filter variables also allow you to connect your SQL question to a dashboard filter if you put it in a dashboard.
A field filter variable inserts SQL similar to that generated by the GUI query builder when adding filters on existing columns. This is useful because it lets you do things like insert dynamic date range filters into your native query. When adding a field filter, you should link that variable to a specific column. Field filter variables should be used inside of a `WHERE` clause in SQL, or a `$match` clause in MongoDB.
**Note:**
- Table aliases are not supported. This is because field filters generate SQL based on the mapped field.
- Some databases require the schema in the `FROM` clause. An example for Oracle would be `FROM "schema"."table"`. In BigQuery, back ticks are needed, like `` FROM `dataset_name.table` ``.
WHERE {% raw %}{{date_var}}{% endraw %}
```
A MongoDB native query example might look like this:
```
{% raw %}[ {$match: {{date_var}} } ]{% endraw %}
#### Creating SQL question filters using field filter variables
First, insert a variable tag in your SQL, like `{% raw %}{{my_var}}{% endraw %}`. Then, in the side panel, select the "Field Filter" variable type, and choose which field to map your variable to. In order to display a filter widget, you'll have to choose a field whose "Type" in the "Data Model" section of the Admin Panel is one of the following:
- Category
- City
- Entity Key
- Entity Name
- Foreign Key
- State
- UNIX Timestamp (Seconds)
- UNIX Timestamp (Milliseconds)
- ZIP or Postal Code
The field can also be a datetime, which can be left as "No special type" in the data model.
You'll then see a dropdown labeled "Widget," which will let you choose the kind of filter widget you want on your question, which is especially useful for datetime fields. You can select "None" if you don't want a widget on the question at all, which you might do, for example, if you just want to allow this question to be mapped to a dashboard filter (see more on that below).
**Note:** If you're not seeing the option to display a filter widget, make sure the mapped field is set to one of the above types, and then try manually syncing your database from the "Databases" section of the Admin Panel to force Metabase to scan and cache the field's values.
Filter widgets **can't** be displayed if the variable is mapped to a field marked as:
- Avatar Image URL
- Description
- Email
- Enum
- Field containing JSON
- Image URL
- Number
- Latitude
- Longitude
- URL
If you input a default value for your field filter, this value will be selected in the filter whenever you come back to this question. If you clear out the filter, though, no value will be passed (i.e., not even the default value). **The default value has no effect on the behavior of your SQL question when viewed in a dashboard.**
##### Default value in the query
You can also define default values directly in your query, which are useful when defining complex default values. Note that the hash (`#`) might need to be replaced by the comment syntax of the database you're using. Some databases use double-dashes (`--`) as comment syntax.
Current example for a Date filter:
```
SELECT p.*
FROM products p
WHERE p.createdAt = [[ {% raw %}{{dateOfCreation}}{% endraw %} #]]CURRENT_DATE()
#### Connecting a SQL question to a dashboard filter
In order for a saved SQL/native question to be usable with a dashboard filter, it must contain at least one field filter. The kind of dashboard filter that can be used with the SQL question depends on the field that you map to the question's field filter(s). For example, if you have a field filter called `{% raw %}{{var}}{% endraw %}` and you map it to a State field, you can map a Location dashboard filter to your SQL question. In this example, you'd create a new dashboard or go to an existing one, click the "Edit" button, add the SQL question that contains your State field filter, add a new dashboard filter or edit an existing Location filter, then click the dropdown on the SQL question card to see the State field filter. [Learn more about dashboard filters here](08-dashboard-filters.md).
To make a clause optional in your native query, type `[[brackets around a {% raw %}{{variable}}{% endraw %}]]`. If `variable` is given a value, then the entire clause is placed into the template. If not, then the entire clause is ignored.
In this example, if no value is given to `cat` from its filter widget or URL, then the query will just select all the rows from the `products` table. But if `cat` does have a value, like `Widget`, then the query will only grab the products with a category type of `Widget`:
[[WHERE category = {% raw %}{{cat}}{% endraw %}]]
{% raw %}
[
[[{
$match: {category: {{cat}}}
},]]
{
$count: "Total"
}
]
{% endraw %}
To use multiple optional clauses you must include at least one regular `WHERE` clause followed by optional clauses, each starting with `AND`.
Example:
WHERE True
[[AND id = {% raw %}{{id}}{% endraw %}]]
[[AND category = {% raw %}{{category}}{% endraw %}]]
When using a field filter, the column name should not be included in the SQL. Instead, the variable should be mapped to a field in the side panel.
Example:
```
SELECT count(*)
FROM products
WHERE True
[[AND {% raw %}{{id}}{% endraw %}]]
[[AND {% raw %}{{category}}{% endraw %}]]
```
### Using an existing question as a sub-query
You can use an existing question in a new query with the following variable syntax:
```
SELECT count(*)
FROM {% raw %}{{#123}}{% endraw %}
```
This will return the number of records returned by the existing question with ID 123. A question's ID is the number at the end of the URL in your browser's location bar, after `/question/`, when viewing the question.
Alternatively, you can select the target question in the sidebar, in the "Question #..." box that appears after typing `{% raw %}{{#}}{% endraw %}` in the query editor. The saved question you select has to be one that's based on the same database as the one you've currently selected in the native query editor.
The same syntax can be used in Common Table Expressions (CTEs), with databases that support them:
```
WITH filtered_products AS {% raw %}{{#123}}{% endraw %}
SELECT count(*)
FROM filtered_products
```
The `{% raw %}{{#123}}{% endraw %}` tag is substituted for the SQL query of the referenced question, surrounded by parentheses.
**Note:** Sub-queries are only supported in SQL databases.
Learn about how to easily and quickly see automatic explorations of your data with Metabase's powerful [x-ray feature](14-x-rays.md).