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 https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html — lacostenycoder, Apr 06 '20 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, Apr 06 '20 at 16:28
may try cast? https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html also maybe something helpful here https://stackoverflow.com/questions/39742801/chaining-json-extract-with-cast-or-str-to-date-fails — lacostenycoder, Apr 06 '20 at 16:31

score 1 · Accepted Answer · answered May 26 '20 at 09:40

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

score 0 · Answer 2 · answered Apr 13 '20 at 11:17

0

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

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

answered Apr 13 '20 at 11:17

matanco

2,096
1
13
20

It did not worked , given syntax error, tried the same which you had suggested @matanco – Manoj Menon Apr 13 '20 at 14:27
Incorrect syntax near '>'.: EXEC sp_executesql N'SELECT [bankings].* FROM [bankings] WHERE (info ->> ''account_type'' = ''card'')' – Manoj Menon Apr 13 '20 at 14:33
1

could you please share reference about this operator '->> ' in Activerecord guides or any reference – Manoj Menon Apr 13 '20 at 14:39

How to Query Mysql text column

2 Answers2