create a complex mongodb query from a json with multi-level array

Solution 1:

Use $reduce as a loop.

db.foo.aggregate([
    // In this strategy, we walk the field6 array with $reduce and                                                          
    // "rebuild it" with either a filtered field15 or no entry at all.                                                      
    {$addFields: {"field6": {$reduce: {
        input: "$field6",
        initialValue: [], // important: start rebuild with empty array                                                      
            in: {$let: {
                vars: {ee: {$filter: {input: "$$this.field15", as: "z",
                                      cond: {$and:[ {$eq:["$$z.field16","xxx"]},
                                                    {$eq:["$$z.field17","yyy"]}
                                                  ]}
                                     }}
                      },

                 in: {$cond: [
                     {$ne:[0,{$size: "$$ee"}]}, // IF ee is not size 0                                                      
                
                     // THEN append an entry with filter field15                                                            
                     // plus its peer fields.  Since we cannot directly                                                     
                     // say "$$this.field15 = $ee", we use $mergeObjects                                                    
                     // to overlay field15:$$ee onto the existing object                                                    
                     // with the peer fields field7, field8, field10, etc.                                                           
                     // $concatArrays wants arrays, not objects, so wrap                                                    
                     // it in [] to make an array of one:                                                                   
                     {$concatArrays: [ "$$value",
                         [ {$mergeObjects: [ "$$this", {field15: "$$ee"} ]} ]
                                     ]},

                     // ELSE no concat; just pass back the existing array:                                                  
                     "$$value"
                 ]}
        }}  
        }}
    }}

]);

Alternately, if many more conditions need to applied to field6, it might be easier to $unwind on field6 first to isolate further operations on the fields inside that doc. Note however that $unwind and $group could have performance impact if field6 is a lengthy array.

db.foo.aggregate([
    // Get us down to dealing with only one array:
    {$unwind: "$field6"}

    // Overwrite field6.field15 with filtered version of same:
    ,{$addFields: {"field6.field15":
                   {$filter: {input: "$field6.field15",
                     as: "z2",
                     cond: {$and:[ {$eq:["$$z2.field16","xxx"]},
                                   {$eq:["$$z2.field17","yyy"]}
                                 ]}
                   }}
    }}
 
    // .. and eliminate those that have NO xxx/yyy in field16 and field17:
    ,{$match: {"field6.field15": {$ne:[]} }}

    // You might be good enough at this point, but if you really want to reform the
    // shape with an array for field6, use $group to put it back together.
    // Using $first on all the other peer fields to field6 is a bit ungainly, yes, but
    // it does produce the desired result:
    ,{$group: {_id:"$_id",
               "field1": {$first: "$field1"},
               "field2": {$first: "$field2"},
               "field3": {$first: "$field3"},
               "field4": {$first: "$field4"},
               "field5": {$first: "$field5"},
               "field6": {$push: "$field6"} // ah!  Rebuild array
          }}
]);

Solution 2:

Maybe this is what you are looking for:

     db.collection.aggregate([
         {
         "$addFields": {
            "field6": {
                     "$filter": {
                              "input": {
                                    "$map": {
                                     "input": "$field6",
                                     "as": "f6",
                                     "in": {
                                     "$cond": [
                                               true,
                                                       {
                                            "field7": "$$f6.field7",
                                            "field8": "$$f6.field8",
                                            "field9": "$$f6.field9",
                                            "field10": "$$f6.field10",
                                            "field15": {
                                                       "$filter": {
                                                       "input": "$$f6.field15",
                                                      "as": "f15",
                              "cond": {
                               $and: [
                              {
                                $eq: [
                                   "$$f15.field16",
                                      "xxx"
                                     ]
                               },
                                    {
                                $eq: [
                                     "$$f15.field17",
                                      "yyy"
                                     ]
                                 }
                             ]
                          }
                        }
                       }
                      },
                      false
                       ]
                     }
                  }
                 },
                 "as": "cls",
                 "cond": {
                           $ne: [
                          "$$cls.field15",
                           []
                           ]
                         }
         }
         }
       }
       }
      ])

Explained:

1. Create a $addFields stage with new filed6 that will overwrite the old filtered based on map on original field6
2. Provide to the map the filed15 based on second filter about f16 & f17
3. Remove in the first filter from the output the f15 empty arrays since they are generated in the map for those f15 where there is no match found

( Afcourse you can add your initial $match stage with the $elemMatch , I have just removed it to save some space )

playground