首页
友链
留言板
关于

MongoDB关联(聚合)查询,关联集合筛选

场景:商品列表中包含订单统计信息,商品和订单是一对多的关系
注: 这里把商品和订单分成两个集合,仅做演示!具体场景,具体分析

image

插入数据

创建goods和orders集合

goods

db.goods.insert([ {name: 'iphone XS', price: 8999}, {name: '小米 9', price: 3299}, {name: '华为 p30 Pro', price: 4988} ])

image

orders

/** * goodsId为刚才goods生成的_id * idDelete: 0 未删除 1 已删除 */ db.orders.insert([ {sum: 1, goodsId: ObjectId('5d522fd20ec3b11bf17a91e2'), isDelete: 0}, {sum: 2, goodsId: ObjectId('5d522fd20ec3b11bf17a91e2'), isDelete: 0}, {sum: 1, goodsId: ObjectId('5d522fd20ec3b11bf17a91e2'), isDelete: 1}, {sum: 1, goodsId: ObjectId('5d5230870ec3b11bf17a91e4'), isDelete: 0}, {sum: 1, goodsId: ObjectId('5d5230870ec3b11bf17a91e5'), isDelete: 0}, {sum: 1, goodsId: ObjectId('5d5230870ec3b11bf17a91e5'), isDelete: 1}, ]) // 在mongoose的schame中定义为: goodsId:{ type: mongoose.Schema.ObjectId, ref: 'Article' }

image

查询

1、查询某一个商品的所有订单

// 查询语句 db.orders.find({goodsId: ObjectId("5d522fd20ec3b11bf17a91e2")}) // 结果 // 1 { "_id": ObjectId("5d5376580ec3b11bf17a91ec"), "sum": 1, "goodsId": ObjectId("5d522fd20ec3b11bf17a91e2"), "isDelete": 0 } // 2 { "_id": ObjectId("5d5376580ec3b11bf17a91ed"), "sum": 2, "goodsId": ObjectId("5d522fd20ec3b11bf17a91e2"), "isDelete": 0 } // 3 { "_id": ObjectId("5d5376580ec3b11bf17a91ee"), "sum": 1, "goodsId": ObjectId("5d522fd20ec3b11bf17a91e2"), "isDelete": 1 }
// 过滤已删除的订单 db.orders.find({goodsId: ObjectId("5d522fd20ec3b11bf17a91e2"), isDelete: 0}) // 结果 // 1 { "_id": ObjectId("5d5376580ec3b11bf17a91ec"), "sum": 1, "goodsId": ObjectId("5d522fd20ec3b11bf17a91e2"), "isDelete": 0 } // 2 { "_id": ObjectId("5d5376580ec3b11bf17a91ed"), "sum": 2, "goodsId": ObjectId("5d522fd20ec3b11bf17a91e2"), "isDelete": 0 }

2、查询所有商品对应的所有订单

这里使用的的士MongoDB中的aggregate

查询所有商品以及对应的订单

