如何在PostgreSQL中对jsonb列类型使用“LIKE”查询?

For hstore columns in PostgreSQL databases, I know I can use a "LIKE" query like so in Ruby on Rails to search for names that include a certain string:

对于PostgreSQL数据库中的hstore列，我知道我可以使用Ruby on Rails中的“LIKE”查询来搜索包含特定字符串的名称:

  Product.where("hstore_data -> 'author' LIKE '%billy%'")

I tried that for a jsonb column type, but got this error:

我尝试了jsonb列类型，但是得到了这个错误:

ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR: operator does not exist: jsonb ~~ unknown
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. : SELECT "products".* FROM "products" WHERE (jsonb_data -> 'author' LIKE '%billy%')

ActiveRecord:::StatementInvalid: PG::UndefinedFunction: ERROR: operator不存在:jsonb ~~ unknown HINT:没有操作符匹配给定的名称和参数类型。您可能需要添加显式类型转换。:选择“产品”。*从“产品”(jsonb_data ->的作者“%billy%”)

Is there a way to use "LIKE" correctly for jsonb column types?

对于jsonb列类型，是否有正确使用“LIKE”的方法?

1 个解决方案

#1

You can try this

你可以试试这个

Hope you have

希望你有

product.jsonb_data = {
      author: "billynando"
   }

Then

然后

Product.where("jsonb_data ->> :key LIKE :value",
  key: "author", value: "%billy%"
)

More here

更多的在这里

#1