-
I have two collection--------- named leads and trips.
-
leads contains—
- _userId: ObjectId,
- tripId: ObjectId,
- insertDate: ISODate,
- confirmed:boolean,
.
.
.
.
*etc
-
trips contains-----------
- _userId: ObjectId,
- _id: ObjectId,
- tripCreatedDate: ISODate,
- confirmed:boolean,
.
.
.
.
*etc
What i want to find out??
Question 1 : I need find out the total no of bookings customer already had before new lead was created, (new lead === insertDate in leads)
what I am doing ---- first I am passing some date ranges where I need to get the sample data, then looking up for matching _userId in the tripsCollection, then trying to eleminate those documents where tripCreationDate in trips is greater than insertDate in leads. after am trying to get the count of document.
Correct Result I am getting: Is total no of bookings so far.
Wront Result I am getting: Instead of total no of booking I have to get total no of booking before new lead was created mean insertDate.
here is my aggregation --------------------------------------------------
[
{
$match: {
$or: [
{
calls: {
$elemMatch: {
callTime: {
$gte: ISODate(
“2024-06-01T00:00:00Z”
)
}
}
}
},
{
$and: [
{ calls: { $exists: false } },
{
insertDate: {
$gte: ISODate(
“2024-06-01T00:00:00Z”
)
}
}
]
}
]
}
},
{
$lookup: {
from: “trips”,
let: {
userId: “$_userId”,
insertDate: “$insertDate”
},
pipeline: [
{
$match: {
$expr: {
$and: [
{ $eq: [“$$userId”, “$_userId”] },
{
$lt: [
“$$insertDate”,
“$tripCreatedDate”
]
}
]
}
}
},
{
$count: “previousBookingCount”
}
],
as: “previousBookings”
}
},
{
$addFields: {
previousBookingsCount: {
$ifNull: [
{
$arrayElemAt: [
“$previousBookings.previousBookingCount”,
0
]
},
0
]
}
}
},
{
$addFields: {
previousBookingsCount: {
$ifNull: [
{
$arrayElemAt: [
“$previousBookings.previousBookingCount”,
0
]
},
0
]
}
}
},
{
$project: {
_id: 1,
_userId: 1,
tripId: 1,
custName: 1,
email: 1,
insertDate: 1,
previousBookingsCount: 1,
bookingStatus: 1,
}
},
]
I am new to mongoDB, I would really appritiate someone’s help on this.