Nodejs Mongoose聚合管道aggregate关联查询筛选字段 -筛选关联表字段

Mongoose聚合管道aggregate常用的操作有$project 、$match 、$group、$sort、$limit、$skip、$lookup 表关联 Nodejs Mongoose聚合管道aggregate关联查询筛选关联表指定返回字段，见最下面代码

返回指定字段：

db.order.aggregate([
	{
	   $project:{ order_id:1,trade_no:1, all_price:1 }
	}
])

筛选：

db.order.aggregate([
 { 
	$project:{ order_id:1,trade_no:1, all_price:1 }
 },
 {
	$match:{"all_price":{$gte:90}}
 }
])

group聚合：

db.order_item.aggregate(
    [
         {
              $group: {_id: "$order_id", total: {$sum: "$num"}}
         }
    ]
   )

db.order_item.aggregate(
    [
         {
              $group: {_id: "$order_id", total: {$sum: "$price"}}
         }
    ]
   )

排序 筛选 返回指定字段

db.order.aggregate([
{	
	$project:{ trade_no:1, all_price:1 }
},
{
	$match:{"all_price":{$gte:90}}
},
{
	$sort:{"all_price":-1}
}

])

db.order.aggregate([ { $project:{ trade_no:1, all_price:1 } }, { $match:{“all_price”:{$gte:90}} }, { $sort:{“all_price”:-1} }, { $limit:1 }

])

排序 siip 筛选 返回指定字段

db.order.aggregate([
{	
	$project:{ trade_no:1, all_price:1 }
},
{
	$match:{"all_price":{$gte:90}}
},
{
	$sort:{"all_price":-1}
},
{
	$skip:1
}

])

aggregate关联查询 想返回如下json数据

[
	{
		Order_id:’1’,
		Trade_no:’’,
		Items:[
			{
				Title:’鼠标’，
				Price：20
			}，
			{
				Title:’键盘’，
				Price：20
			}

		]
	}，
	{
		Order_id:’2’,
		Trade_no:’’,
		Items:[
			{
				Title:’鼠标’，
				Price：20
			}，
			{
				Title:’键盘’，
				Price：20
			}

		]
	}
]

Mongoose聚合管道aggregate关联查询筛选字段 实现代码

db.order.aggregate([
    {
      $lookup:
        {
          from: "order_item",
          localField: "order_id",
          foreignField: "order_id",
          as: "items"
        }
   }
])

返回结果：

{
	"_id": ObjectId("5b743d8c2c327f8d1b360540"),
	"order_id": "1",
	"uid": 10,
	"trade_no": "111",
	"all_price": 100,
	"all_num": 2,
	"items": [{
		"_id": ObjectId("5b743d9c2c327f8d1b360543"),
		"order_id": "1",
		"title": "商品鼠标1",
		"price": 50,
		"num": 1
	}, {
		"_id": ObjectId("5b743da12c327f8d1b360544"),
		"order_id": "1",
		"title": "商品键盘2",
		"price": 50,
		"num": 1
	}, {
		"_id": ObjectId("5b74f457089f78dc8f0a4f3b"),
		"order_id": "1",
		"title": "商品键盘3",
		"price": 0,
		"num": 1
	}]
} {
	"_id": ObjectId("5b743d902c327f8d1b360541"),
	"order_id": "2",
	"uid": 7,
	"trade_no": "222",
	"all_price": 90,
	"all_num": 2,
	"items": [{
		"_id": ObjectId("5b743da52c327f8d1b360545"),
		"order_id": "2",
		"title": "牛奶",
		"price": 50,
		"num": 1
	}, {
		"_id": ObjectId("5b743da92c327f8d1b360546"),
		"order_id": "2",
		"title": "酸奶",
		"price": 40,
		"num": 1
	}]
} {
	"_id": ObjectId("5b743d962c327f8d1b360542"),
	"order_id": "3",
	"uid": 9,
	"trade_no": "333",
	"all_price": 20,
	"all_num": 6,
	"items": [{
		"_id": ObjectId("5b743dad2c327f8d1b360547"),
		"order_id": "3",
		"title": "矿泉水",
		"price": 2,
		"num": 5
	}, {
		"_id": ObjectId("5b743dff2c327f8d1b360548"),
		"order_id": "3",
		"title": "毛巾",
		"price": 10,
		"num": 1
	}]
}

