MYSQL JSON field WHERE begins or ends with

193 Views Asked by At
'{"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?

2

There are 2 best solutions below

0
On

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

0
On

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.