如何使用聚合从mongodb的两个日期之间过滤返回的数据:匹配,查找和项目? [英] How do I FILTER returned data between two dates from mongodb using an aggregation : match, lookup and project?
问题描述
当我传递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,默认值:null} ,
})
module.exports = Register = mongoose.model('register',RegisterSchema)
这里有一些寄存器数据
[
{
_id: 5eac9e815fc57b07f5d0d29f,
userId: 5ea108babb65b800172b11be,
accessToken: 111
},
{
_id : 5ecaeba3c7b910d3276df839,
userId: 5e6c2dddad72870c84f8476b,
accessToken: 222
}
]
下一个文档包含通过accessToken与寄存器架构相关的数据
Notifications
const mongoose = require('mongoose');
const Schema = mongoose.Schema;
const NotificationSchema = new Schema({
accessToken:{type:String,required:true},
summaryId:{type:Number,required:true},
dateCreated:{类型:日期,默认值:Date.now},
})
module.exports = Notification = mongoose.model('notification',NotificationSchema)
以下是一些通知数据
[{
_id: 5ebf0390c719e60004f42e74,
accessToken: 111,
summaryId:1111,
dateCreated: 2020年4月17日},
{
_id: 6ebf0390c719e60004f42e76,
accessToken: 222,
summaryId:2221,
dateCreated: 2020年4月18日},
{
_id: 6ebf0390c719e60004f42e78,
accessToken: 111,
summaryId:1112,
dateCreated: 2020年5月25日},
{
_id: 6ebf0390c719e60004f42e80,
accessToken: 222,
summaryId:2222,
dateCreated: 2020年5月26日}
]
尝试1
var userId ='5ea108babb65b800172b11be'
var dateStart ='2020年4月27日';
var dateEnd = 2020年5月27日;
var match = {$ match:{userId:mongoose.Types.ObjectId(userId)}};
var查找= {
$ lookup:
{
来自: notifications,
localField: accessToken,
foreignField: accessToken,
表示为: testingThis
}
};
项目= {
$ project:{
项:{
$ filter:{
输入: $ items,
as : item,
cond:{{ dateCreated:{'$ gte':dateStart,'$ lte':dateEnd}}}
}
}
}
};
var agg = [
比赛,
查找,
项目
];
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:{ \表示表达式的对象必须完全具有一个字段:{$ gte:new Date(1588017802546),$ lte:new Date(1590609802546)} \}
尝试2
我摆脱了错误...但是仍然没有返回任何内容:
var dateCondition = {$ and:[
{$ gte:[ $$ item.dateCreated,dateStart.getTime()]},
{$ lte:[ $$ item .dateCreated,dateEnd.getTime()]}
]}
project = {
$ project:{
项目:{
$ filter: {
input: $ items,
as: item,
cond:dateCondition
}
}
}
};
这是我的项目:
< pre class = lang-js prettyprint-override>
{
$ project:{
items:{
$ filter:{
input: $ items,
as: item,
cond:{
$ and:[
{ $ gte :[ $$ item.dateCreated,1588019227296]},
{ $ lte:[ $$ item.dateCreated,1590611227296]}
]}}}}
}
尝试3
使用以下建议评论...我将项目(从尝试2)更改为通知
var dateCondition = {$ and:[
{$ gte:[ $$ item.dateCreated,dateStart.getTime()]},
{$ lte:[ $$ item.dateCreated,dateEnd .getTime()]}
]}
project = {
$ project:{
通知:{
$ filter:{
输入: $ notifications,
as: item,
cond:dateCondition
}
}
}
};
仍然不起作用
所以在尝试尽可能简化以使其正常工作...我正在尝试使用摘要ID
尝试4
dateCondition = {$ and:[
{$ gte:[ $$ item.summaryId,1]},
{$ lte:[ $$ item.summaryId,555555]}
]}
project = {
$ project:{
通知:{{
$ filter:{
输入: $ notifications,
as: item,
cond:dateCondition
}
}
}
};
它的工作原理……让我觉得这是个约会问题。
最终代码-有效!
//确保输入日期是真正的日期对象
var dateStart = new Date(inputDateStart);
var dateEnd = new Date(inputDateEnd);
var match = {$ match:{userId:mongoose.Types.ObjectId(userId)}};
var查找= {
$ lookup:
{
来自: my_Notifications,
localField: accessToken,
foreignField: accessToken,
为:通知
}
};
var dateCondition = {$ and:[
{$ gte:[ $$ item.dateCreated,dateStart]},
{$ lte:[ $$ item .dateCreated,dateEnd]}
]}
project = {
$ project:{
通知:{
$ filter:{
输入: $ notifications,
as: item,
cond:dateCondition
}}}
};
var agg = [
比赛,
查找,
项目
];
Register.aggregate(agg)
.then(.....)
您的解决方案看起来几乎正确,只要 dateStart
和 dateStart
实际上是 Date
对象,而不是 String
s。
您的尝试2 不完整我不确定是否使用尝试1 中的 $ lookup
。如果是这样,则必须确保 $ lookup
的输出与 $ filter
的输入相同。因此,您应该在 $ lookup
中将更改为
以匹配输入
$ filter
{
$ lookup:{
来自: notifications,
localField: accessToken,
foreignField: accessToken,
as: items //这里
}
}
替代解决方案
我不确定要输出什么。如果只需要通知数组而不需要用户对象,则可以尝试以下操作。
[{
$ match :{{userId:mongoose.Types.ObjectId(userId)}
},{
$ lookup:{
from: notifications,
localField: accessToken,//不要忘记索引register.accessToken
foreignField: accessToken,//不要忘记索引notification.accessToken
如: notifications
}
}, {
$ unwind: $ notifications
},{
$ match:{
dateCreated:{$ gte:dateStart,$ lte:dateEnd} // dateStart,dateEnd应该是Date对象
}
},{//可选,将通知移至顶层
$ replaceRoot:{根目录:'$ notifications'}
}]
I would like to get all Notifications between a start and end date that are related to a Register when I pass in a userId.
Register Schema
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)
Here is some register data
[
{
"_id": "5eac9e815fc57b07f5d0d29f",
"userId": "5ea108babb65b800172b11be",
"accessToken": "111"
},
{
"_id": "5ecaeba3c7b910d3276df839",
"userId": "5e6c2dddad72870c84f8476b",
"accessToken": "222"
}
]
The next document contains data that is related to the Register schema via the accessToken
Notifications
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)
Here is some notification data
[{
"_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});
})
Try 1 Error
I am expecting to see the notification for 25 May for accessToken of 111, but I am getting an error:
ERROR : {"\"An object representing an expression must have exactly one field: { $gte: new Date(1588017802546), $lte: new Date(1590609802546) }\""}
Try 2
I got rid of the error ... but still getting nothing returned:
var dateCondition = { $and: [
{ $gte: [ "$$item.dateCreated", dateStart.getTime() ] },
{ $lte: [ "$$item.dateCreated", dateEnd.getTime() ] }
] }
project = {
$project: {
items: {
$filter: {
input: "$items",
as: "item",
cond: dateCondition
}
}
}
};
This is what my project looks like:
{
"$project": {
"items": {
"$filter": {
"input": "$items",
"as": "item",
"cond": {
"$and": [
{"$gte": ["$$item.dateCreated",1588019227296] },
{"$lte": ["$$item.dateCreated",1590611227296] }
] } } } }
}
Try 3
using advice from comments... I changed 'items' (from try 2) to 'notifications'
var dateCondition = { $and: [
{ $gte: [ "$$item.dateCreated", dateStart.getTime() ] },
{ $lte: [ "$$item.dateCreated", dateEnd.getTime() ] }
] }
project = {
$project: {
notifications: {
$filter: {
input: "$notifications",
as: "item",
cond: dateCondition
}
}
}
};
still does not work
so in an attempt to simplify as much as I can to get this to work... I am trying it with summary id
Try 4
dateCondition = { $and: [
{ $gte: [ "$$item.summaryId", 1 ] },
{ $lte: [ "$$item.summaryId", 555555 ] }
] }
project = {
$project: {
notifications: {
$filter: {
input: "$notifications",
as: "item",
cond: dateCondition
}
}
}
};
which works... so that leads me to think it is a date problem.
Final Code - works!
// 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( ..... )
Your solution looks almost right, provided that dateStart
and dateStart
are actually Date
objects and not String
s.
Your Try 2 was incomplete I'm not sure it uses the $lookup
from Try 1 or not. If so you have to make sure the output of $lookup
is the same as input of $filter
. So you should change as
in $lookup
to match input
of $filter
{
$lookup: {
from: "notifications",
localField: "accessToken",
foreignField: "accessToken",
as: "items" // here
}
}
Alternative Solution
I'm not sure what you want as output. If you only need array of notifications without the user object, you can try the following.
[{
$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' }
}]
这篇关于如何使用聚合从mongodb的两个日期之间过滤返回的数据:匹配,查找和项目?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!