An exception occurred while executing a query: SQLSTATE[42883]: Undefined function: 7 ERROR: operato-CodePudding

I'm using PostgreSQL with Symfony.

I have a user table which is as below:

id | email         | roles
---------------------------------------
1  | [email protected] | ["ROLE_IVM_USER"]

The roles column is JSON type as shown above.

I want to get user list which has role of ROLE_IVM_USER using code below:

$queryBuilder
            ->andWhere(sprintf('%s.roles IN (:roles)', $rootAlias))
            ->setParameter('roles', [User::ROLE_IVM_USER]);

which results in the below error:

An exception occurred while executing a query: SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: json = unknown\nLINE 1: ... u0_ WHERE u0_.organization_id = $1 AND u0_.roles IN ($2) OR...\n ^\nHINT: No operator matches the given name and argument types. You might need to add explicit type casts."

I'm new to PostgreSQL let me know how to get users with the role of ROLE_IVM_USER

CodePudding user response：

You can do a LIKE instead of an IN :

$queryBuilder
  ->andWhere(sprintf('%s.roles LIKE :roles', $rootAlias))
  ->setParameter('roles', '%' . User::ROLE_IVM_USER . '%');

CodePudding user response：

Here is how I achived the desired result without using any third party lib:

$rsm = $this->createResultSetMappingBuilder('u');

        $rawQuery = sprintf(
            'SELECT %s
                    FROM public.user u
                    WHERE u.roles::jsonb ?? :role',
            $rsm->generateSelectClause()
        );

        $query = $this->getEntityManager()->createNativeQuery($rawQuery, $rsm);
        $query->setParameter('role', $role);
        return $query->getResult();

Hope this will help someone and will save hours of investigation