dimanche 10 avril 2016

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?

Aucun commentaire:

Enregistrer un commentaire