boriscy
Pass a list or other type to custom Ecto query
I have a query like this one
select o.id::text, o.name, o.geom, o.tags, o.address, o.open
from organizations o, jsonb_array_elements(tags) as t
where t->>'text' in($1)
Is there any way I can pass a list (Array) to the query, I get an error if I pass a list this way Ecto.Adapters.SQL.query(MyRepo, sql, ["tag1"])
Most Liked
belaustegui
You should use a query fragment to wrap the JSON access:
# Assuming that the variable input = ["tag1"]
select o.id::text, o.name, o.geom, o.tags, o.address, o.open
from organizations o, jsonb_array_elements(tags) as t
and fragment("(?->>?)", t, "text") in ^input
boriscy
As you can see in the example the fragment is used in the where clause so to make things work I should create a view.
boriscy
It doesn’t work if I use in ($1) but If I change the query to
select o.id::text, o.name, o.geom, o.tags, o.address, o.open
from organizations o, jsonb_array_elements(tags) as t
where t->>'text' = any($1)
will work I would like to know if there is a way to make work in the future and would be really nice to have instead of numbers named params like ..where t->>'text' = any($tags)", [tags: ["tag1"]], thanks for the help.
josevalim
Using any is a good call. You should be able to make it work with fragments too by doing fragment("t->>'text' = any(?)", ^list).
The query API is a direct proxy to the database, so there are no plans to support something as $tags unless the database also supports it.







