Search query in jsonb column-CodePudding

I have a stage table that has a jsonb column named title.

The value of title is a ruby hash value, e.g. {en: "A", de: "A" }

Now I wrote the following scope for searching a stage based on the title in the Rails model

scope :search, ->(query) { where('title @> ?', { "#{I18n.locale}": "%#{query}%" }.to_json).any? }

but it does not work and it's giving me all items in the RSpec test.

CodePudding user response：

You need to use the ->> JSON operator together with ILIKE to do pattern matching:

scope :search, ->(query) {
  where("title->>'#{I18n.locale}' ILIKE ?", "%#{query}%")
}