mongo联表聚合查询

 

 

 

mongo小白一枚，最近公司业务需求，需要将两个表进行关联后，再进行条件过滤，后进行聚合统计

下面以【主集合A】,联表【集合B】进行举例说明当时做的想法

先简单给一下【主集合A】【主集合B】的结构

集合A，结构如下：

{
	"_id": "123456",
	"aName": '测试',
	'status': '1'
}


集合B，结构如下：

{
	"_id": "abcd",
	"aId": "123456",
	"orders": [{
		"_id": "order123",
		"quantity": "123",
		"saleStatus": "1"
	},
	{
		"_id": "order456",
		"quantity": "456",
		"saleStatus": "0"
	},{
		"_id": "order789",
		"quantity": "100",
		"saleStatus": "1"
	}]
}

需求：求集合A状态为1(a.statue = 1)并且售卖状态为1(b.orders.saleStatus=1)的售卖总量(sum(b.orders.quantity))

db.b.aggregate( [

   {
     $lookup: {
        from: "a",
        localField: "aId",
        foreignField: "_id",
        as: "aInfo"
      }
     
    },{
     $unwind: { path: "$aInfo" }
   },{
     $match: {'aInfo.status':1}
   },
   {
     $unwind: { path: "$orders" }
   },
   {
     $match: {"orders.saleStatus":'1'}  
   },{
     $group:
       {
         _id: "$_id",
         count: { $sum: "$orders.quantity" }
       }
   }
] )

相当于SQL

select sum(b.orders.quantity) from b inner join a on (a._id = b.aId) where a.status='1' and b.orders.saleStatus = '1' group by b._id

$unwind:将数组打散，类似于Java的flatMap,将数组转成多个平级对象