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".
As a result I just need list of items with only 2 fields that exist in document collection: "ActionType" and "Status". I need such a list for a particular batch ID - so batch ID is a filter criteria.
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'
}
-Jacek
possibly this:
db.getCollection('batches').aggregate([
{$match: {_id : ObjectId('61e82ed943389ffc5d277055')}},
{$unwind : {"path" : "$DocumentIDs"}},
{$project: {
_id: 1,
"documentId" : {$toObjectId: "$DocumentIDs"}
}
},
{$lookup:
{
from: 'documents',
localField: "documentId",
foreignField: "_id",
as: "join_results"
}
},
{$unwind: {"path" : "$join_results"}},
{$project:
{
"_id" : 0,
"actionType" : "$join_results.DocumentStatus.ActionId",
"status" : "$join_results.DocumentStatus.Status",
}
}
])