选择主题

选择导航背景

MongoDB高级查询aggregate聚合管道

1.png

先导入数据库

$project 、$match 、$group、$sort、$limit、$skip、$lookup 表关联

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

 }

])

=======================

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

}


])

====================

db.order.aggregate([

{

$project:{ trade_no:1, all_price:1 }

},

{

$match:{"all_price":{$gte:90}}

},

{

$sort:{"all_price":-1}

},

{

$skip:1

}


])

===================

实现下面结果




[

{

Order_id:’1’,

Trade_no:’’,

Items:[

{

Title:’鼠标’,

Price:20

},

{

Title:’键盘’,

Price:20

}


]

},

{

Order_id:’2’,

Trade_no:’’,

Items:[

{

Title:’鼠标’,

Price:20

},

{

Title:’键盘’,

Price:20

}


]

}

]










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

}]

}










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

}]

}










db.order.aggregate([

{

      $lookup:

        {

          from: "order_item",

          localField: "order_id",

          foreignField: "order_id",

          as: "items"

        }

   },

{

$project:{ trade_no:1, all_price:1,items:1 }

},

{

$match:{"all_price":{$gte:90}}

},

{

$sort:{"all_price":-1}

},

])






{

"_id": ObjectId("5b743d8c2c327f8d1b360540"),

"trade_no": "111",

"all_price": 100,

"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"),

"trade_no": "222",

"all_price": 90,

"items": [{

"_id": ObjectId("5b743da52c327f8d1b360545"),

"order_id": "2",

"title": "牛奶",

"price": 50,

"num": 1

}, {

"_id": ObjectId("5b743da92c327f8d1b360546"),

"order_id": "2",

"title": "酸奶",

"price": 40,

"num": 1

}]

}

====================

1.png

2.png

3.png

4.png

5.png

9.png

6.png

7.png

8.png