Metabase - create filter from json field

ONLINECAST

2 min read

·

05-10-2024

6

0

Share

Filtering Metabase Reports Based on JSON Fields: A Comprehensive Guide

Metabase is a powerful tool for creating insightful data visualizations and dashboards. But what happens when your data contains information stored as JSON fields? This can make filtering your reports a bit more challenging.

Let's dive into how to effectively filter your Metabase reports using data stored in JSON fields.

The Challenge: Filtering JSON Data

Imagine you have a table in your database containing a column named properties storing JSON data like this:

{
  "color": "blue",
  "size": "medium",
  "type": "shirt"
}

You want to create a filter in Metabase to display only items with a specific color value, but Metabase doesn't directly support filtering on JSON objects.

The Solution: Unleashing the Power of JSON_EXTRACT

Metabase offers a solution by leveraging the JSON_EXTRACT function. This function allows you to access specific values within your JSON data.

Here's how to filter your data:

1. Create a Filter: In your Metabase report, create a new filter.
2. Select the JSON Field: Choose the column that contains your JSON data (properties in our example).
3. Utilize JSON_EXTRACT: Within the filter expression, use JSON_EXTRACT(properties, "$.color"). This extracts the value of the color key within the properties field.
4. Apply Your Filter: Now you can set up your filter using the extracted value, e.g., "equals 'blue'" or "is not null."

Example: Filtering Products Based on Color

Let's say you have a table called products with a properties column containing JSON data similar to the example above. You want to create a report showing only products that are blue.

Filter Expression:

JSON_EXTRACT(properties, "$.color") = "blue"

Result: Your report will only display products where the color within the properties field is "blue."

Additional Tips & Tricks:

• Nested JSON: For nested JSON objects, use a dot notation within the JSON_EXTRACT function. For example, to extract the category value from a nested details object: JSON_EXTRACT(properties, "$.details.category")
• Multiple Filters: You can create multiple filters based on different JSON keys to narrow down your results further.
• Custom Filters: Metabase allows you to create custom filter expressions using the JSON_EXTRACT function in combination with other functions like CASE WHEN. This lets you filter based on complex conditions.

Conclusion: Mastering JSON Filtering in Metabase

By understanding the JSON_EXTRACT function and utilizing it effectively, you can easily filter your Metabase reports based on data stored in JSON fields. This unlocks powerful insights and allows you to create truly tailored reports based on your specific data needs.

Remember: The specific syntax and implementation may vary slightly depending on your database system. Refer to your database documentation for specific details on working with JSON data.