Why am I getting an error when trying to build a filter with VarChar?

Issue

The user encountered an error when attempting to build a filter in an Insights dashboard. The error message stated: 

operator does not exist: character varying = text[]
LINE 34: Where org.plant_run$raw.id = ARRAY[]::text[]
                                       ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

This suggests an issue with filtering a VarChar data type.

Environment

• Product: Benchling Insights
• Area: Filter creation in dashboard
• Field Type: VarChar (character varying)

Resolution

To resolve the issue of filtering a VarChar data type in Benchling Insights, try the following solutions:

1. Use the ?! Operator:

    

   org.plant_run$raw.id ?! {{plant_run}}

2. Combine ?! Operator with Cardinality Check:

    

   org.plant_run$raw.id ?! {{plant_run}} OR CARDINALITY({{plant_run}}) = 0

3. Use the ?& Operator:

    

   org.plant_run$raw.id ?& {{plant_run}}

4. Combine ?& Operator with Cardinality Check:

    

   org.plant_run$raw.id ?& {{plant_run}} OR CARDINALITY({{plant_run}}) = 0

Alternatively, the user mentioned a workaround by using:

These steps should allow you to filter VarChar fields without encountering the operator mismatch error.

Cause

The error occurs because SQL does not automatically match character varying with text[] without explicit type casting. Using operators like ?! and ?& or incorporating cardinality checks helps align the data types properly for filtering operations.