筛选 price大于90的

db.order.aggregate([
    {
      $lookup:
        {
          from: "order_item",
          localField: "order_id",
          foreignField: "order_id",
          as: "items"
        }
   },
 {
	$match:{"all_price":{$gte:90}}
 }

])

结果

{
	"_id": ObjectId("5b743d8c2c327f8d1b360540"),
	"order_id": "1",
	"uid": 10,
	"trade_no": "111",
	"all_price": 100,
	"all_num": 2,
	"items": [{
		"_id": ObjectId("5b743d9c2c327f8d1b360543"),
		"order_id": "1",
		"title": "商品鼠标1",
		"price": 50,
		"num": 1
	}, {
		"_id": ObjectId("5b743da12c327f8d1b360544"),
		"order_id": "1",
		"title": "商品键盘2",
		"price": 50,
		"num": 1
	}, {
		"_id": ObjectId("5b74f457089f78dc8f0a4f3b"),
		"order_id": "1",
		"title": "商品键盘3",
		"price": 0,
		"num": 1
	}]
} {
	"_id": ObjectId("5b743d902c327f8d1b360541"),
	"order_id": "2",
	"uid": 7,
	"trade_no": "222",
	"all_price": 90,
	"all_num": 2,
	"items": [{
		"_id": ObjectId("5b743da52c327f8d1b360545"),
		"order_id": "2",
		"title": "牛奶",
		"price": 50,
		"num": 1
	}, {
		"_id": ObjectId("5b743da92c327f8d1b360546"),
		"order_id": "2",
		"title": "酸奶",
		"price": 40,
		"num": 1
	}]
}

Nodejs Mongoose聚合管道aggregate关联查询筛选关联表返回字段

db.order.aggregate([
    {
      $lookup:
        {
          from: "order_item",
          localField: "order_id",
          foreignField: "order_id",
          as: "items"
        }
   },
 {
	$match:{"all_price":{$gte:90}}
 },{
    $project:{order_id:1,uid:1,trade_no:1,all_price:1,all_num:1,items:{ title:1，price:1}}
    
  }

])

返回结果：

{
	"_id": ObjectId("5b743d8c2c327f8d1b360540"),
	"order_id": "1",
	"uid": 10,
	"trade_no": "111",
	"all_price": 100,
	"all_num": 2,
	"items": [{
		"_id": ObjectId("5b743d9c2c327f8d1b360543"),		
		"title": "商品鼠标1",
		"price": 50,
		
	}, {
		"_id": ObjectId("5b743da12c327f8d1b360544"),		
		"title": "商品键盘2",
		"price": 50,
		
	}, {
		"_id": ObjectId("5b74f457089f78dc8f0a4f3b"),		
		"title": "商品键盘3",
		"price": 0,
		
	}]
} {
	"_id": ObjectId("5b743d902c327f8d1b360541"),
	"order_id": "2",
	"uid": 7,
	"trade_no": "222",
	"all_price": 90,
	"all_num": 2,
	"items": [{
		"_id": ObjectId("5b743da52c327f8d1b360545"),		
		"title": "牛奶",
		"price": 50,
		
	}, {
		"_id": ObjectId("5b743da92c327f8d1b360546"),		
		"title": "酸奶",
		"price": 40,		
	}]
} {
	"_id": ObjectId("5b743d962c327f8d1b360542"),
	"order_id": "3",
	"uid": 9,
	"trade_no": "333",
	"all_price": 20,
	"all_num": 6,
	"items": [{
		"_id": ObjectId("5b743dad2c327f8d1b360547"),		
		"title": "矿泉水",
		"price": 2		
	}, {
		"_id": ObjectId("5b743dff2c327f8d1b360548"),		
		"title": "毛巾",
		"price": 10,
		
	}]
}