Search query in jsonb column

Question

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.

max · Accepted Answer · 2021-09-08 08:13:45Z

3

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

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

edited Sep 8, 2021 at 8:13

answered Sep 8, 2021 at 7:40

max

103k15 gold badges113 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

kia Over a year ago

but I got this error message

ActiveRecord::PreparedStatementInvalid:        wrong number of bind variables (1 for #<Arel::Nodes::Count:0x00005578ffa1a3b0>) in: title->>en ILIKE ?

max Over a year ago

@kia Ah, I shouldn't have wrapped the string in Arel.sql. Edited.

kia Over a year ago

now I got this error

ActiveRecord::StatementInvalid:        PG::UndefinedColumn: ERROR:  column "en" does not exist        LINE 1: SELECT "stages".* FROM "stages" WHERE (title->>en ILIKE '%A1...

bacause I access title in backe-end like this -> title[:en]

max Over a year ago

Ah sorry. The key name needs to be quoted title->>'en' ILIKE ?. sqlfiddle.com/#!17/58944b/5

kia Over a year ago

also, we should write SQL query with sanitize_sql for passing from brakeman

Collectives™ on Stack Overflow

Search query in jsonb column

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related