SQL - Error Using Input Variables in JSON function

Hi Metabase community!
My DB is postgres and I'm having a strange error from this SQL below. It works fine when I run it in my own SQL client (where I replace the token {{var_id}} with a value like 1 2 5 10 30).

However in Metabase, I am getting this error:
The column index is out of range: 1, number of columns: 0.

The context is that I want to have a variable input that a user can enter. The variable should allow the user to enter a single value, a comma-separated list of values, or a space-separated list of values. The SQL below is basically supposed to replace spaces in the input with commas, and then convert that entire string into a list that can be fed into the IN clause as a sub-query.

Again, this works fine in my SQL client. But in Metabase it fails with that error.

select *
from table t
where t.id in (
	select json_array_elements(
		CONCAT('[', replace('{{var_id}}', ' ' , ',') , ']')::json	
	)::text::int
);

or

select *
from table t
where t.id in (
	select json_array_elements(
		CONCAT('[', replace('1 2 5 10 30', ' ' , ',') , ']')::json	
	)::text::int
);

Any ideas? Any other ways I can support this kind of functionality? Thanks!

Hi @ariel.kemelman
All variables are already escaped, when being inserted - otherwise it would be a very thing.
For reference: https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html

See more examples here:
https://github.com/metabase/metabase/issues/4728 - upvote by clicking :+1: on the first post

Thanks, not sure how I missed this before, but simply removing the quotes around the variables seemed to do the trick!