Simple Question view: filter array contains

Feedback and Requests
1

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:

image
image1198×482 39.6 KB

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.

2

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.

3

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"
    }
  }
}
4

@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 :+1: 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

5

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

6

@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 :+1: on the first post

7

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.

8

@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.

9

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

10

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.