Non-indexed JSONb operations

Open
#666 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
5/5
Estimated time
Over a week
Newbie friendliness
30/100
Issue type
Feature
Clarity
Mostly clear
Activity status
Stale
Tech stack
elixir, postgresql
Domain
databases

Research direction

Start in lib/ecto/adapters/postgres/connection.ex around line 1054 and reproduce the two Repo.to_sql examples comparing literal and pinned JSONB values. Review how PostgreSQL JSONB paths, pinned values, equality, and containment are represented; done means the proposed non-literal cases generate GIN-index-compatible SQL without regressing existing literal queries.

Written by the indexing model from the issue text.

Description

Kind:Enhancement Note:Discussion
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

Dominant language
Elixir
Stars
658
Forks
343
Avg merge
5h 40m
Merged PRs (30d)
2

Getting set up

This project ships no dev container, Dockerfile or contributing guide, so setting up is up to you: start from its README, and see our first-contribution guide for the general steps.

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from elixir-ecto/ecto_sql

All issues in elixir-ecto/ecto_sql

Similar issues

More Elixir issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.