Native queries

Understand how to write database-specific queries for MongoDB, PostgreSQL, MySQL, and other databases in Metabase.

Native queries let you use your database’s native query language instead of SQL. This is particularly useful for NoSQL databases like MongoDB, or when you need database-specific features.

What are native queries?

Native queries are written in your database’s native language:

SQL databases

PostgreSQL, MySQL, SQL Server, Oracle, etc. use SQL

MongoDB

Uses MongoDB query language (MQL) for aggregations and queries

Other databases

Druid, Presto, BigQuery each have their own dialects and features

When to use native queries

Choose native queries when you need:

Database-specific features

Use features unique to your database:

• PostgreSQL: JSONB operations, array functions, window functions
• MongoDB: Aggregation pipeline, MapReduce
• BigQuery: ML functions, geography operations
• MySQL: Full-text search, spatial data

Performance optimization

Write queries optimized for your specific database:

• Use database-specific indexes
• Leverage query hints
• Optimize for your database’s query planner

Complex transformations

Perform operations the query builder can’t express:

• Recursive queries (CTEs)
• Complex conditional logic
• Advanced aggregations
• Custom functions

SQL databases

For SQL databases (PostgreSQL, MySQL, SQL Server, Oracle, etc.), you write standard SQL with database-specific extensions.

Common SQL patterns

Basic query

SELECT 
  customer_id,
  order_date,
  total_amount
FROM orders
WHERE order_date >= '2024-01-01'
ORDER BY order_date DESC

Aggregation

SELECT 
  date_trunc('month', order_date) as month,
  count(*) as order_count,
  sum(total_amount) as revenue
FROM orders
GROUP BY month
ORDER BY month

Joins

SELECT 
  c.name,
  count(o.id) as order_count,
  sum(o.total) as total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.name
ORDER BY total_spent DESC
LIMIT 10

Window functions

SELECT 
  order_date,
  total_amount,
  sum(total_amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) as running_total
FROM orders
ORDER BY order_date

PostgreSQL-specific features

JSON operations

SELECT 
  id,
  data->>'name' as name,
  (data->>'age')::int as age
FROM users
WHERE data ?? 'premium'

For PostgreSQL JSON operations, use ?? instead of ? due to JDBC limitations. The single ? is interpreted as a parameter placeholder.

MySQL-specific features

Full-text search

SELECT 
  id,
  title,
  MATCH(title, content) AGAINST ('search term' IN BOOLEAN MODE) as relevance
FROM articles
WHERE MATCH(title, content) AGAINST ('search term' IN BOOLEAN MODE)
ORDER BY relevance DESC

MongoDB queries

MongoDB uses its own query language based on JavaScript:

Aggregation pipeline

Basic aggregation

db.orders.aggregate([
  {
    $match: {
      order_date: { $gte: ISODate("2024-01-01") }
    }
  },
  {
    $group: {
      _id: "$status",
      total: { $sum: 1 },
      revenue: { $sum: "$total_amount" }
    }
  },
  {
    $sort: { revenue: -1 }
  }
])

You can use Metabase variables in MongoDB queries just like in SQL. Wrap variable names in double curly braces: {{variable_name}}.

Adding variables to native queries

Make your native queries interactive with variables:

1. Add variable syntax

   Include variables in your query using double curly braces:

   WHERE status = {{status}}
   
2. Configure variable

   When you add a variable, Metabase shows the variables sidebar where you can set:

   • Variable type (field filter, text, number, date)
   • Display label
   • Widget type (dropdown, search, input)
   • Default value
3. Test your variable

   Run the query with different values to ensure it works correctly.

Variable types

Field filters

Smart filters that connect to database fields:

SELECT *
FROM orders
WHERE {{created_date}}
  AND {{status}}

Benefits:

• Automatic filter UI based on field type
• Better performance with proper SQL generation
• Works with dashboard filters

Basic variables

Simple value substitution:

SELECT *
FROM orders
WHERE total > {{min_total}}
LIMIT {{row_limit}}

Good for:

• Query parameters
• Configurable limits
• Simple value filters

Table variables

Select which table to query:

SELECT *
FROM {{table_name}}
WHERE active = true

Useful for:

• Multi-tenant queries
• Similar tables with same schema
• Dynamic data sources

Optimizing native queries

Use indexes

Ensure your WHERE clauses and JOIN conditions use indexed columns for better performance.

Limit result sets

Use LIMIT or TOP clauses to prevent accidentally returning millions of rows.

Select specific columns

Avoid SELECT * in production queries. List only the columns you need.

Test with representative data

Test queries against realistic data volumes to identify performance issues.

Use EXPLAIN

Analyze query execution plans to understand and optimize performance.

Cache expensive queries

Enable result caching for queries that run frequently but data changes infrequently.

Database-specific considerations

BigQuery

• Use Standard SQL syntax
• Be mindful of costs - queries scan entire columns
• Leverage partitioned tables for better performance
• Use approximate aggregation functions when exact counts aren’t needed

SELECT 
  DATE(timestamp) as date,
  APPROX_COUNT_DISTINCT(user_id) as users
FROM events
WHERE _PARTITIONTIME >= TIMESTAMP('2024-01-01')
GROUP BY date

Snowflake

• Queries are case-insensitive by default
• Use warehouse-appropriate query complexity
• Leverage result caching
• Consider clustering for large tables

SELECT 
  date_trunc('month', order_date) as month,
  count(*) as orders
FROM orders
WHERE order_date >= dateadd('month', -12, current_date())
GROUP BY month

Redshift

• Optimize for columnar storage
• Use DISTKEY and SORTKEY in table design
• Avoid nested loops - use hash joins
• Vacuum and analyze tables regularly

SELECT 
  c.name,
  count(o.id) as orders
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE c.created_at >= '2024-01-01'
GROUP BY c.name

Limitations of native queries

Native queries have some limitations compared to query builder questions:

• Limited drill-through: Can’t drill down to underlying records or change groupings
• No pivot tables: Pivot table visualization not available for native queries
• Manual visualization: Must manually select chart type (not auto-selected)
• Harder to modify: Requires SQL knowledge to make changes

Best practices

1. Start with query builder

   Use the query builder first. Only switch to native queries when you hit limitations.

2. Document your queries

   Add comments explaining:

   • What the query does
   • Why specific approaches were used
   • Any gotchas or important notes
3. Use descriptive aliases

   Name columns clearly so visualizations and results are self-explanatory.

4. Test thoroughly

   Verify results with known test cases before sharing or adding to dashboards.

5. Add variables for reusability

   Make queries flexible with variables so others can adjust parameters without editing SQL.

6. Format for readability

   Use consistent formatting, indentation, and capitalization for maintainability.

Converting from query builder

You can convert query builder questions to native SQL:

1. View the generated SQL

   Click View SQL in the top right to see what SQL the query builder generates.

2. Convert to SQL

   Click the Console icon and select Convert this question to SQL.

3. Modify as needed

   Edit the SQL to add features not available in the query builder.

Converting to SQL is one-way. You cannot convert a SQL question back to the query builder.

Using native queries on dashboards

To make native queries work with dashboard filters:

1. Add variables to your query
2. Add the question to your dashboard
3. Create dashboard filters
4. Map filters to your query variables

Native queries require at least one variable to connect to dashboard filters. Without variables, the question will appear on the dashboard but won’t respond to filters.

Next steps

SQL editor

Learn about the SQL editor interface and features

Query builder

Understand when to use the query builder instead

Dashboard filters

Connect native queries to dashboard filters