How to Query Mysql text column

Question

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"}

You may need to write SQL inside your activer record request see dev.mysql.com/doc/refman/8.0/en/json-search-functions.html — lacostenycoder
– lacostenycoder, Commented Apr 6, 2020 at 16:25
Hey @lacostenycoder thanks for response but it seems its only applicable for field which has mysql json type, in my case it is text type — Manoj Menon
– Manoj Menon, Commented Apr 6, 2020 at 16:28
may try cast? dev.mysql.com/doc/refman/8.0/en/cast-functions.html also maybe something helpful here stackoverflow.com/questions/39742801/… — lacostenycoder
– lacostenycoder, Commented Apr 6, 2020 at 16:31

Bình Trương · Accepted Answer · 2020-05-26 09:40:23Z

1

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%')

answered May 26, 2020 at 9:40

Bình Trương

4104 silver badges14 bronze badges

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

Comments

matanco · Accepted Answer · 2020-04-13 11:17:38Z

0

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

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

answered Apr 13, 2020 at 11:17

matanco

2,1191 gold badge15 silver badges20 bronze badges

3 Comments

Manoj Menon Over a year ago

It did not worked , given syntax error, tried the same which you had suggested @matanco

Manoj Menon Over a year ago

Incorrect syntax near '>'.: EXEC sp_executesql N'SELECT [bankings].* FROM [bankings] WHERE (info ->> ''account_type'' = ''card'')'

Manoj Menon Over a year ago

could you please share reference about this operator '->> ' in Activerecord guides or any reference

Collectives™ on Stack Overflow

How to Query Mysql text column

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related