Find JSON element in MySQL

MySQL isn't really build for searching through JSON data. PostgreSQL seems to be way better in this regard. Nether the less it is possible to search since MySQL 5.7.

To do so use the MySQL function JSON_CONTAINS. In our example we have a table called client_data which contains a field called details. Details is a JSON array filled with key and value attributes.

Our query looks like this if we want to search for the key name and value Peter:

$sql = 'SELECT *
FROM client_data
WHERE JSON_CONTAINS(details, \'{"key": "name", "value": "Peter"}\') = 1';

Using a custom type, you can also use JSON_CONTAINS in Doctrine.

doctrine:
  orm:
    dql:
      string_functions:
        json_contains: Syslogic\DoctrineJsonFunctions\Query\AST\Functions\Mysql\JsonContains