通过Postgrex适配器查询jsonb字段时遇语法错误求助

问题场景 #

使用Ecto的Postgrex适配器查询jsonb字段时出现语法错误，相关信息如下：

原查询代码 #

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

生成的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

报错信息 #

↳ :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

验证情况 #

手动替换SQL中的参数值后，在psql控制台执行可成功返回结果：

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;

执行结果：

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字段为jsonb类型：

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

问题原因 #

原代码中，fragment内的JSON字符串'{"external_id": ?}'会被PostgreSQL当作完整的JSON文本解析，占位符$2被包含在JSON字符串内部后，PostgreSQL无法识别其为参数占位符，反而将$视为JSON中的无效字符，触发语法错误。

解决方案 #

有两种可行的修改方式：

方法1：传递完整的JSON对象作为参数 #

直接构造包含external_id的Map，让Ecto自动将其转换为jsonb参数：

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

Ecto会将^%{"external_id" => external_id}转换为合法的jsonb参数，Postgrex能正确识别并完成匹配。

方法2：使用PostgreSQL的jsonb_build_object函数 #

通过PostgreSQL内置函数动态构建jsonb对象，确保参数被正确插值：

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

jsonb_build_object会将传入的参数external_id转换为jsonb结构的一部分，避免了手动拼接JSON字符串的问题。

内容的提问来源于stack exchange，提问作者zhisme