Non-indexed JSONb operations

[florius0]

Elixir version

Erlang/OTP 27 [erts-15.2.3] [source] [64-bit] [smp:16:16] [ds:16:16:10] [async-threads:1] [jit] Elixir 1.18.3 (compiled with Erlang/OTP 27)

Database and Version

PostgreSQL 17.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 14.2.1 20240912 (Red Hat 14.2.1-3), 64-bit

Ecto Versions

3.12.5

Database Adapter and Versions (postgrex, myxql, etc)

0.20.0

Current behavior

When comparing jsonb paths with literals in where, Ecto generates efficient query using @>:

iex(1)> q = from j in Oban.Job, where: j.args["event_id"] == "123"
iex(2)> Repo.to_sql(:all, q) |> elem(0) |> IO.puts()
SELECT ... FROM "oban_jobs" AS o0 WHERE ((o0."args"@>'{"event_id": "123"}'))

However, if doing the same, but interpolating the value, e.g.:

iex(1)> q = from j in Oban.Job, where: j.args["event_id"] == ^"123"
iex(2)> Repo.to_sql(:all, q) |> elem(0) |> IO.puts()
SELECT ... FROM "oban_jobs" AS o0 WHERE ((o0."args"#>'{"event_id"}') = $1)

Ecto generates the query using #> operator, which isn't supported by GIN indexes

Expected behavior

It seems that this inefficiency comes from https://github.com/elixir-ecto/ecto_sql/blob/ad5e31c13b034564ff8bf3783bfb2e8ab7c0e6d0/lib/ecto/adapters/postgres/connection.ex#L1054, which is restricted only to literals, and not interpolations.

My proposal is to add support for pinned vars and other db columns with following cases:

1. data known in elixir:

   SELECT ... FROM "oban_jobs" AS o0 WHERE (o0."args" @@ '$.event_id #{operator} #{Jason.encode value}')
   

2. equality comparison:

   SELECT ... FROM "oban_jobs" AS o0 WHERE (o0."args" @> jsonb_build_object($1, jsonb_build_object($2, ...)))
   

   where $1..$n-1 is path items and $n is the value. Maybe it would be more optimal case for elixir-data equality comparisons since the query can be prepared

3. containment check

   iex(1)> q = from j in Oban.Job, where: %{event_id: "123"} in j.args
   iex(2)> Repo.to_sql(:all, q) |> elem(0) |> IO.puts()
   SELECT ... FROM "oban_jobs" AS o0 WHERE (o0."args" @> jsonb_build_object('event_id', '123'))
   

   The idea is to re-build the object using jsonb_build_object and jsonb_build_array which would allow for specific values to be non-literals