Doctrine + Postgres - how to build a QueryBuilder with JSON search?

Question

PostgreSQL

There is a working SQL query:

SELECT * FROM "user" WHERE "roles"::text = '["ROLE_COURIER"]';

It is necessary to compose it on QueryBuilder.

If you write like this:

$role = '["ROLE_COURIER"]';

        return $this->createQueryBuilder('user')
            ->where('user.roles = :role')
            ->setParameter('role', $role)
            ->getQuery()
            ->getResult()
            ;

Gives an error message:

SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: json = unknown

If you write like this:

$role = '["ROLE_COURIER"]';

        return $this->createQueryBuilder('user')
            ->where('"roles"::text = :role')
            ->setParameter('role', $role)
            ->getQuery()
            ->getResult()
            ;

Gives an error message:

[Syntax Error] line 0, col 44: Error: Expected Literal, got '"'

I can’t figure it out - so how to correctly make a request with a condition on the json field?

Answer 1

for example