通过PostgreSQL适配器使用@>查询jsonb字段

8xiog9wr 于 2022-12-26 发布在 PostgreSQL

关注(0)|答案(1)|浏览(160)

我试图通过PostgreSQL适配器查询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
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)

数据是jsonb列

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

我在我的 Elixir 通知查询代码中遗漏了什么？

postgresql

来源：https://stackoverflow.com/questions/74813697/querying-jsonb-field-with-through-postgrex-adapter

1条答案

按热度按时间

l2osamch1#

搜索解决方案时，我只使用原始sql语句，因为我无法找出查询在通过Postgrex时出现了什么问题
所以我找到了以下解决方案：

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

但结果是，我收到的Array里面有Maps，而不是Ecto.Schema，就好像我一直在使用Ecto.Query到Postgrex一样，所以要注意。

赞(0）回复(0）举报 2022-12-26

我来回答

通过PostgreSQL适配器使用@>查询jsonb字段

1条答案

相关问题

热门标签

最新问答