MongoDB关联（聚合）查询，关联集合筛选

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

插入数据

创建goods和orders集合

goods

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

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'
        }

查询

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
        }
    ]
}