My table has a column "ids" which is a jsonb array. e.g. '[1, 2, 3]'
I tried to create a text variable in a SQL like this to get any row that contains the number:
SELECT * FROM table WHERE column @> {{id}};
However, it throws an error when I run the query:
ERROR: operator does not exist: jsonb @> character varying Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
I am wondering how does the SQL looks like after passing the variable?
Hi @llg3888
Like the database error says, you'll need to explicitly cast, so something like this:
SELECT * FROM table WHERE column @> {{id}}::jsonb;
When you are having problems with variables, then replace them with hardcoded value of exactly what it is supposed to replace - meaning {{id}}
=> '[1, 2, 3]'
(note the quotes)
Thanks this works. Is it possible to perform such filtering under a question instead of raw SQL?
I got this error:
ERROR: function lower(jsonb) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 1848
@llg3888 Metabase supports JSON(B) unfolding on Postgres since 0.43, but does currently not support Arrays: https://www.metabase.com/roadmap/
1 Like