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

Question

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?

Answer 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

Answer 2

I know the answer is already accepted but this Gist might help to have.