Search in Doctrine JSON Type

I'm working on an API project at the moment. The API is build with a simple Symfony / MySQL (through Doctrine) stack. One of the requirements added was a search function. Previously this wasn't an issue because all fields that had to be searched where MySQL columns. But this now changed as there are now also "data" fields which contain a JSON blob. And this JSON now also has be searchable.

Luckily I'm not the first one with this requirement and there is a special extension for Doctrine which allows exactly that, called DoctrineJsonFunctions.

As an example:

Content

[
    [
        "key": "Kunde",
        "value": "Liplex"
    ],
    [
        "key": "Händler",
        "value": "ABC"
    ]
]

DQL

$dql = "
    SELECT t
    FROM AppBundle:Tracking t
    WHERE JSON_SEARCH(t.details, 'one', :search, '\', '$**.value') IS NOT NULL
";

This would return all trackings where :search would be set to Liplex or ABC.

This requires MySQL 5.7 or higher. Therefore you can also look into the documentation of MySQL JSON_SEARCH to see how to use the those functions.