如何使用聚合（匹配、查找和项目）从 mongodb 过滤两个日期之间返回的数据？

当我传入 userId 时，我想获取与注册相关的开始日期和结束日期之间的所有通知。

注册架构

const mongoose = require('mongoose');
const Schema = mongoose.Schema;
const RegisterSchema = new Schema({
    userId: {type: Schema.Types.ObjectId, required: true},
    accessToken: {type:String, required: true, default: null},
})
module.exports = Register = mongoose.model( 'register', RegisterSchema)

这是一些寄存器数据

[
  {
    "_id": "5eac9e815fc57b07f5d0d29f",
    "userId": "5ea108babb65b800172b11be",
    "accessToken": "111"
  },
  {
    "_id": "5ecaeba3c7b910d3276df839",
    "userId": "5e6c2dddad72870c84f8476b",
    "accessToken": "222"
  }
]

下一个文档包含通过 accessToken 与注册架构相关的数据

通知

const mongoose = require('mongoose');
const Schema = mongoose.Schema;
const NotificationSchema = new Schema({
    accessToken: {type:String, required: true},
    summaryId: {type:Number, required: true},
    dateCreated: {type: Date, default: Date.now},
})
module.exports = Notification = mongoose.model( 'notification', NotificationSchema)

这是一些通知数据

[{
    "_id": "5ebf0390c719e60004f42e74",
    "accessToken": "111",
    "summaryId": 1111,
    "dateCreated": "17 Apr 2020" }, 
  {
    "_id": "6ebf0390c719e60004f42e76",
    "accessToken": "222",
    "summaryId": 2221,
    "dateCreated": "18 Apr 2020" },
  {
    "_id": "6ebf0390c719e60004f42e78",
    "accessToken": "111",
    "summaryId": 1112,
    "dateCreated": "25 May 2020" },
  {
    "_id": "6ebf0390c719e60004f42e80",
    "accessToken": "222",
    "summaryId": 2222,
    "dateCreated": "26 May 2020" }
]

Try 1

        var userId = '5ea108babb65b800172b11be'
        var dateStart = '27 Apr 2020';
        var dateEnd   = '27 May 2020'; 

        var match = {$match: { userId: mongoose.Types.ObjectId(userId) } };

        var lookup ={
            $lookup:
            {
                from: "notifications",
                localField: "accessToken",
                foreignField: "accessToken",
                as: "testingThis"
            }
        };

        project = {
            $project: {
                items: {
                    $filter: {
                    input: "$items",
                    as: "item",
                    cond: { {"dateCreated": {'$gte': dateStart, '$lte': dateEnd }} }
                    }
                }
            }
        };

        var agg = [
            match,
            lookup,
            project
        ];


        Register.aggregate(agg)
        .then( events => {
            if(events){
                return resolve(events);
            }else{
                return reject({success:false});
            }
        })
        .catch(err => {
            console.log('ERROR ' + JSON.stringify(err.message));
            return reject({success:false});
        })  

尝试 1 次错误

我预计会看到 5 月 25 日关于 accessToken 111 的通知，但收到错误：

ERROR : {"\"An object representing an expression must have exactly one field: { $gte: new Date(1588017802546), $lte: new Date(1590609802546) }\""}

Try 2

我摆脱了错误......但仍然没有返回任何内容：

        var dateCondition = { $and: [
            { $gte: [ "$$item.dateCreated", dateStart.getTime() ] },
            { $lte: [ "$$item.dateCreated", dateEnd.getTime() ] }
          ] }

          project = {
            $project: {
                items: {
                    $filter: {
                    input: "$items",
                    as: "item",
                    cond: dateCondition
                    }
                }
            }
        };

这就是我的项目的样子：

{
  "$project": {
    "items": {
      "$filter": {
        "input": "$items",
        "as": "item",
        "cond": {
          "$and": [
            {"$gte": ["$$item.dateCreated",1588019227296] },
            {"$lte": ["$$item.dateCreated",1590611227296] }
          ] } } } }
}

Try 3

使用评论中的建议...我将“项目”（从尝试 2）更改为“通知”

        var dateCondition = { $and: [
            { $gte: [ "$$item.dateCreated", dateStart.getTime() ] },
            { $lte: [ "$$item.dateCreated", dateEnd.getTime() ] }
          ] }

          project = {
            $project: {
                notifications: {
                    $filter: {
                    input: "$notifications",
                    as: "item",
                    cond: dateCondition
                    }
                }
            }
        };

仍然不起作用

所以为了尽可能地简化让它工作......我正在尝试使用摘要ID

Try 4

          dateCondition = { $and: [
            { $gte: [ "$$item.summaryId", 1 ] },
            { $lte: [ "$$item.summaryId", 555555 ] }
          ] }          

          project = {
            $project: {
                notifications: {
                    $filter: {
                    input: "$notifications",
                    as: "item",
                    cond: dateCondition
                    }
                }
            }
        };

这有效......所以这让我认为这是一个日期问题。

最终代码 - 有效！

        // make sure the input dates are REALLY date objects
        var dateStart = new Date(inputDateStart);
        var dateEnd = new Date(inputDateEnd);     

        var match = {$match: { userId: mongoose.Types.ObjectId(userId) } };

        var lookup ={
            $lookup:
            {
                from: "my_Notifications",
                localField: "accessToken",
                foreignField: "accessToken",
                as: "notifications"
            }
        };

        var dateCondition = { $and: [
            { $gte: [ "$$item.dateCreated", dateStart ] },
            { $lte: [ "$$item.dateCreated", dateEnd ] }
          ]}  

        project = {
            $project: {
                notifications: {
                    $filter: {
                    input: "$notifications",
                    as: "item",
                    cond: dateCondition
                    } } }
        };

        var agg = [
            match,
            lookup,
            project
        ];

        Register.aggregate(agg)
        .then( ..... )

您的解决方案看起来几乎正确，前提是dateStart and dateStart实际上是Date对象而不是Strings.

Your Try 2不完整我不确定它使用了$lookup from Try 1或不。如果是这样，你必须确保输出$lookup与输入相同$filter。所以你应该改变as in $lookup匹配input of $filter

{
  $lookup: {
    from: "notifications",
    localField: "accessToken",
    foreignField: "accessToken",
    as: "items" // here
  }

}

替代解决方案

我不确定你想要什么作为输出。如果您只需要通知数组而不需要用户对象，您可以尝试以下操作。

[{
  $match: { userId: mongoose.Types.ObjectId(userId) }
}, {
  $lookup: {
    from: "notifications",
    localField: "accessToken", // don't forget to index register.accessToken
    foreignField: "accessToken", // don't forget to index notification.accessToken
    as: "notifications"
  }
}, {
  $unwind: "$notifications"
}, {
  $match: { 
    dateCreated: { $gte: dateStart, $lte: dateEnd } // dateStart, dateEnd should be Date objects
  }
}, { // optional, move notifications to top lvel
  $replaceRoot: { root: '$notifications' }
}]