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 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.
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.
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 '%firstname.lastname@example.org%')
Here’s the diagnostic info:
"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."
"java.runtime.name": "OpenJDK Runtime Environment",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"version": "1.4.197 (2018-03-18)"
"name": "H2 JDBC Driver",
"version": "1.4.197 (2018-03-18)"
@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:
Hi! has this been resolved? i have the same issue. I cannot seem to filter anything in an array column
@doncube Arrays are coming in later versions. JSON support has been added to Postgres.
https://github.com/metabase/metabase/issues/2974 - upvote by clicking on the first post
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.
@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.
Hi, does the issue of not being able to view array content for JSON type is solved yet ?
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.