mongodb实战2

数据准备（本节数据需与上一节关联）

db.doc_2.insertMany([
  {
    tid:ObjectId("5fc05968b6b39ed267069813"),
    tel:13344556688,
    email:"hahaha1@foxmail.com"

  },
  {
    tid:ObjectId("5fc05968b6b39ed267069814"),
    tel:13444566688,
    email:"hahaha2@foxmail.com"

  },
  {
    tid:ObjectId("5fc05968b6b39ed267069815"),
    tel:18544156688,
    email:"hahaha3@foxmail.com"

  }
])

目标：

1.统计年龄在31~34(包含31和34)之间的数据，并取出最后一条数据的name和最后一条数据中test3取和

db.doc_1.aggregate([
    {
        $match: {
            age: {
                $gte: 31,
                $lte: 34
            }
        }
    },
    {
        $group: {
            _id: null,    //_id 强制，可以设置为null
            name: {
                $last: "$name"
            },
            sunNum: {
                $last: {
                    $sum: "$test3"
                }
            }
        }
    },
    
])

结果：

// 1
{
    "_id": null,
    "name": "马保",
    "sunNum": 10
}

2.通过test和test1表联查，关联id为test._id和test1.tid,取出手机号和邮箱，并过滤掉邮箱或者手机号为空的数据

db.test.aggregate([
    {
        $lookup: {
            from: "test1", //关联test1
            localField: "_id", //test表id
            foreignField: "tid", //test1关联id
            as: "sec" //别名
        }
    },
    {
        $unwind: {
            //拆分子数组
            path: "$sec",
            preserveNullAndEmptyArrays: true //空的数组也拆分
        }
    },
    {
        $group: {
            _id: "$_id",
            tel: {
                $first: "$sec.tel"
            },
            email: {
                $first: "$sec.email"
            },
            
        }
    },
    {
        $match: {
            "$or": [{
                "tel": {
                    $ne: null //过滤tel为空的数据
                },
                "email": {
                    $ne: null //过滤email为空的数据
                }
            }]
        }
    }
])

3.通过test和test1表联查，使用$unset过滤掉email，仅显示test1中的tel，并过滤掉邮箱或者手机号码为空的数据

db.doc_1.aggregate([
    {
        $lookup: {
            from: "doc_2",  //关联test1
            localField: "_id",  //test表id
            foreignField: "tid",//test1关联id
            as: "sec"   //别名
        }
    },
    {
      $unwind:{
        path:"$sec",
        preserveNullAndEmptyArrays:true
      }
    },
    {
      $match:{
        $or:[
          {
              "sec.tel":{
                $ne:null
              },
              "sec.email":{
                $ne:null
              }
          }
        ]
      }
    },
    {
      $unset:
        ["sec.email"]
     }
])

结果：

// 1
{
    "_id": ObjectId("60c53f2e05c904135f531052"),
    "test1": [
        "a1",
        "b1",
        "c1"
    ],
    "test2": {
        "n1": "123",
        "n2": "321"
    },
    "test3": [
        1,
        2,
        3
    ],
    "name": "马国",
    "age": 31,
    "sec": {
        "_id": ObjectId("60d182a83997bc0e9805f672"),
        "tid": ObjectId("60c53f2e05c904135f531052"),
        "tel": 13344556688
    }
}

// 2
{
    "_id": ObjectId("60c53f2e05c904135f531053"),
    "test1": [
        "a1",
        "b2",
        "c2"
    ],
    "test2": {
        "n1": "456",
        "n2": "654"
    },
    "test3": [
        1,
        3,
        3
    ],
    "name": "牛宝宝",
    "age": 34,
    "sec": {
        "_id": ObjectId("60d182a83997bc0e9805f673"),
        "tid": ObjectId("60c53f2e05c904135f531053"),
        "tel": 13444566688
    }
}