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

ecto_sql/lib/ecto/adapters/postgres/connection.ex

Line 1054 in ad5e31c

defp expr({:==, _, [{:json_extract_path, _, [expr, path]} = left, right]}, sources, query)

, 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

[josevalim]

When the query is parameterized, we don't really know the value, so we don't really have the value to encode which limits the amount of optimizations we can do. However, please do try a pull request, if you can improve something without breaking code, we will gladly accept it!