Add the option to query a json field and get the lower of it

[melicerte]

Hi !

I was trying to compare the value of a json column field with some value given by the user, but I couldn't make it right.

Given I have a Model MyModel which table contains a json field named settings
And the following models exist in database :
| id | settings |
| 1 | {setting1: "AwEsoMe"} |
| 2 | {setting1: "WoNdErFuL"} |
When I execute the following code:

MyModel::where('settings->setting1', 'LIKE', 'awesome');

Then Laravel converts the query to :

SELECT * FROM my_model WHERE (json_unquote(json_extract('settings', "$.setting1")) LIKE '%awesome%') AND deleted_at IS NULL

And the query returns no result
But I'd love to be able to lowercase the json part in order to make a case insensitive comparison :

SELECT * FROM my_model WHERE (lower(json_unquote(json_extract('settings', "$.setting1"))) LIKE '%awesome%') AND deleted_at IS NULL

So the query returns MyModel of id 1

I'm running MySQL 8.
Maybe it would be great if we could use the 'ILIKE' keyword, not supported by MySQL ?

What do you think about such a feature ?

[BenceBakos]

Workaround can be found here:

https://gist.github.com/Sarav-S/8cf262b78f934255172fcc8d779168ca#file-ordercontroller-php

Keep in mind that you have to use mb_strtolower for some characters to work properly, and these solutions didn't work for me in sqlite (testing).