Here is an example of both methods, the array per field and the jsonb_to_recordset, using ecto.

Array per field + unnest:

ids =[ 1,  2,  1]
ages=[10, 20, 30]

from x in Friends.Person, 
inner_join: j in fragment("SELECT distinct * from unnest(?::int[],?::int[]) AS j(id,age)", ^ids, ^ages),
        on: x.id==j.id and x.age==j.age,
select: [:name]

jsonb_to_recordset:

list = [%{id: 1, age: 10}, 
        %{id: 2, age: 20}, 
        %{id: 1, age: 30}]

from x in Friends.Person,
inner_join: j in fragment("SELECT distinct * from jsonb_to_recordset(?) AS j(id int,age int)", ^list),
        on: x.id==j.id and x.age==j.age,
select: [:name]

Late to the party, but I found a hack that builds and expands code at runtime and uses proper parameters instead of interpolation:

def tuple_in(fields, values) do
  fields = Enum.map(fields, &quote(do: field(x, unquote(&1))))
  values = for v <- values, do: quote(do: fragment("(?)", splice(^unquote(Tuple.to_list(v)))))
  field_params = Enum.map_join(fields, ",", fn _ -> "?" end)
  value_params = Enum.map_join(values, ",", fn _ -> "?" end)
  pattern = "(#{field_params}) in (#{value_params})"

  quote do
    dynamic(
      [x],
      fragment(unquote(pattern), unquote_splicing(fields), unquote_splicing(values))
    )
  end
  |> Code.eval_quoted()
  |> elem(0)
end

# usage
from p in Product, where: ^tuple_in([:category_id, :collection_id], [{1, 100}, {2, 200}])

# sql
SELECT
	p0."id",
	p0."title"
# more columns...
FROM
	"product" AS p0
WHERE ((p0."category_id", p0."collection_id")
	IN(($1, $2), ($3, $4));

Using fragment("(?) in (?)", splice(^[p.category_id, p.collection_id]), splice(^values)) does not work because p is out of scope inside of a ^ statement.

My solution uses a dynamic statement and instead creates the field bindings with field/2.

Code.eval_quoted is a bit unorthodox but required to let Ecto
build queries using AST. I hope this helps!

That isn’t logically equivalent. Suppose you have WHERE (col1, col2) IN ((1, 10), (2, 20)). If you do WHERE col1 in (1, 2) AND col2 IN (10, 20) that would allow a row that had col1 as 1 and col2 as 20, which would not have been allowed by the first query.

@victorolinasc I’ve had some success using unnest and JOIN for here’s an example:

    SELECT p.result_order as result_order, si.*
      FROM unnest($1::text[], $2::timestamp[], $3::timestamp[], $4::integer[])
      AS p(slug, starts_at, ends_at, result_order)
    LEFT JOIN sensors AS s ON s.slug = p.slug
    LEFT JOIN sensor_installations AS si
      ON s.id = si.sensor_id
      AND (si.activated_at, COALESCE(si.deactivated_at, p.ends_at + INTERVAL '5 minutes')) OVERLAPS
          (p.starts_at, p.ends_at)
    ORDER BY p.result_order ASC

The nice thing is that you get to at least use parameterized queries instead of interpolation, and if you join on multiple columns it should be perfectly able to use compound indices. The downside of course is that you’re still writing raw SQL. This is a pretty old query for us, you might be able to write the unnest as raw SQL but then use that as a subquery in a regular ecto query. Not sure.

I landed here from some googling trying to find how to pass a list of maps into ecto as an inline table that could be used in a join expression.

I almost ended up doing the clever jsonb_to_recordset trick, however shortly after I luckily discovered values/2.

This probably didn’t exist at the time, but in case anyone else lands here after some searching, I hope leaving this here helps.

I suspect part of the challenge may be that the row type like (a, b, c) is an anonymous type in Postgres. And therefore interpolating may require typing on both the postgres and elixir sides (this is speculation on my part). I don’t have a repo handy right now to even see how Ecto handle select * from table where (a,b,c) in (1,2,3) type queries.

Another angle might be to try an = ANY type query which in most cases is semantically the same as an in query. But it would allow you to provide an elixir list as a parameter which may help make some progress.

I appreciate these are more questions, not answers.