SQL variable for jsonb array

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