CODEWITHSUNDEEP
ruby-on-railspostgresql
Vishal
Vishal

Reputation: 7361

Special character in LIKE in PostgreSQL

I have notifications table in my database which contains metadata field with data type TEXT. Below is the one record for my notificaiton table

#<Notification id: 1863, target_type: "User", target_id: 8, object_type: nil, object_id: nil, read: true, metadata: {:title=>"test user sent you a message. Read it now!", :description=>"Dear user, You've received a new chat from test user. Tap here to check!", :notification_type=>"chat", :sender_id=>7, :receiver_id=>8, :name=>"Shyam fb", :img_url=>"http://s3-eu-west-2.amazonaws.com/moodit-prod/avatars/images/000/000/001/original/image.jpg"}, created_at: "2019-03-27 13:01:48", updated_at: "2019-03-27 13:05:03">

for one record metadata value is like below

metadata: {
           :title=>"test user sent you a message. Read it now!",
           :description=>"Dear user, You've received a new chat from test user. Tap here to check!", 
           :notification_type=>"chat", 
           :sender_id=>7, 
           :receiver_id=>8, 
           :name=>"Shyam fb", 
           :img_url=>"demo image url"
          }

I can get value 7 if i do

User.find(8).notifications.last.metadata[:sender_id]

I want find records for metadata which contains the string sender_id=>7 .

I tried below queries but didn't get success

User.find(8).notifications.where("metadata LIKE ? ", "%#{sanitize_sql_like(:sender_id=>7)}%" )

User.find(8).notifications.where("metadata LIKE ? ESCAPE'=' ", "%sender_id=>7".gsub('=', '!=').gsub('>', '!>') + '%')

User.find(8).notifications.where("metadata LIKE LOWER(E'sender_id=>7%')")

How can i achieve that with LIKE query?

Upvotes: 1

Views: 96

Answers (1)

Vishal
Vishal

Reputation: 7361

I managed to make a query if anyone has this type of issue in future they can use below query for finding a record from the text data type.

User.find(8).notifications.where("position(':sender_id: ?' in metadata) != 0", 7)

Upvotes: 1

Related Questions