如何在 PostgreSQL 中正确查询 JSON 类型字段中的特定角色

老晨君_6901

老晨君_6901

2026-01-14

874人浏览

原创

如何在 PostgreSQL 中正确查询 JSON 类型字段中的特定角色

本文详解 symfony 项目中使用 postgresql 查询 json 类型 `roles` 字段时出现 `operator does not exist: json = unknown` 错误的原因与解决方案,重点介绍 `??` 操作符、类型转换及 dql 替代方案。

在 PostgreSQL 中,json 或 jsonb 类型字段不支持直接使用 IN、= 等标准比较操作符进行数组元素匹配——这正是你遇到 SQLSTATE[42883] 错误的根本原因。你的原始 DQL:

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

会被 Doctrine 编译为类似 WHERE roles IN ('["ROLE_IVM_USER"]') 的 SQL,但 PostgreSQL 无法将 json 值与字符串数组直接用 IN 比较,且 roles 是 JSON 数组(如 ["ROLE_IVM_USER"]),需按 JSON 语义判断是否包含某元素。

✅ 正确做法是利用 PostgreSQL 原生 JSONB 操作符。推荐使用 ??(JSONB 包含键/字符串操作符),它专用于判断 jsonb 对象或数组是否包含指定字符串(注意:仅对 jsonb 有效,json 需先显式转换):

-- ✅ 正确:将 json 转为 jsonb 后使用 ?? 判断是否包含字符串
WHERE u.roles::jsonb ?? 'ROLE_IVM_USER'
⚠️ 注意:?? 要求右侧为 字符串字面量(非数组),且左侧必须是 jsonb 类型。json 类型需强制转换(::jsonb),否则报错。

因此,你提供的原生 SQL 方案是可行的,但可进一步优化为更安全、可复用的形式:

Mailtrap Node.js SDK 用于事务性和沙盒电子邮件
Mailtrap Node.js SDK 用于事务性和沙盒电子邮件

一个基于官方 Mailtrap Node.js SDK 构建的 ASE 技能,用于发送事务邮件并与 Mailtrap 沙盒或生产流程协作。它非常适合需要 API 驱动邮件投递、测试邮箱验证和结构化消息自动化的代理工作流。

下载
// 推荐:使用 createNativeQuery + ResultSetMappingBuilder(确保实体映射)
$rsm = new ResultSetMapping();
$rsm->addEntityResult(User::class, 'u');
$rsm->addFieldResult('u', 'id', 'id');
$rsm->addFieldResult('u', 'email', 'email');
$rsm->addFieldResult('u', 'roles', 'roles');
// ... 添加其他字段

$rawSql = 'SELECT u.* FROM public.user u WHERE u.roles::jsonb ?? :role';
$query = $this->getEntityManager()->createNativeQuery($rawSql, $rsm);
$query->setParameter('role', User::ROLE_IVM_USER);

return $query->getResult();

? 替代方案(纯 DQL,无需原生 SQL):
若希望保持 Doctrine 抽象层,可借助 JSON_CONTAINS 函数(需 PostgreSQL ≥ 12 + pg_trgm 扩展)或更通用的 jsonb_exists_any(PostgreSQL ≥ 9.4):

// 使用 Doctrine 扩展函数(需配置 doctrine/dbal 扩展或自定义 DQL 函数)
// 或直接在 DQL 中调用 PostgreSQL 函数(需启用函数白名单)
$queryBuilder
    ->where("JSONB_EXISTS_ANY({$rootAlias}.roles, :role)")
    ->setParameter('role', [User::ROLE_IVM_USER]);

但最轻量、兼容性最好的方式仍是显式类型转换 + ?? 操作符。

? 额外提示:

  • 建议将 roles 字段类型设为 jsonb(而非 json),性能更优且原生支持索引;
  • 可为此字段创建 GIN 索引加速查询:CREATE INDEX idx_user_roles ON public.user USING GIN (roles);
  • 若需匹配多个角色(如同时含 ROLE_IVM_USER 和 ROLE_ADMIN),改用 @> 操作符:u.roles::jsonb @> '["ROLE_IVM_USER", "ROLE_ADMIN"]'::jsonb。

通过理解 PostgreSQL JSONB 语义与操作符,即可优雅解决此类“undefined operator”问题,无需引入第三方库。

相关专题

更多
PHP Symfony框架
PHP Symfony框架

本专题专注于PHP主流框架Symfony的学习与应用,系统讲解路由与控制器、依赖注入、ORM数据操作、模板引擎、表单与验证、安全认证及API开发等核心内容。通过企业管理系统、内容管理平台与电商后台等实战案例,帮助学员全面掌握Symfony在企业级应用开发中的实践技能。

2025.09.11

6577

17

数据分析工具有哪些
数据分析工具有哪些

数据分析工具有Excel、SQL、Python、R、Tableau、Power BI、SAS、SPSS和MATLAB等。详细介绍:1、Excel,具有强大的计算和数据处理功能;2、SQL,可以进行数据查询、过滤、排序、聚合等操作;3、Python,拥有丰富的数据分析库;4、R,拥有丰富的统计分析库和图形库;5、Tableau,提供了直观易用的用户界面等等。

2023.10.12

5083

8

SQL中distinct的用法
SQL中distinct的用法

SQL中distinct的语法是“SELECT DISTINCT column1, column2,...,FROM table_name;”。本专题为大家提供相关的文章、下载、课程内容,供大家免费下载体验。

2023.10.27

1031

4

SQL中months_between使用方法
SQL中months_between使用方法

在SQL中,MONTHS_BETWEEN 是一个常见的函数,用于计算两个日期之间的月份差。想了解更多SQL的相关内容,可以阅读本专题下面的文章。

2024.02.23

1329

5

SQL出现5120错误解决方法
SQL出现5120错误解决方法

SQL Server错误5120是由于没有足够的权限来访问或操作指定的数据库或文件引起的。想了解更多sql错误的相关内容,可以阅读本专题下面的文章。

2024.03.06

7161

10

sql procedure语法错误解决方法
sql procedure语法错误解决方法

sql procedure语法错误解决办法:1、仔细检查错误消息;2、检查语法规则;3、检查括号和引号;4、检查变量和参数;5、检查关键字和函数;6、逐步调试;7、参考文档和示例。想了解更多语法错误的相关内容,可以阅读本专题下面的文章。

2024.03.06

3783

4

oracle数据库运行sql方法
oracle数据库运行sql方法

运行sql步骤包括:打开sql plus工具并连接到数据库。在提示符下输入sql语句。按enter键运行该语句。查看结果,错误消息或退出sql plus。想了解更多oracle数据库的相关内容,可以阅读本专题下面的文章。

2024.04.07

7140

11

sql中where的含义
sql中where的含义

sql中where子句用于从表中过滤数据,它基于指定条件选择特定的行。想了解更多where的相关内容,可以阅读本专题下面的文章。

2024.04.29

10021

6

sql中删除表的语句是什么
sql中删除表的语句是什么

sql中用于删除表的语句是drop table。语法为drop table table_name;该语句将永久删除指定表的表和数据。想了解更多sql的相关内容,可以阅读本专题下面的文章。

2024.04.29

1370

5

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程
WEB前端教程【HTML5+CSS3+JS】
WEB前端教程【HTML5+CSS3+JS】

共101课时 | 21.4万人学习

JS进阶与BootStrap学习
JS进阶与BootStrap学习

共39课时 | 4.9万人学习