1. 基础 CRUD操作PostgreSQL JSONBMongoDB Pipeline新增INSERT INTO public.cfx_mongo (id, cfx_message, create_time) VALUES (5, {MessageName:CFX.TEST}, NOW());db.cfx_mongo.insertOne({ _id: 5, cfx_message: { MessageName: CFX.TEST }, create_time: new Date() })修改UPDATE public.cfx_mongo SET cfx_message {MessageName:CFX.AAAA.DE00000000} WHERE id 5;db.cfx_mongo.updateOne({ _id: 5 }, { $set: { cfx_message.MessageName: CFX.AAAA.DE00000000 } })删除DELETE FROM public.cfx_mongo WHERE id 5;db.cfx_mongo.deleteOne({ _id: 5 })查询全部SELECT id, cfx_message, create_time FROM public.cfx_mongo ORDER BY id;db.cfx_mongo.find({}, { _id: 1, cfx_message: 1, create_time: 1 }).sort({ _id: 1 })2. 条件查询操作PostgreSQL JSONBMongoDB Pipeline等值查询 ()SELECT id, cfx_message, create_time FROM public.cfx_mongo WHERE cfx_message-Source CFX.A00.OT05460002;db.cfx_mongo.find({ cfx_message.Source: CFX.A00.OT05460002 }, { _id: 1, cfx_message: 1, create_time: 1 })不等值查询 (!)SELECT id, cfx_message, create_time FROM public.cfx_mongo WHERE cfx_message-Source ! CFX.A00.OT05460002;db.cfx_mongo.find({ cfx_message.Source: { $ne: CFX.A00.OT05460002 } }, { _id: 1, cfx_message: 1, create_time: 1 })3. 嵌套查询操作PostgreSQL JSONBMongoDB Pipeline单层嵌套SELECT id, cfx_message, create_time FROM public.cfx_mongo WHERE cfx_message - MessageBody - $type CFX.Plus.InformationSystem.UnitValidation.ValidateUpdateUnitsRequest, CFX.Structures.GenericEquipment;db.cfx_mongo.find({ cfx_message.MessageBody.$type: CFX.Plus.InformationSystem.UnitValidation.ValidateUpdateUnitsRequest, CFX.Structures.GenericEquipment }, { _id: 1, cfx_message: 1, create_time: 1 })深度嵌套 (-)SELECT id, cfx_message, create_time FROM public.cfx_mongo WHERE cfx_message - MessageBody - PrimaryResult - Result Passed;db.cfx_mongo.find({ cfx_message.MessageBody.PrimaryResult.Result: Passed }, { _id: 1, cfx_message: 1, create_time: 1 })深度嵌套 (#)SELECT id, cfx_message, create_time FROM public.cfx_mongo WHERE cfx_message # {MessageBody,PrimaryResult,Result} Passed;db.cfx_mongo.find({ cfx_message.MessageBody.PrimaryResult.Result: Passed }, { _id: 1, cfx_message: 1, create_time: 1 })4. 组合条件查询操作PostgreSQL JSONBMongoDB PipelineAND 条件SELECT id, cfx_message, create_time FROM public.cfx_mongo WHERE cfx_message - MessageBody - $type CFX.Plus.InformationSystem.UnitValidation.ValidateUpdateUnitsRequest, CFX.Structures.GenericEquipment AND cfx_message - MessageBody - PrimaryResult - Result Passed;db.cfx_mongo.find({ $and: [ { cfx_message.MessageBody.$type: CFX.Plus.InformationSystem.UnitValidation.ValidateUpdateUnitsRequest, CFX.Structures.GenericEquipment }, { cfx_message.MessageBody.PrimaryResult.Result: Passed } ] }, { _id: 1, cfx_message: 1, create_time: 1 })OR 条件SELECT id, cfx_message, create_time FROM public.cfx_mongo WHERE cfx_message - MessageBody - $type CFX.Plus.InformationSystem.UnitValidation.ValidateUpdateUnitsRequest, CFX.Structures.GenericEquipment OR cfx_message - MessageBody - PrimaryResult - Result Passed;db.cfx_mongo.find({ $or: [ { cfx_message.MessageBody.$type: CFX.Plus.InformationSystem.UnitValidation.ValidateUpdateUnitsRequest, CFX.Structures.GenericEquipment }, { cfx_message.MessageBody.PrimaryResult.Result: Passed } ] }, { _id: 1, cfx_message: 1, create_time: 1 })5. 键存在性查询操作PostgreSQL JSONBMongoDB Pipeline包含指定键 (?)SELECT id, cfx_message, create_time FROM public.cfx_mongo WHERE cfx_message ? factory;db.cfx_mongo.find({ cfx_message.factory: { $exists: true } }, { _id: 1, cfx_message: 1, create_time: 1 })包含任意一个指定键 (?|)SELECT id, cfx_message, create_time FROM public.cfx_mongo WHERE cfx_message ?| ARRAY[factory, MessageBody];db.cfx_mongo.find({ $or: [ { cfx_message.factory: { $exists: true } }, { cfx_message.MessageBody: { $exists: true } } ] }, { _id: 1, cfx_message: 1, create_time: 1 })包含所有指定键 (?)SELECT id, cfx_message, create_time FROM public.cfx_mongo WHERE cfx_message ? ARRAY[factory, MessageBody];db.cfx_mongo.find({ $and: [ { cfx_message.factory: { $exists: true } }, { cfx_message.MessageBody: { $exists: true } } ] }, { _id: 1, cfx_message: 1, create_time: 1 })6. 包含查询 ()操作PostgreSQL JSONBMongoDB Pipeline包含指定键值对SELECT id, cfx_message, create_time FROM public.cfx_mongo WHERE cfx_message {factory: DET7};db.cfx_mongo.find({ cfx_message.factory: DET7 }, { _id: 1, cfx_message: 1, create_time: 1 })同时满足多个条件SELECT id, cfx_message, create_time FROM public.cfx_mongo WHERE cfx_message {factory: DET7, testData: };db.cfx_mongo.find({ cfx_message.factory: DET7, cfx_message.testData: }, { _id: 1, cfx_message: 1, create_time: 1 })7. 路径查询#/#与-/-的区别操作PostgreSQL JSONBMongoDB Pipeline--逐级访问SELECT cfx_message - MessageBody - $type FROM public.cfx_mongo;db.cfx_mongo.find({}, { cfx_message.MessageBody.$type: 1 })#路径访问返回文本SELECT cfx_message # {MessageBody,PrimaryResult,Result} FROM public.cfx_mongo;db.cfx_mongo.find({}, { cfx_message.MessageBody.PrimaryResult.Result: 1 })#路径访问返回 JSONSELECT cfx_message # {MessageBody,PrimaryResult} FROM public.cfx_mongo;db.cfx_mongo.find({}, { cfx_message.MessageBody.PrimaryResult: 1 })路径访问语法对比PostgreSQL说明MongoDBcfx_message - MessageBody - $type逐级访问适合层级少cfx_message.MessageBody.$typecfx_message # {MessageBody,$type}路径访问适合层级深cfx_message.MessageBody.$typecfx_message # ARRAY[MessageBody, $type]数组形式路径cfx_message.MessageBody.$type8. 范围查询操作PostgreSQL JSONBMongoDB Pipeline等于SELECT id, cfx_message, create_time FROM public.cfx_mongo WHERE (cfx_message # {MessageBody,PrimaryResult,PositionNumber})::INT 0;db.cfx_mongo.find({ cfx_message.MessageBody.PrimaryResult.PositionNumber: 0 }, { _id: 1, cfx_message: 1, create_time: 1 })大于SELECT id, cfx_message, create_time FROM public.cfx_mongo WHERE (cfx_message # {MessageBody,PrimaryResult,PositionNumber})::INT 0;db.cfx_mongo.find({ cfx_message.MessageBody.PrimaryResult.PositionNumber: { $gt: 0 } }, { _id: 1, cfx_message: 1, create_time: 1 })BETWEENSELECT id, cfx_message, create_time FROM public.cfx_mongo WHERE (cfx_message # {MessageBody,PrimaryResult,PositionNumber})::INT BETWEEN 0 AND 100;db.cfx_mongo.find({ cfx_message.MessageBody.PrimaryResult.PositionNumber: { $gte: 0, $lte: 100 } }, { _id: 1, cfx_message: 1, create_time: 1 })日期范围SELECT id, cfx_message, create_time FROM public.cfx_mongo WHERE (cfx_message # {TimeStamp}) BETWEEN 2025-08-19T11:53:12.179153608:00 AND 2025-08-19T11:53:12.179153608:00;db.cfx_mongo.find({ cfx_message.TimeStamp: { $gte: ISODate(2025-08-19T11:53:12.179153608:00), $lte: ISODate(2025-08-19T11:53:12.179153608:00) } }, { _id: 1, cfx_message: 1, create_time: 1 })9. 模糊查询操作PostgreSQL JSONBMongoDB PipelineLIKE顶层字段SELECT id, cfx_message, create_time FROM public.cfx_mongo WHERE cfx_message - MessageName LIKE %ValidateUpdateUnitsRequest%;db.cfx_mongo.find({ cfx_message.MessageName: { $regex: ValidateUpdateUnitsRequest } }, { _id: 1, cfx_message: 1, create_time: 1 })LIKE嵌套字段SELECT id, cfx_message, create_time FROM public.cfx_mongo WHERE cfx_message - MessageBody - $type LIKE %ValidateUpdateUnitsRequest%;db.cfx_mongo.find({ cfx_message.MessageBody.$type: { $regex: ValidateUpdateUnitsRequest } }, { _id: 1, cfx_message: 1, create_time: 1 })LIKE深层路径SELECT id, cfx_message, create_time FROM public.cfx_mongo WHERE cfx_message # {MessageBody,$type} LIKE %ValidateUpdateUnitsRequest%;db.cfx_mongo.find({ cfx_message.MessageBody.$type: { $regex: ValidateUpdateUnitsRequest } }, { _id: 1, cfx_message: 1, create_time: 1 })ILIKE忽略大小写SELECT id, cfx_message, create_time FROM public.cfx_mongo WHERE cfx_message # {MessageBody,$type} ILIKE %validateupdateunitsrequest%;db.cfx_mongo.find({ cfx_message.MessageBody.$type: { $regex: validateupdateunitsrequest, $options: i } }, { _id: 1, cfx_message: 1, create_time: 1 })正则匹配SELECT id, cfx_message, create_time FROM public.cfx_mongo WHERE cfx_message # {MessageBody,$type} ~ .*ValidateUpdateUnitsRequest.*;db.cfx_mongo.find({ cfx_message.MessageBody.$type: { $regex: .*ValidateUpdateUnitsRequest.* } }, { _id: 1, cfx_message: 1, create_time: 1 })10. 索引创建目的PostgreSQL JSONBMongoDBGIN 全文索引CREATE INDEX idx_cfx_message_gin ON public.cfx_mongo USING GIN (cfx_message);db.cfx_mongo.createIndex({ cfx_message: text })顶层字段索引CREATE INDEX idx_cfx_message_name ON public.cfx_mongo ((cfx_message - MessageName));db.cfx_mongo.createIndex({ cfx_message.MessageName: 1 })嵌套字段索引CREATE INDEX idx_cfx_message_body_primary_result ON public.cfx_mongo ((cfx_message # {MessageBody,PrimaryResult,Result}));db.cfx_mongo.createIndex({ cfx_message.MessageBody.PrimaryResult.Result: 1 })组合索引CREATE INDEX idx_cfx_message_unique ON public.cfx_mongo ((cfx_message - MessageName), (cfx_message - UniqueID));db.cfx_mongo.createIndex({ cfx_message.MessageName: 1, cfx_message.UniqueID: 1 })11. 聚合查询操作PostgreSQL JSONBMongoDB Pipeline数组查询SELECT id, cfx_message, create_time FROM public.cfx_mongo WHERE cfx_message - tags [urgent];db.cfx_mongo.find({ cfx_message.tags: urgent }, { _id: 1, cfx_message: 1, create_time: 1 })分组统计SELECT cfx_message - MessageName AS message_name, COUNT(*) AS count FROM public.cfx_mongo GROUP BY cfx_message - MessageName;db.cfx_mongo.aggregate([ { $group: { _id: $cfx_message.MessageName, count: { $sum: 1 } } } ])12. 总结对比维度PostgreSQL JSONBMongoDB查询语法SQL JSONB 操作符类 JSON 的查询语法学习曲线需要理解 JSONB 操作符直观易上手索引灵活性高多种索引类型高事务支持完整 ACID多文档事务较新版本嵌套查询使用-、-、#使用点号路径正则查询LIKE、~操作符$regex操作符数组查询包含操作符直接查询数组元素聚合能力标准 SQL GROUP BY聚合管道数据完整性强约束、触发器、外键弱应用层保证更新方式整行更新MVCC字段级更新13. 迁移注意事项查询语法转换MongoDB 点号路径 → PostgreSQL 的-和#操作符索引策略根据实际查询模式选择 GIN 或 B-tree 表达式索引性能验证使用EXPLAIN ANALYZE验证索引是否生效数据完整性利用 PostgreSQL 约束保证数据质量NULL 值处理JSONB 中不存在的字段返回 NULL与 MongoDB 的 undefined 处理逻辑不同事务支持PostgreSQL 提供完整 ACID 事务