MYSQL JSON field WHERE begins or ends with

Question

'{"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?

Answer 1

Check the capitals on the like function string. It may be a simple solution.

Answer 2

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.