// 关联查询 db.goods.aggregate([ { $lookup: { from: "orders", // 要关联查询的集合 localField: "_id", // goods集合中的_id foreignField: "goodsId", // 要查询的集合的 关联id as: "child" } } ]) // 结果 // 1 { "_id": ObjectId("5d522fd20ec3b11bf17a91e2"), "name": "iphone XS", "price": 8999, "child": [ { "_id": ObjectId("5d5376580ec3b11bf17a91ec"), "sum": 1, "goodsId": ObjectId("5d522fd20ec3b11bf17a91e2"), "isDelete": 0 }, { "_id": ObjectId("5d5376580ec3b11bf17a91ed"), "sum": 2, "goodsId": ObjectId("5d522fd20ec3b11bf17a91e2"), "isDelete": 0 }, { "_id": ObjectId("5d5376580ec3b11bf17a91ee"), "sum": 1, "goodsId": ObjectId("5d522fd20ec3b11bf17a91e2"), "isDelete": 1 } ] } // 2 { "_id": ObjectId("5d5230870ec3b11bf17a91e4"), "name": "小米 9", "price": 3299, "child": [ { "_id": ObjectId("5d5376580ec3b11bf17a91ef"), "sum": 1, "goodsId": ObjectId("5d5230870ec3b11bf17a91e4"), "isDelete": 0 } ] } // 3 { "_id": ObjectId("5d5230870ec3b11bf17a91e5"), "name": "华为 p30 Pro", "price": 4988, "child": [ { "_id": ObjectId("5d5376580ec3b11bf17a91f0"), "sum": 1, "goodsId": ObjectId("5d5230870ec3b11bf17a91e5"), "isDelete": 0 }, { "_id": ObjectId("5d5376580ec3b11bf17a91f1"), "sum": 1, "goodsId": ObjectId("5d5230870ec3b11bf17a91e5"), "isDelete": 1 } ] }

查询所有商品以及对应的订单数

// 关联查询 db.goods.aggregate([ { $lookup: { from: "orders", localField: "_id", foreignField: "goodsId", as: "child" } }, { $project: { name:1, // 筛选要返回的数据,值为:0不返回,1返回 price:1, orderNm: { "$size": "$child" } } } ]) //结果 // 1 { "_id": ObjectId("5d522fd20ec3b11bf17a91e2"), "name": "iphone XS", "price": 8999, "orderNm": NumberInt("3") } // 2 { "_id": ObjectId("5d5230870ec3b11bf17a91e4"), "name": "小米 9", "price": 3299, "orderNm": NumberInt("1") } // 3 { "_id": ObjectId("5d5230870ec3b11bf17a91e5"), "name": "华为 p30 Pro", "price": 4988, "orderNm": NumberInt("2") }

查询单价大于5000商品以及对应的订单

// 关联查询,加入删选条件 db.goods.aggregate([ { $match: { price: { $gt : 5000 } } }, { $lookup: { from: "orders", localField: "_id", foreignField: "goodsId", as: "child" } } ])

查询所有商品以及未删除的订单,是对关联集合的筛选

因为是对关联集合的筛选,所以不能简单通过match去处理

db.goods.aggregate([ { $lookup: { from: "orders", let: { isDelete: "$isDelete", // 定义要筛选的条件 id: "$_id", // 当前集合的_id }, pipeline: [{ "$match": { isDelete: 0, // 筛选条件,未删除的订单 $expr: { $eq: ["$$id","$goodsId"] // 商品对应的订单 } }, }], as: "child", } } ]) // 结果 // 1 { "_id": ObjectId("5d522fd20ec3b11bf17a91e2"), "name": "iphone XS", "price": 8999, "child": [ { "_id": ObjectId("5d5376580ec3b11bf17a91ec"), "sum": 1, "goodsId": ObjectId("5d522fd20ec3b11bf17a91e2"), "isDelete": 0 }, { "_id": ObjectId("5d5376580ec3b11bf17a91ed"), "sum": 2, "goodsId": ObjectId("5d522fd20ec3b11bf17a91e2"), "isDelete": 0 } ] } // 2 { "_id": ObjectId("5d5230870ec3b11bf17a91e4"), "name": "小米 9", "price": 3299, "child": [ { "_id": ObjectId("5d5376580ec3b11bf17a91ef"), "sum": 1, "goodsId": ObjectId("5d5230870ec3b11bf17a91e4"), "isDelete": 0 } ] } // 3 { "_id": ObjectId("5d5230870ec3b11bf17a91e5"), "name": "华为 p30 Pro", "price": 4988, "child": [ { "_id": ObjectId("5d5376580ec3b11bf17a91f0"), "sum": 1, "goodsId": ObjectId("5d5230870ec3b11bf17a91e5"), "isDelete": 0 } ] }

评论区

正在加载中。。。