Ecto“左向右"使用片段查询 [英] Ecto "left IN right" query using a fragment

问题描述

我想使用postgres IN运算符(带有Ecto库)查询jsonb字段

I would like to query a jsonb field using postgres IN operator (with Ecto library)

此代码使用简单的=运算符:

This code work with a simple = operator:

from a in query, where: fragment("?->>'format' = ?", a.properties, "foo")

但是我无法做出任何以下尝试:

But I cannot make any of these attempts to work:

from a in query, where: fragment("?->>'format' IN ?", a.properties, ["foo", "bar"])
from a in query, where: fragment("?->>'format' IN (?)", a.properties, ["foo", "bar"])
from a in query, where: fragment("?->>'format' IN ?", a.properties, "('foo', 'bar')"])

有什么主意吗?

推荐答案

除了Patrick的出色回答外，请记住，您也只能将查询的一部分放在一个片段中.例如，您可以将其重写为:

Besides Patrick's excellent response, keep in mind you can put only part of a query in a fragment too. For example, you can rewrite it to:

from a in query, where: fragment("?->>'format', a.properties) in ["foo", "bar"]

如果将片段放在宏中，甚至可以得到可读的语法:

If you put the fragment in a macro, you can even get a readable syntax:

defmacro jsonb_get(left, right) do
  quote do
    fragment("?->>?", unquote(left), unquote(right))
  end
end

现在:

from a in query, where: jsonb_get(a.properties, "format") in ["foo", "bar"]

这篇关于Ecto“左向右"使用片段查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！