How to Query Mysql text column

user_2724228 0
Viewed 229

I have mysql 'text' column with data as json which looks like below, how to query using specific value, where Banking is model name , info is field name

Banking 
  info : text

info: {"age": 23, name: "John"}
ruby-on-rails-5 ruby-on-rails-4 activerecord ruby mysql
2 Answers

In my opinion you can try search by LIKE query as below but because your database save data by text so it is very difficult to handle exception case. So I think you should try use json type of mysql or split it into table and save age as integer.

select * from bankings where info LIKE '%"age": 23%'

Ruby

Banking.where('infor LIKE ?', '%"age": 23%')
user_47515860

You should use ActiveRecord to perform this query using ->> operator:

Banking.where("info ->> 'name' = 'John'")
user_36250580
Related