Mongoose 查询查找同一文档中日期不匹配的文档

发布于 2025-01-12 08:12:30 字数 638 浏览 1 评论 0原文

我正在尝试编写一个猫鼬查询，它可以找到 REVIEW_DATE 在过去 24 小时内的所有文档，并且 actions 数组中没有与 TYPE 匹配的操作，DETAIL_TYPE 和 ACTION_DATE 与 REVIEW_DATE 不匹配。基本上，如果 REVIEW_DATE 发生变化，我需要重新执行操作，因此审核日期的值会记录在 actionDetails 中。

{
   reviewDate: <REVIEW_DATE>,
   actions: [
      {
         actionType: <TYPE>,
         actionDetails: {
            detailType: <DETAIL_TYPE>,
            valueOfReviewDateAtTimeOfAction: <ACTION_DATE>
         }
      }
   ]
}

由于 mongo 不支持自联接，因此我将撤回 REVIEW_DATE 在过去 24 小时内的所有文档，然后通过迭代操作列表来迭代结果过滤列表。有没有办法通过单个查询或聚合来做到这一点？

原文

I am trying to write a mongoose query that can find all documents where REVIEW_DATE is within the last 24 hours and there is no action in the actions array that matches TYPE, DETAIL_TYPE, and ACTION_DATE does not match REVIEW_DATE. Basically if REVIEW_DATE changes, I need to re-perform the action, so the value of the review date is recorded in the actionDetails.

{
   reviewDate: <REVIEW_DATE>,
   actions: [
      {
         actionType: <TYPE>,
         actionDetails: {
            detailType: <DETAIL_TYPE>,
            valueOfReviewDateAtTimeOfAction: <ACTION_DATE>
         }
      }
   ]
}

Because mongo doesn't support a self join, I am pulling back all documents where REVIEW_DATE is within the last 24 hours and then iterating through the results filtering the list by iterating over the list of actions. Is there a way do do this with a single query or aggregation?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

安稳善良 2025-01-19 08:12:30

如果我正确地回答了您的问题，我相信您需要生成一个使用 $expr 运算符，因为它允许您在同一文档中使用聚合管道表达式，例如

$dateDiff - 获取两个日期之间的差异，并使用 a 确定是否在过去 24 小时内系统聚合变量 $$NOW 作为当前日期时间值的引用
$filter - 过滤 actions 数组以仅返回那些与给定条件匹配的元素
$size - 返回的长度过滤的 actions 数组
$gt/ $lt - 返回一个用于 $expr 条件来判断过滤后的数组是否有在同一文档上满足上述查询条件的元素。

例如，您可以尝试运行以下查询：

Model.find({ 
    $expr: {
        $and: [
            { $lt: [
                { $dateDiff: { 
                    startDate: '$reviewDate', 
                    endDate: '$NOW', 
                    unit: 'hour' 
                } }, 
                24 
            ] },
            { $gt: [
                { $size: { 
                    $filter: { 
                        input: '$actions',
                        cond: {
                            $and: [
                                { $ne: [
                                    '$reviewDate', 
                                    '$this.actionDetails.valueOfReviewDateAtTimeOfAction'
                                ] },
                                { $ne: [
                                    '$this.actionType', 
                                    '$this.actionDetails.detailType'
                                ] },
                            ]
                        }
                    }
                } }, 
                0 
            ] }
        ]
    }
})

或者对于不支持 $dateDiff

Model.find({ 
    $expr: {
        $and: [
            { $lt: [
                new Date(Date.now() - 1000 * 60 * 60 * 24), 
                '$reviewDate' 
            ] },
            { $gt: [
                { $size: { 
                    $filter: { 
                        input: '$actions',
                        cond: {
                            $and: [
                                { $ne: [
                                    '$reviewDate', 
                                    '$this.actionDetails.valueOfReviewDateAtTimeOfAction'
                                ] },
                                { $ne: [
                                    '$this.actionType', 
                                    '$this.actionDetails.detailType'
                                ] },
                            ]
                        }
                    }
                } }, 
                0 
            ] }
        ]
    }
})

If I got your question correctly, I believe that you need to generate a query that uses the $expr operator as it allows you to use aggregate pipeline expressions within the same document such as

$dateDiff - get the difference between two dates and determine wether the <REVIEW_DATE> is within the last 24 hours using a system aggregation variable $$NOW as reference to the current datetime value
$filter - filter the actions array to return only those elements that match the given conditions
$size - returns the length of the filtered actions array
$gt/$lt - returns a bool that is used for the $expr condition to determine whether the filtered array has elements or not that satisfy the above query criteria on the same document.

For example, you could try running the following query:

Model.find({ 
    $expr: {
        $and: [
            { $lt: [
                { $dateDiff: { 
                    startDate: '$reviewDate', 
                    endDate: '$NOW', 
                    unit: 'hour' 
                } }, 
                24 
            ] },
            { $gt: [
                { $size: { 
                    $filter: { 
                        input: '$actions',
                        cond: {
                            $and: [
                                { $ne: [
                                    '$reviewDate', 
                                    '$this.actionDetails.valueOfReviewDateAtTimeOfAction'
                                ] },
                                { $ne: [
                                    '$this.actionType', 
                                    '$this.actionDetails.detailType'
                                ] },
                            ]
                        }
                    }
                } }, 
                0 
            ] }
        ]
    }
})

or for MongoDB versions which do not have support for $dateDiff

Model.find({ 
    $expr: {
        $and: [
            { $lt: [
                new Date(Date.now() - 1000 * 60 * 60 * 24), 
                '$reviewDate' 
            ] },
            { $gt: [
                { $size: { 
                    $filter: { 
                        input: '$actions',
                        cond: {
                            $and: [
                                { $ne: [
                                    '$reviewDate', 
                                    '$this.actionDetails.valueOfReviewDateAtTimeOfAction'
                                ] },
                                { $ne: [
                                    '$this.actionType', 
                                    '$this.actionDetails.detailType'
                                ] },
                            ]
                        }
                    }
                } }, 
                0 
            ] }
        ]
    }
})

回复收藏 0 原文

~没有更多了~