Querying jsonb field with @> through Postgrex adapter

Question

I'm trying to query jsonb field via Postgrex adapter, however I receive errors I cannot understand.

Notification schema

def all_for(user_id, external_id) do
  from(n in __MODULE__,
    where: n.to == ^user_id and fragment("? @> '{\"external_id\": ?}'", n.data, ^external_id)
  )
  |> order_by(desc: :id)
end

it generates the following sql

SELECT n0."id", n0."data", n0."to", n0."inserted_at", n0."updated_at" FROM "notifications"
AS n0 WHERE ((n0."to" = $1) AND n0."data" @> '{"external_id": $2}') ORDER BY n0."id" DESC

and then I receive the following error

↳ :erl_eval.do_apply/6, at: erl_eval.erl:680
** (Postgrex.Error) ERROR 22P02 (invalid_text_representation) invalid input syntax for type json. If you are trying to query a JSON field, the parameter may need to be interpolated. Instead of

    p.json["field"] != "value"

do

    p.json["field"] != ^"value"


    query: SELECT n0."id", n0."data", n0."to", n0."inserted_at", n0."updated_at" FROM "notifications" AS n0 WHERE ((n0."to" = $1) AND n0."data" @> '{"external_id": $2}') ORDER BY n0."id" DESC

Token "$" is invalid.
    (ecto_sql 3.9.1) lib/ecto/adapters/sql.ex:913: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto_sql 3.9.1) lib/ecto/adapters/sql.ex:828: Ecto.Adapters.SQL.execute/6
    (ecto 3.9.2) lib/ecto/repo/queryable.ex:229: Ecto.Repo.Queryable.execute/4
    (ecto 3.9.2) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3

however if I just copypaste generated sql to psql console and run it, it will succeed.

SELECT n0."id", n0."data", n0."to", n0."inserted_at", n0."updated_at" FROM "notifications" AS n0 WHERE ((n0."to" = 233) AND n0."data" @> '{"external_id": 11}') ORDER BY n0."id" DESC
notifications-# ;
 id |        data         | to  |     inserted_at     |     updated_at
----+---------------------+-----+---------------------+---------------------
 90 | {"external_id": 11} | 233 | 2022-12-15 14:07:44 | 2022-12-15 14:07:44
(1 row)

data is jsonb column

   Column    |              Type              | Collation | Nullable |                  Default
-------------+--------------------------------+-----------+----------+-------------------------------------------
 data        | jsonb                          |           |          | '{}'::jsonb

What am I missing in my elixir notification query code?

I think pg doesn't like the `$` in the string `'{"external_id": $2}'` can you try change it to concatenating parts of the string `'{"external_id": ' || $2 || '}'`? — Yuri Golobokov, Dec 16 '22 at 00:56
$1 and $2 generated by `Postgrex` library, I can't modify them from Ecto.Query module — zhisme, Dec 16 '22 at 07:58
yeah, I meant `fragment("? @> '{\"external_id\": ' || ? || '}'", n.data, ^external_id)` — Yuri Golobokov, Dec 16 '22 at 20:39
@YuriGolobokov [debug] QUERY ERROR source="notifications" db=0.8ms queue=3.4ms idle=89.0ms SELECT n0."id", n0."data" FROM "notifications" AS n0 WHERE ((n0."to" = $1) AND n0."data" @> '{"external_id": ' || $2 || '}') ORDER BY n0."id" DESC [233, 10] ↳ :erl_eval.do_apply/6, at: erl_eval.erl:685 ** (Postgrex.Error) ERROR 22P02 (invalid_text_representation) actually it is the same error — zhisme, Dec 19 '22 at 08:08

score 0 · Answer 1 · answered Dec 20 '22 at 10:21

Searching for solution I came across only using raw sql statement, as I couldn't figure out what's wrong with my query when it gets passed through Postgrex

so as a solution I found the following:

  def all_for(user_id, external_ids) do
    {:ok, result} =
      Ecto.Adapters.SQL.query(
        Notifications.Repo,
        search_by_external_id_query(user_id, external_ids)
      )

    Enum.map(result.rows, &Map.new(Enum.zip(result.columns, &1)))
  end

  defp search_by_external_id_query(user_id, external_id) do
    """
    SELECT * FROM "notifications" AS n0 WHERE ((n0."to" = #{user_id})
    AND n0.data @> '{\"external_id\": #{external_id}}')
    ORDER BY n0."id" DESC
    """
  end

But as a result I'm receiving Array with Maps inside not with Ecto.Schema as if I've been using Ecto.Query through Postgrex, so be aware.

Querying jsonb field with @> through Postgrex adapter

1 Answers1