I was searching a translatable JSON column on MySql using ->where('name->first', 'like', '%jeRRy%'). But this returned 0 results. In database, there is data and it's like this:
{ "first":"Jerry", "last":"Kim" }
My table column name has insensitive collation!
My solution was to lower case my searching term and extract json attribute "first" like this:
->whereRaw('LOWER(first->"$.") like ?', mb_strtolower("%jeRRy%"))this was suggested in MySql Docs
I was searching a translatable JSON column on MySql using
->where('name->first', 'like', '%jeRRy%'). But this returned 0 results. In database, there is data and it's like this:{ "first":"Jerry", "last":"Kim" }My table column name has insensitive collation!My solution was to lower case my searching term and extract json attribute "first" like this:
->whereRaw('LOWER(first->"$.") like ?', mb_strtolower("%jeRRy%"))this was suggested in MySql DocsThis worked fine in my problem. but, I think it's a bit incorrect way considering chars like 'å' I think this solution was way better to use collation
This would be good to have the option to search JSON columns with original column collation!!!