An exception occurred while executing a query: SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: json

Question

I'm using PostgreSQL with Symfony.

I have a user table which is as below:

id | email         | roles
---------------------------------------
1  | abc@gmail.com | ["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

score 0 · Answer 1 · answered Feb 11 '22 at 18:21

0

You can do a LIKE instead of an IN :

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

answered Feb 11 '22 at 18:21

jean-max

1,640
1
18
33

tried and resulted in `An exception occurred while executing a query: SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: json ~~ unknown` – dev_mustafa Feb 11 '22 at 18:24
And with this : `"$rootAlias.roles LIKE :roles"` instead of using sprintf ? And it's all the query or there is other thing in your QB that could cause this problem ? – jean-max Feb 11 '22 at 18:26
didn't work either. no there is nothing else.. the issue is due to PostgreSQL as it has different syntax for the JSON column – dev_mustafa Feb 11 '22 at 18:30
Did you see this : https://stackoverflow.com/questions/47388433/doctrine-search-in-json-array – jean-max Feb 11 '22 at 18:32

score 0 · Accepted Answer · answered Feb 14 '22 at 06:57

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

An exception occurred while executing a query: SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: json

2 Answers2