Filter by JSON values on Doctrine

Question

I have my roles of my users with type json and I would like to get the list of users by roles.

/**
 * @ORM\Column(type="json")
 */
private $roles = [];

$queryBuilder
    ->where("$rootAlias.roles LIKE :role")
    ->setParameter('role', '["ROLE_USER"]')
;

This is the error I get:

An exception occurred while executing a query: SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: json ~~ unknown\nLINE 1: ...ce s1_ ON u0_.service_id = s1_.id WHERE u0_.roles LIKE $1 OR...\n
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

It works great like this "roles"::TEXT LIKE :role but I don't know how to convert "roles"::TEXT in query builder?

Answer 1

To work with json, you can use the DoctrineJsonFunctions extension.

Installation

composer require scienta/doctrine-json-functions

Declare a function in config/packages/doctrine.yaml

orm:
    dql:
        string_functions: 
            JSON_GET_TEXT: Scienta\DoctrineJsonFunctions\Query\AST\Functions\Postgresql\JsonGetText

Now you can write such a request in your UserRepository

   public function getUsersByRole($role){
        return $this->createQueryBuilder('u')
           ->where("JSON_GET_TEXT(u.roles,0) = :role ")
           ->setParameter('role', $role)
           ->getQuery()
           ->getResult();
    }