PostgreSQL JSONB 操作符详解
概述
PostgreSQL 提供了专门的 JSONB 类型和操作符，用于高效存储和查询 JSON 数据。JSONB 以二进制格式存储，支持索引和快速字段访问。
核心操作符
1.->操作符
作用：提取 JSON 字段，返回JSON 类型
语法：
jsonb_column->'key'示例：
-- payload: {"name": "张三", "age": 25}SELECTpayload->'name'FROMusers;-- 结果: "张三" (JSON 字符串，带引号)SELECTpayload->'age'FROMusers;-- 结果: 25 (JSON 数字)特点：
- 返回 JSON 类型（保留引号、数据类型）
- 适合继续链式操作
2.->>操作符
作用：提取 JSON 字段，返回TEXT 类型
语法：
jsonb_column->>'key'示例：
-- payload: {"name": "张三", "age": 25}SELECTpayload->>'name'FROMusers;-- 结果: 张三 (纯文本，无引号)SELECTpayload->>'age'FROMusers;-- 结果: "25" (文本字符串)特点：
- 返回 TEXT 类型（去除引号）
- 适合直接显示或字符串比较
3.#>操作符
作用：提取嵌套路径，返回JSON 类型
语法：
jsonb_column#> '{key1,key2,key3}'示例：
-- payload: {"user": {"address": {"city": "北京"}}}SELECTpayload#>'{user,address,city}' FROM users;-- 结果: "北京" (JSON 字符串)特点：
- 支持多层嵌套访问
- 返回 JSON 类型
4.#>>操作符
作用：提取嵌套路径，返回TEXT 类型
语法：
jsonb_column#>> '{key1,key2,key3}'示例：
-- payload: {"user": {"address": {"city": "北京"}}}SELECTpayload#>>'{user,address,city}' FROM users;-- 结果: 北京 (纯文本)特点：
- 支持多层嵌套访问
- 返回 TEXT 类型
对比总结
| 操作符 | 返回类型 | 用途 | 示例 |
|---|---|---|---|
-> | JSON | 单层提取，保持 JSON 格式 | payload->'name' |
->> | TEXT | 单层提取，转为文本 | payload->>'name' |
#> | JSON | 多层嵌套提取，保持 JSON 格式 | payload#>'{a,b,c}' |
#>> | TEXT | 多层嵌套提取，转为文本 | payload#>>'{a,b,c}' |
实际应用示例
场景 1：直接查询字段
-- 表结构CREATETABLEpartner_level_lock_push_log(id BIGSERIALPRIMARYKEY,payload JSONBNOTNULL,push_statusSMALLINTDEFAULT0);-- 查询特定字段SELECTid,payload->>'fiscalYear'asfiscal_year,payload->>'fiscalQuarter'asfiscal_quarter,payload->>'incentiveId'asincentive_idFROMpartner_level_lock_push_logWHEREpush_statusIN(0,-1);场景 2：条件过滤
-- 根据 JSON 字段过滤SELECT*FROMpartner_level_lock_push_logWHEREpayload->>'status'='locked';-- 数值比较SELECT*FROMpartner_level_lock_push_logWHERE(payload->>'age')::int>25;场景 3：批量提取并映射到对象
<!-- MyBatis XML --><selectid="selectPendingLogs"resultType="java.lang.String">SELECT payload FROM ${tableName} WHERE push_status IN (0, -1) ORDER BY id ASC LIMIT #{limit}</select>// Java 代码List<String>payloadList=mapper.selectPendingLogs(tableName,1000);List<AnaplanPartnerLevelLockDo>dataList=newArrayList<>();for(Stringpayload:payloadList){AnaplanPartnerLevelLockDolockDo=JSON.parseObject(payload,AnaplanPartnerLevelLockDo.class);dataList.add(lockDo);}tip:也可以不用转换，直接从数据库返回json object ，后续使用object性能优化建议
✅ 推荐做法
直接查询 payload 字段
SELECTpayloadFROMtableWHERE...然后在 Java 中一次性反序列化。
使用 GIN 索引加速查询
CREATEINDEXidx_payload_ginONtableUSINGGIN(payload);针对常用字段创建表达式索引
CREATEINDEXidx_fiscal_yearONtable((payload->>'fiscalYear'));
❌ 避免的做法
不要在 SQL 中逐个字段提取
-- 低效：数据库要做多次 JSON 解析SELECTpayload->>'field1',payload->>'field2',payload->>'field3',...FROMtable;不要混合使用 JSON 和关系型字段
- 如果某个字段需要频繁查询/排序，应该单独建列
- JSONB 适合存储结构化但不常查询的数据
常见错误
错误 1：混淆->和->>
-- 错误：返回 JSON 类型，无法直接比较SELECT*FROMtableWHEREpayload->'status'='active';-- 正确：返回 TEXT 类型SELECT*FROMtableWHEREpayload->>'status'='active';错误 2：嵌套路径格式错误
-- 错误：缺少花括号SELECTpayload#>'user,address,city' FROM table;-- 正确：需要花括号SELECTpayload#>'{user,address,city}' FROM table;错误 3：类型转换问题
-- 错误：文本比较SELECT*FROMtableWHEREpayload->>'age'>25;-- 正确：显式转换SELECT*FROMtableWHERE(payload->>'age')::int>25;最佳实践
1. 简单场景：直接查 payload
-- 最简单最高效SELECTpayloadFROMtableWHEREpush_status=0;Java 端反序列化一次即可。
2. 部分字段提取：只取需要的字段
SELECTpayload->>'fiscalYear'asfiscal_year,payload->>'incentiveId'asincentive_idFROMtableWHEREpush_status=0;适用于只需要少数字段的场景。
3. 复杂查询：结合索引
-- 创建索引CREATEINDEXidx_statusONtable((payload->>'status'));-- 利用索引查询SELECTpayloadFROMtableWHEREpayload->>'status'='locked';总结
->/->>: 单层提取#>/#>>: 多层嵌套提取- 箭头单数 (
->,#>): 返回 JSON 类型 - 箭头双数 (
->>,#>>): 返回 TEXT 类型 - 最佳实践: 直接查询
payload字段，在应用层反序列化