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?

Lorenzoloresz

might help – Streetcar

Just use the operator, which returns text, not json[b]: ->> (Parentheses might required, -- i.e. (jsonb_col ->> 'key') LIKE 'pattern' -- that depends on the PostgreSQL version you use). – Downwash

Pozs, great solution! Thank you so much! :) I'll accept your answer of course if you want to add it below. This is what I used: Product.where("(jsonb_data ->> 'author') LIKE '%billy%'") Appreciate the great help. Thank you!! – Lorenzoloresz

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

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

Chaim

also, it's the same as Product.where("jsonb_data ->> 'author' LIKE '%billy%'") to allow you to combine more in your query – Livonia