How to perform a string-to-ObjectId collection join in a DocumentDB aggregation

Question

We are migrating our application from Mongo 4.0 to DocumentDB 4.0 and are hampered by DocumentDB's lack of a $toObjectId() function.

In our application, we have two collections: person and changelog. Example documents:

person:

{
  _id :      ObjectId("5a304138d9fe0b0001bdb65b"), 
  firstname: "Mike"
} 

changelog:

{ 
  field:    "firstname",  
  oldValue: "Michael", 
  newValue: "Mike", 
  targetId: "5a304138d9fe0b0001bdb65b"
}

The changelog.targetId string field corresponds to the person._id ObjectId field.

We have an changelog aggregation that joins changelog to person using these pipeline bits:

{$addFields: {'personId': {$toObjectId '$targetId'}}
{$lookup: { from: 'person', localField: 'personId', foreignField: '_id', as: "joinedPerson"} }

However, this aggregation will not execute in DocumentDB because DocumentDb lacks the $toObjectId function:

Command failed with error 305: 'Aggregation project operator not supported: '$toObjectId''

Is there any way to perform a string-to-objectId join in a DocumentDB aggregation?

Note that a good solution will not require any database backfills -- we don't want to add any new fields or modify any existing fields in the database in order to get this functionality.

Answer 1

It's not possible on DocumentDB, as it was not possible on MongoDB prior to 4.0, before they had introduced $toObjectId.

Answer 2

As of November 6, 2023, $toObjectId is now supported on both DocumentDB 4.0 and 5.0: https://docs.aws.amazon.com/documentdb/latest/developerguide/release-notes.html#release-notes.11-06-2023