Query for JSON list in MySQL with Doctrine

Working with JSON is not as easy in MySQL as in other databases. But even in MySQL there are ways to add more complex queries through Doctrine.

In this example we have an entity Change with a details field of type JSON in the database in MySQL and array in PHP. This entity is there to store the updated configuration settings in the application. As there are different types of changes, the details will also be different.

/**
 * @var array
 *
 * @ORM\Column(type="json", name="details")
 */
public $details;

As we don't know what kind of details will be stored, we won't be writing different findBy* methods in the repository. Instead we will send an array of values which will be searched with the help of JSON_EXTRACT. To have this available in Doctrine, we will install scienta/doctrine-json-functions and enable it in your doctrine.yaml

doctrine:
  ...
  orm:
    ...
    dql:
      string_functions:
        JSON_EXTRACT: Scienta\DoctrineJsonFunctions\Query\AST\Functions\Mysql\JsonExtract

After this our repository method will look like this:

public function findOneByTypeAndDetails(
    string $type,
    array $details
): ?Change {
    $baseQuery = "
        SELECT c
        FROM App\EventSourcing\Ledger\State\Change c
        WHERE c.type = :type
    ";

    foreach ($details as $key => $value) {
        $baseQuery .= sprintf(" AND JSON_EXTRACT(c.details, '$.%s') = :%s", $key, $key);
    }

    $query = $this->getEntityManager()->createQuery($baseQuery)
        ->setParameter('type', $type)
    ;

    foreach ($details as $key => $value) {
        $query->setParameter($key, $value);
    }

    return $query->getResult();
}

Using the method might look like this:

$applicationChange = $this->changeRepository->findOneByTypeAndDetails(
	CHANGE::TYPE_APPLICATION_ENVIRONMENT,
    [
    	'startingAt' => $startingAt->format(\DateTime::ATOM),
        'performedBy' => $user->getId()->toString(),
    ]
)

Make sure that you only store "plain data" in the details and no objects.

An example of the content:

[
  {
    "key":"startingAt",
    "value":"2020-08-13T21:39:15+02:00"
  }, 
  {
    "key":"performedBy",
    "value":"19aa4b64-f983-4183-98aa-249990bc2b18"
  }
]