Search code examples
rubyruby-on-rails-3postgresqlhstorejsonb

How do you use the "LIKE" query for jsonb column types in PostgreSQL?

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:

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

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

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%')

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

Solution

  • 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