Aggregation between 2 tables with join using field that is array of strings

Working with Data
1

My schema looks like this

batches collection:

{"_id":{"$oid":"61e82ed943389ffc5d277055"},
"name":"Batch ready to process",
"DocumentIDs":["61e82edb75d2841f2a8a023d"]}

documents collection:

{"_id":{"$oid":"61e82edb75d2841f2a8a023d"},
"DocumentStatus":{"ActionId":"1","ActionType":"Type1","Status":"Status1"}
}

So the batches collection have array of document IDs. How I can merge these 2 collections? I was trying to use aggregate lookup but it seems it will not work for this case because its schema only allows to compare single simple fields and I need to check if the document ID exists in array “DocumentIDs”

This is schema from aggregation lookup

/**
 * from: The target collection.
 * localField: The local join field.
 * foreignField: The target join field.
 * as: The name for the results.
 * pipeline: The pipeline to run on the joined collection.
 * let: Optional variables to use in the pipeline field stages.
 */
{
  from: 'string',
  localField: 'string',
  foreignField: 'string',
  as: 'string'
}
2

Your problem is that you keep your document ids as string in your batches collection.

This means that every time you lookup you must convert your string ids into ObjectId or the other way around. This means you will not be able to use the index on the converted values.

Since it is best to keep ObjectId as ObjectId, the first step is to make sure DocumentIDs holds the ID as ObjectId.

Recent $lookup is more powerfull than the one you shown. See https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/

1 Like
3

@Jacek_Kowalski, if my post was useful please mark it as the solution for the benefits to all and to keep this forum useful and efficient.