MongoDB - aggregation between 2 documents with join using field that is array of strings-CodePudding

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

CodePudding user response：

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",
          
      }
  }   
])