'{"city": "Gradystad"}'
This Works
`fields`->'$."city"' LIKE '%stad%' // contains
This Does Not
`fields`->'$."city"' LIKE 'Grady%' // begins with
`fields`->'$."city"' LIKE '%stad' // ends with
Why? & How to work around?
Laravel uses the ->
JSON selector syntax in the MySQL Grammar, like documented in the MySQL manual
mysql> SELECT c, c->"$.id", g
> FROM jemp
> WHERE c->"$.id" > 1
> ORDER BY c->"$.name";
+-------------------------------+-----------+------+
| c | c->"$.id" | g |
+-------------------------------+-----------+------+
| {"id": "3", "name": "Barney"} | "3" | 3 |
| {"id": "4", "name": "Betty"} | "4" | 4 |
| {"id": "2", "name": "Wilma"} | "2" | 2 |
+-------------------------------+-----------+------+
3 rows in set (0.00 sec)
However, it does not add JSON_UNQUOTE
or any method to remove the double quotes from the result set. Since the where
clauses are evaluated via MySQL (PDO must strip them in the result set conversion) we need to include the extra "
's in our search values.
Check the capitals on the like function string. It may be a simple solution.