Simple Question view: filter array contains

[dideler_rkki]

On a table that has an array column, how can you filter on the array containing a value?

For example, I have a column that's a varchar array. The filtering options are

• Is
• Is not
• Is empty
• Not empty

When going to that field's settings via the admin panel, I have some options to change how it's filtering works:

But no matter what filtering option I choose for the column, I can't search for a value that's contained in the array. It searches my varchar input directly against varchar which causes an error.

ERROR: operator does not exist: character varying[] = character varying Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 1930

Could we have a "contains" filter in the simple question view, or if it exists, how do we enable it?

P.S. I've labelled this is as a feature request, because it's possible to do when changing to the SQL view, but we use Metabase precisely for those in the company that aren't proficient with SQL.

[flamber]

Hi @dideler_rkki
Please post “Diagnostic Info” from Admin > Troubleshooting.
And which database are you querying?
Metabase does not support looking inside of JSON type columns (or similar, but I don’t know what database has “arrays” in varchar), so you would need to create a database view, so Metabase just sees a simple table with the array expanded into columns.

[dideler_rkki]

Hi @flamber,

It’s a PostgreSQL database and the column is of type varchar array, not json(b) array.

emails character varying(255)[] NOT NULL

I would expect a “contains” filter to generate a SQL clause such as

WHERE ("emails"::TEXT ILIKE '%user@example.com%')

Here’s the diagnostic info:

{
  "browser-info": {
    "language": "en-US",
    "platform": "MacIntel",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.149 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.5+10",
    "java.vendor": "AdoptOpenJDK",
    "java.vendor.url": "https://adoptopenjdk.net/",
    "java.version": "11.0.5",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.5+10",
    "os.name": "Linux",
    "os.version": "4.14.154-128.181.amzn2.x86_64",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "postgres",
      "h2"
    ],
    "hosting-env": "unknown",
    "application-database": "h2",
    "application-database-details": {
      "database": {
        "name": "H2",
        "version": "1.4.197 (2018-03-18)"
      },
      "jdbc-driver": {
        "name": "H2 JDBC Driver",
        "version": "1.4.197 (2018-03-18)"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2020-04-02",
      "tag": "v0.35.1",
      "branch": "release-0.35.x",
      "hash": "e67f169"
    },
    "settings": {
      "report-timezone": "UTC"
    }
  }
}

[flamber]

@dideler_rkki I guess until the jsonb columns are supported, you’re not going to see arrays either:
https://github.com/metabase/metabase/issues/708 - upvote by clicking on the first post

You should migrate away from H2 if you’re using Metabase in production:
https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html

[doncube]

Hi! has this been resolved? i have the same issue. I cannot seem to filter anything in an array column

[flamber]

@doncube Arrays are coming in later versions. JSON support has been added to Postgres.
https://www.metabase.com/roadmap/
https://github.com/metabase/metabase/issues/2974 - upvote by clicking on the first post

[doncube]

Hi, apologies. I'm not a programmer/engineer. can you explain this further? all i really need is to filter a column that is currently set as an array. i can't seem to do it.

[flamber]

@doncube You would either need to use SQL, or cast the column to a string-type. Metabase does not support arrays currently in the GUI.

[Lina]

Hi, does the issue of not being able to view array content for JSON type is solved yet ?

[RobertoMaurizzi]

0.46.1 still doesn't appear to have support for ARRAY fields*... Since I was working on a view created for Metabase, I changed the ARRAY field to a string then I simply check if the user input is contained in the string... the only problem is: the filter, not being of type Field, won't allow me to use a dropdown interface to select a value from the available ones (possibly taken from another question).

* They work, but you can only select the full value of a row, so filtering all rows where the ARRAY contains 2023 (for example) isn't possible with a Field filter (and regular text or number filters won't allow me to see the available value, unless I missed something...)

UPDATE: I ended up using database functions to unpack the values of the array in a Question, then used that question as the source for the Filters of both the Questions and the Dashboard where I wanted to display those data. A bit convoluted, but it worked.
Only missing functionality compared to having real support for a Field Filter supporting Arrays is I can't select multiple values.

[juliankmazo]

What’s the status on this?

I have a table that comes from Snowflake

Locations is an array

	LOCATIONS ARRAY,

But seems like I cannot do much with it. Does Metabase really not support arrays?

[dwhitemv]

No, no movement on this feature request.

If you need to work with arrays or other collections in Snowflake, look into the FLATTEN function. This works like the SQL standard UNNEST(), which expands an array or other collection into multiple rows.

This is part of the problem with arrays, there is no standard way to interact with them. SQL99 defined the qualities of a (one-dimensional) array and suggested a one-based subscript indexing, but left out important details like syntax for defining arrays. Every database engine has therefore invented their own and made it difficult to write tools with good compatibility without having to special-case each database engine.

On top of that, array support is also not universal; Sybase & derivatives like MS SQL Server don’t support them.

[juliankmazo]

Gotcha. Thanks for the explanation

I didn’t want to explode the table. Wanted to see if I could keep it one row per business object.

But seems like it's not that bad. Thanks!

[timorkal]

Hey Metabase, why is it taking you so long to implement such a critical feature?
the issue was opened 5 years ago… people are getting old.