Find string in json column-CodePudding

I'm trying to check if find_this_message message exists inside a json column errors using doctrine/orm.

Here is an example of the json field:

[{"message_key": "another_message", "message_params": []}, {"message": "find_this_message", "message_params": []}]

I used the below code but it always returns nothing

$qb = $this->createQueryBuilder('er');

    $qb
        ->andWhere('JSON_CONTAINS(er.errors, :error) = 1')
        ->setParameter('error', '"'find_this_message'"');
    ;

 return $qb->getQuery()->getSingleResult();

Thanks in advance for your help :)

CodePudding user response：

According Doctrine ORM documentation you should to use something like:

$qb->select('*')
   ->from('er', 'er')
   ->where('JSON_CONTAINS(er.errors, :error) = 1')
   ->setParameter('error', '{"message": "find_this_message"}');

MySQL JSON_CONTAINS online