Generate ObjectID from Date in Aggregation?

Working with Data
1

I was hoping to be able to use $toObjectId on a date field in my pipeline in order to lookup items from another collection where _id is greater than the date from a previous stage. But apparently $toObjectId can only accept a string.

Is there a way to generate an ObjectId from a date in the aggregation? Sample pipeline stage (fails because of $toObjectId, but you get the idea of what I’m trying to do):

{ $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"] } } ] } }
] } }
2

I have to shoot out so don’t have time to play but I did have a peek here:

To try and create an ID based on a date:

db.getCollection('test2').aggregate([
{
    $addFields:{
    "timestamp": {
        $toObjectId:{
            $concat:[
                {
                    $toString:{
                      "$subtract": [ "$$NOW", new Date("1970-01-01") ]
                    }
                },
                '00000000000'
            ]
            }
       }
    }
},
{
    $addFields:{
        thing:{
            $toDate:'$timestamp'
        }
    }
}
])

This is obviously not working exactly but it’s trying to build an objectID from a date…you may have luck playing about before I get back to this at some point over the weekend.

1 Like
3

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!)