Was having another thought…but to the above, the reason why it’s not working is that we would need to convert the unix timestamp into a hex before passing to $toObjectID, i.e.
Current time as Unix: 1711789762 769
Create an objectID now: 66 07 d6 97 8b e6 b1 6f 5b 23 8a 85
Using the above site, we can see that this is broken down as:
TT TT TT TT MM MM MM PP PP II II II
66 07 d6 97 8b e6 b1 6f 5b 23 8a 85
Where:
TT : Unix timestamp as Hex representation
MM : Machine ID as Hex representation
PP : Process ID as Hex representation
II : Incrementing Number as Hex representation
So we’d need to convert 1711789762 to a hex representation, which we could do but at this point I think it’s getting way too complex, if you’re calculating a field to compare for every record the performance will be horrible.
You can normally compare an object ID against a date, convert the ObjectID to a date and you can compare.
Reformatting your query:
{
"$lookup":{
"from":"leads",
"let":{
"minID":{
"$toObjectId":"$dateEmailed"
},
"post":{
"$toObjectId":"$post"
},
"member":"$member"
},
"as":"leads",
"pipeline":[
{
"$match":{
"$and":[
{
"$expr":{
"$gt":[
"$_id",
"$$minID"
]
}
},
{
"$expr":{
"$eq":[
"$post",
"$$post"
]
}
},
{
"$expr":{
"$eq":[
"$member",
"$$member"
]
}
}
]
}
}
]
}
}
Have you tried converting your ObjectID to a date as opposed to creating an ObjectID from a date?
I may have made errors above in regards to the casting and number of digits needed but I think the gist is right. I think the generation of an ObjectID is probably trying to drive a nail into a plank of woof by using a sledge-hammer attached to a see-saw.
(I tried to use AI to generate that image, but it seemed to REALLY confuse the AI!)