如何根据日期、周和月的范围限制管道的结果

Question

我有一个管道，它每天、每周和每月根据某家公司的玩家向我提供结果。我在仪表板上有一个日期过滤器，它给出了一个iso日期范围。我想根据日期过滤器限制或调整我的结果范围吗？有什么办法在酝酿中吗？

if (chartType === 'Daily') {
statsPipelineCondition = {
_id: { day: { $dayOfMonth: '$createdAt' }, month: { $month: '$createdAt' }, year: { $year: '$createdAt' } },
};
sortCondition = { '_id.year': 1, '_id.month': 1, '_id.day': 1 };
} else if (chartType === 'Monthly') {
statsPipelineCondition = {
_id: { month: { $month: '$createdAt' }, year: { $year: '$createdAt' } },
};
sortCondition = { '_id.year': 1, '_id.month': 1 };
} else {
statsPipelineCondition = {
_id: { week: { $week: '$createdAt' }, year: { $year: '$createdAt' } },
};
sortCondition = { '_id.year': 1, '_id.week': 1 };
}
const statsPipeline = [
{
$group: {
...statsPipelineCondition,
TOTAL: { $sum: 1 },
XR: { $sum: { $cond: [{ $in: ['$company', ['XR', 'CR', 'DX']] }, 1, 0] } },
CT: { $sum: { $cond: [{ $eq: ['$company', 'CT'] }, 1, 0] } },
MR: { $sum: { $cond: [{ $eq: ['$company', 'MR'] }, 1, 0] } },
MG: { $sum: { $cond: [{ $in: ['$company', ['NM', 'MM', 'MG']] }, 1, 0] } },
},
},
{
$sort: {
...sortCondition,
},
},
];

日期过滤器：

datefilter -  { '$gte': '2020-09-01T04:49:50.899Z',
'$lte': '2020-11-03T04:49:50.899Z' }

Answer 1

您需要在$group阶段之前添加一个$match阶段，以根据范围过滤我们的：

let datefilter =  {
'$gte': new Date('2020-09-01T04:49:50.899Z'),
'$lte': new Date('2020-11-03T04:49:50.899Z') 
};
const statsPipeline = [
{
$match: {
createdAt: datefilter
}
},
{
$group: {
...statsPipelineCondition,
TOTAL: { $sum: 1 },
XR: { $sum: { $cond: [{ $in: ['$company', ['XR', 'CR', 'DX']] }, 1, 0] } },
CT: { $sum: { $cond: [{ $eq: ['$company', 'CT'] }, 1, 0] } },
MR: { $sum: { $cond: [{ $eq: ['$company', 'MR'] }, 1, 0] } },
MG: { $sum: { $cond: [{ $in: ['$company', ['NM', 'MM', 'MG']] }, 1, 0] } },
},
},
{
$sort: {
...sortCondition,
},
},
];

Answer 2

好吧，出于某种原因。MongoDB不喜欢字符串中的日期。如果我们只是通过聚合框架提供的运算符来转换字符串，这会更好，这将使事情正常工作。

{
$match: {
$expr: {
$and: [
{
$gte: [
'$createdAt',
{
$dateFromString: {
dateString: dateFilter.$gte,
},
},
],
},
{
$lte: [
'$createdAt',
{
$dateFromString: {
dateString: dateFilter.$lte,
},
},
],
},
],
},
},
},