How to select a field with condition through a persisted multi-dimensionnal array (Doctrine / Symfony 5)

Question

I am trying to retrieve a field from my multi-dimensional array that has been persisted in database.

The array looks like this:

$valid = [
            "technical" => [
                "sender" => [
                    "uid" => "first.last",
                    "date" => new \DateTime(),
                    "recipient" => 75306
                ],
                "receiver" => []
            ]
        ];

and I would like to be able to retrieve my entity "Valid" in SQL with Doctrine if the field technical.sender.recipient equals $unity.

return $this->createQueryBuilder('r')
            ->join('r.valid','valids')
            ->where('valids.technical.sender.recipient IN (:unity)')
            ->setParameter('unity', $unity)
            ->getQuery()
            ->getResult()
        ;

Unfortunately, I can't get this value because Symfony warns me that : Class App\Entity\Valid has no field or association named technical.sender.recipient

My persisted array looks like this (longtext field):

a:1:{s:9:"technical";a:2:{s:6:"sender";a:4:{s:3:"uid";s:10:"first.last";s:4:"date";O:8:"DateTime":3:{s:4:"date";s:26:"2023-05-02 09:47:39.229914";s:13:"timezone_type";i:3;s:8:"timezone";s:13:"Europe/Berlin";}s:9:"recipient";i:75306;}s:8:"receiver";a:0:{}}}

I tried to select the field directly with this notation and also using partials but it did not work.

Answer 1

Solved it by converting my array field to json type and using DoctrineJsonFunctions.

Looks like this now and perfectly working :

return $this->createQueryBuilder('r')
        ->leftJoin('r.valid','valids')
        ->andWhere('JSON_EXTRACT(valids.valids, :jsonPath) = :unity')
        ->setParameter('jsonPath', '$.technical.sender.recipient')
        ->setParameter('unity', intval($unity))
        ->getQuery()
        ->getResult()
    ;