Pass a list or other type to custom Ecto query

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

I have used fragments in the past but as you can see
from organizations o, jsonb_array_elements(tags) as t
How can you make this part of Ecto.Query, I can do from o in Organization but how should I add the json_array_elements fucntion from postgresql?

Oh, I see the real problem now

A fragment lets you execute a SQL fragment directly into the database, maybe you could try wrapping the jsonb_array_elements function call into another fragment.

select o.id::text, o.name, o.geom, o.tags, o.address, o.open
from organizations o, fragment("jsonb_array_elements(?)", tags) as t
 and fragment("(?->>?)", t, "text") in ^input

I’m not sure if that would work, but the docs show similar examples using other functions like lower.

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.

Shouldn’t it be Ecto.Adapters.SQL.query(MyRepo, sql, [["tag1"]])?

The third argument is a list of parameters and the first argument is a list.

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.

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.