PostgreSQL JSONB 操作符详解

概述PostgreSQL 提供了专门的 JSONB 类型和操作符用于高效存储和查询 JSON 数据。JSONB 以二进制格式存储支持索引和快速字段访问。核心操作符1.-操作符作用提取 JSON 字段返回JSON 类型语法jsonb_column-key示例-- payload: {name: 张三, age: 25}SELECTpayload-nameFROMusers;-- 结果: 张三 (JSON 字符串带引号)SELECTpayload-ageFROMusers;-- 结果: 25 (JSON 数字)特点返回 JSON 类型保留引号、数据类型适合继续链式操作2.-操作符作用提取 JSON 字段返回TEXT 类型语法jsonb_column-key示例-- payload: {name: 张三, age: 25}SELECTpayload-nameFROMusers;-- 结果: 张三 (纯文本无引号)SELECTpayload-ageFROMusers;-- 结果: 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-fiscalYearasfiscal_year,payload-fiscalQuarterasfiscal_quarter,payload-incentiveIdasincentive_idFROMpartner_level_lock_push_logWHEREpush_statusIN(0,-1);场景 2条件过滤-- 根据 JSON 字段过滤SELECT*FROMpartner_level_lock_push_logWHEREpayload-statuslocked;-- 数值比较SELECT*FROMpartner_level_lock_push_logWHERE(payload-age)::int25;场景 3批量提取并映射到对象!-- MyBatis XML --selectidselectPendingLogsresultTypejava.lang.StringSELECT payload FROM ${tableName} WHERE push_status IN (0, -1) ORDER BY id ASC LIMIT #{limit}/select// Java 代码ListStringpayloadListmapper.selectPendingLogs(tableName,1000);ListAnaplanPartnerLevelLockDodataListnewArrayList();for(Stringpayload:payloadList){AnaplanPartnerLevelLockDolockDoJSON.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-statusactive;-- 正确返回 TEXT 类型SELECT*FROMtableWHEREpayload-statusactive;错误 2嵌套路径格式错误-- 错误缺少花括号SELECTpayload#user,address,city FROM table;-- 正确需要花括号SELECTpayload#{user,address,city} FROM table;错误 3类型转换问题-- 错误文本比较SELECT*FROMtableWHEREpayload-age25;-- 正确显式转换SELECT*FROMtableWHERE(payload-age)::int25;最佳实践1. 简单场景直接查 payload-- 最简单最高效SELECTpayloadFROMtableWHEREpush_status0;Java 端反序列化一次即可。2. 部分字段提取只取需要的字段SELECTpayload-fiscalYearasfiscal_year,payload-incentiveIdasincentive_idFROMtableWHEREpush_status0;适用于只需要少数字段的场景。3. 复杂查询结合索引-- 创建索引CREATEINDEXidx_statusONtable((payload-status));-- 利用索引查询SELECTpayloadFROMtableWHEREpayload-statuslocked;总结-/-: 单层提取#/#: 多层嵌套提取箭头单数 (-,#): 返回 JSON 类型箭头双数 (-,#): 返回 TEXT 类型最佳实践: 直接查询payload字段在应用层反序列化