Mongo Aggregate document with more than 1 array

Question

After $match and $project I have this document structure:

{ 
    "_id" : ObjectId("5a764de08337490ff57c7dc1"), 
    "Lote" : "id", --> Unique Index
    "Planning" : [
         {MainField:10, field1:value, field2:value}, 
         {MainField:20,...},
         {MainField:30...}
    ], 
    "Request" : [
         {MainField:10, field1:value, field2:value}, 
         {MainField:20,...},
         {MainField:30...}],
    ]
}

Planning and Request are arrays.

I like to $unwind both arrays and then only keep the documents that Planning.MainField === Request.MainField

First, solution I have in mind is join both arrays, this is a "long term" solution.

I'd like to play with aggregation if it's possible.

Thanks.

Answer 1

I found the solution:

db.getCollection("works").aggregate(
    // Pipeline
    [
        // Stage 1 - Filter documents not Finished and size of array Request >0
        {
            $match: { Finished:{$ne:true},  Request:{$gt:{$size:0}} }
        },
        // Stage 2 - Project needed fields
        {
            $project: {Lote:1,"Planning.Fase":1,"Planning.MachineName":1,"Planning.OprID":1,"Planning.StartSetupReal":1,"Planning.StartProdReal":1,"Planning.EndProdReal":1,Request:1}
        },
        // Stage 3 - $unwind Planning Array
        {
            $unwind: { path : "$Planning"}
        },
        // Stage 4 - Filter unwind documents with
        {
            $match: { $and:[    {"Planning.field1":{$ne:''}},{"Planning.Field2":{$eq:''}}]}
        },
        // Stage 5 - Unwind Request Array
        {
            $unwind: { path : "$Request"}
        },
        // Stage 6 - Create new field that compares two fields in same document
        {
            $addFields: { "PlanReq": {"$eq":["$Planning.MainField","$Request.MainField"]} }
        },
        // Stage 7 - Filter documents that MainFields of the arrays are equal
        {
            $match: {   "PlanReq":true  }
        },
    ],
);

Answer 2

You can use $expr to match the fields within a document

db.collection.aggregate([
  { "$unwind": "$Planning" },
  { "$unwind": "$Request" },
  { "$match": { "$expr":
    { "$eq": ["$Planning.MainField", "$Request.MainField"] }
  }}
])