I have a query in MongoDB which is suppose to pull records based on certain criteria. I think I may be misunderstanding how the aggregation pipeline functions. The query in questions is pulling records correctly for the most part but it is bringing in some records that do not match the criteria. It seems to be the date portion of the query that is causing this and I think I may be doing it wrong. Any help would be appreciated.
Query:
var theFirst = new Date("2016", "0", "1", "0", "0", "0", "0");
var theLast = new Date("2017", "0", "1", "0", "0", "0", "0");
var query = [
{ $match: { $and: [{ "ProjectName": { '$regex': '^((?!win).)*$', '$options': 'i' } }, { "ProjectName": { '$regex': '^((?!Champion Cooler recommended).)*$', '$options': 'i' } }] } },
{ $match: { $or: [{ "Payments": { $exists: true } }, { "Reports": { $exists: true } }] } },
{ $match: { $or: [{ "Payments.ScheduledDate": { $lt: theLast, $gt: theFirst } }, { "Reports.ScheduledDate": { $lt: theLast, $gt: theFirst } }] } }, {
$lookup: {
from: 'winorganizations',
localField: 'OrganizationId',
foreignField: '_id',
as: 'orgitem'
}
}, {
$project: {
_id: 0,
OrgName: '$Organization',
Address1: '$Org_Info.Address1',
Address2: '$Org_Info.Address2',
Address3: '$Org_Info.Address3',
OrgCity: '$Org_Info.City',
OrgState: '$Org_Info.State',
OrgZip: '$Org_Info.Zip',
TaxID: '$Org_Info.TaxId',
orgitem: '$orgitem',
OrgContactName: "",
OrgContactEmailAddress: "",
GrantContactName: { "$arrayElemAt": ["$Contacts.Name", 0] },
GrantContactEmail: { "$arrayElemAt": ["$Contacts.Email", 0] },
GrantNbr: "$WINNbr",
AmtApproved: "$Amount",
DateAccepted: "$DateAccepted",
ProjectName: "$ProjectName",
ProgramArea: "$ProgramArea",
Initiative: "$Initiative",
Strategy: "$Strategy",
ProgramOfficer: "$programOfficer",
}
}
];
This works for the most part except the third line { $match: { $or: [{ "Payments.ScheduledDate": { $lt: theLast, $gt: theFirst } }, { "Reports.ScheduledDate": { $lt: theLast, $gt: theFirst } }] } },
I want all documents where Payments (embedded array) have a scheduled date of greater than $gt
01/01/2016 AND less than $lt
01/01/2017 OR Reports (embedded array) have a scheduled date of greater than $gt
01/01/2016 AND less than $lt
01/01/2017
It is pulling a document where it has a Report with a scheduled date of 12/30/2017 which obviously meets on part if the criteria but its like its ignoring the $lt. Ideally this record should not be returned.
via Ohjay44
No comments:
Post a Comment