执行查询时发生异常: SQLSTATE[42883]：未定义函数:7错误:操作符不存在: json

Question

我在使用PostgreSQL和Symfony。

我有一个用户表，如下所示：

id | email         | roles
---------------------------------------
1  | abc@gmail.com | ["ROLE_IVM_USER"]

角色列是JSON类型，如上面所示。

我希望获得具有ROLE_IVM_USER角色的用户列表，使用下面的代码：

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

这将导致以下错误：

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."

我是ROLE_IVM_USER的新手，让我知道如何让用户扮演的角色

Answer 1

下面是我如何在不使用任何第三方库的情况下实现所需的结果：

$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();

希望这能帮到别人，节省调查时间。

Answer 2

您可以执行LIKE而不是IN：

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