How to perform fuzzy queries in Superset using Native Filters?

[HWTsang]

I want to implement a feature like LIKE GRC%. Is there a similar functionality available now, or will it be available in the future?

[dosubot[bot]]

Hi @HWTsang! I'm here to help you with any bugs, questions, or contributions you have. Let's solve this together!

Superset currently supports the use of SQL LIKE and ILIKE operators in Native Filters for pattern matching. This means you can perform queries similar to LIKE 'GRC%' to match patterns in your data. The implementation allows for string pattern matching using these operators, as confirmed by the code and test cases in the repository ^[1][2].

If you're looking to implement fuzzy matching or more complex pattern-based filtering, there isn't specific documentation or UI components designed for fuzzy matching in Superset's Native Filters at this time. However, you can use the existing LIKE and ILIKE operators for basic pattern matching needs.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}

[HWTsang]

I can use the jinja template to achieve this, but I can only achieve one of in and like. How can I achieve both in and like in the official example?

[dosubot[bot]]

To achieve both "IN" and "LIKE" functionality in a SQL query using a Jinja template in Apache Superset, you can use the get_filters function. This function allows you to retrieve filters applied to a specific column, including the operator used. Here's an example of how you can implement this in a Jinja template:

SELECT *
FROM your_table
WHERE 1=1
{%- for filter in get_filters('your_column', remove_filter=True) -%}
    {%- if filter.get('op') == 'IN' -%}
        AND your_column IN ( {{ filter.get('val')|where_in }} )
    {%- elif filter.get('op') == 'LIKE' -%}
        AND your_column LIKE {{ "'" + filter.get('val') + "'" }}
    {%- endif -%}
{%- endfor -%}

This template iterates over the filters applied to your_column, checking the operator for each filter. If the operator is "IN", it constructs an IN clause using the where_in filter to format the values correctly. If the operator is "LIKE", it constructs a LIKE clause. The remove_filter=True argument ensures that the filter is marked as processed and removed from the outer query, which is useful when the filter should only apply to the inner query ^[1][2][3].

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}

[HWTsang]

But the method of defining LIKE is not found in the Native Filter on the page(Add and edit filters. FILTER TYPE). How can I get filter.get('op') == 'LIKE' in jinja?@dosu.

[dosubot[bot]]

To use the LIKE operator in a Jinja template with Native Filters in Superset, you need to ensure that the filter is configured to use the LIKE operator in the Superset UI. The LIKE operator is available in the Superset UI for Native Filters and is mapped to the SQL LIKE operator ^[1].

When you define a filter in the Superset UI, you should be able to select the LIKE operator from the list of available operators. Once the filter is set up with the LIKE operator, you can use the get_filters function in your Jinja template to retrieve the filter and check for the LIKE operator as shown in the example provided earlier.

If the LIKE operator is not appearing in the UI, ensure that your Superset instance is up to date and that there are no restrictions or customizations in your environment that might be limiting the available operators.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}