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 类型

对比总结

实际应用示例

场景 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

性能优化建议

✅ 推荐做法

1. 直接查询 payload 字段

   SELECTpayloadFROMtableWHERE...

   然后在 Java 中一次性反序列化。

2. 使用 GIN 索引加速查询

   CREATEINDEXidx_payload_ginONtableUSINGGIN(payload);

3. 针对常用字段创建表达式索引

   CREATEINDEXidx_fiscal_yearONtable((payload->>'fiscalYear'));

❌ 避免的做法

1. 不要在 SQL 中逐个字段提取

   -- 低效：数据库要做多次 JSON 解析SELECTpayload->>'field1',payload->>'field2',payload->>'field3',...FROMtable;

2. 不要混合使用 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字段，在应用层反序列化