Request on key/value in a JSON type field with Doctrine2

You can use ScientaNL/DoctrineJsonFunctions

Install it through composer by adding:

"scienta/doctrine-json-functions": "~4.0",

Register the json function that is needed in the doctrine configuration, in this case JSON_CONTAINS:

doctrine:    orm:        entity_managers:            some_em: # usually also "default"                dql:                    string_functions:                        JSON_CONTAINS: Scienta\DoctrineJsonFunctions\Query\AST\Functions\Mysql\JsonContains

In my case, I just added:

doctrine:    orm:         dql:              string_functions:                  JSON_CONTAINS: Scienta\DoctrineJsonFunctions\Query\AST\Functions\Mysql\JsonContains

Use it:

$queryBuilder = $this->getDoctrine()->getRepository('AppBundle:Letter')->createQueryBuilder('lt');$queryBuilder        ->where("JSON_CONTAINS(lt.metadatas, :mvalue, '$.key') = 1");$queryBuilder->setParameter('mvalue', '"value"');$query = $queryBuilder->getQuery();return $query->getResult();

In dql, it should be something like:

$dql = "SELECT l FROM AppBundle:Letter l              WHERE JSON_CONTAINS(lt.metadatas, :mvalue, '$.key') = 1              ";

Note $.key is the json key to filter and mvalue should be included in its json encoded format, in this case with double quotes.

References:

MySql json-search-functions