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!