Pass a list or other type to custom Ecto query

Questions / Help
,
1

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"])

2

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
1 Like
3

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?

4

Oh, I see the real problem now :thinking:

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.

5

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.

1 Like
6

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.

7

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.

1 Like
8

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.

1 Like