in a mongo query, how to display in an array only the elements that match a filter?

Question

here is a table of objects:

[
    {
        field0: value0,
        field1: value1,
        field2: {
            field3: value3,
            field4 : [
                { sfield1: svalue110, sfield2: svalue210 },
                { sfield1: svalue111, sfield2: svalue211 },
            ]
        }
    },
    {
        field0: value0,
        field1: value1,
        field2: {
            field3: value3,
            field4 : [
                { sfield1: svalue120, sfield2: svalue220 },
                { sfield1: svalue111, sfield2: svalue211 },
                { sfield1: svalue122, sfield2: svalue222 },
            ]
        }
    },
    {
        field0: value0,
        field1: value1,
        field2: {
            field3: value3,
            field4 : [
                { sfield1: svalue130, sfield2: svalue230 },
                { sfield1: svalue131, sfield2: svalue231 },
                { sfield1: svalue132, sfield2: svalue232 },
                { sfield1: svalue133, sfield2: svalue233 },
            ]
        }
    }
]

I want to apply a filter on sfield1 and sfield2 and display only the objects for which field4 has a size > 0. And inside field4, display only the objects that match the filter.

for example, I want to display only the objects for which sfield1 = svalue111 and sfield2 = svalue211

I must have this result:

[
    {
        field0: value0,
        field1: value1,
        field2: {
            field3: value3,
            field4 : [
                { sfield1: svalue111, sfield2: svalue211 },
            ]
        }
    },
    {
        field0: value0,
        field1: value1,
        field2: {
            field3: value3,
            field4 : [
                { sfield1: svalue111, sfield2: svalue211 },
            ]
        }
    }
]

I tried addFields and reduce this way:

{
    $addFields: {
        "field2.field4": {
            $reduce: {
                input: '$field2.field4',
                initialValue: [],
                in: {
                    $let: {
                        vars: {
                            ee: {
                                $filter: {
                                    input: '$$this.field4',
                                    as: 'z',
                                    cond: {
                                        $and: [
                                            {
                                                $eq: [
                                                    '$$z.sfield1',
                                                    "svalue111"
                                                ]
                                            },
                                            { $eq: ['$$z.sfield2', "svalue211"] }
                                        ]
                                    }
                                }
                            }
                        },
                        in: {
                            $cond: [
                                { $ne: [0, { $size: '$$ee' }] },
                                {
                                    $concatArrays: [
                                        '$$value',
                                        [
                                            {
                                                $mergeObjects: [
                                                    '$$this',
                                                    { "field2.field4": '$$ee' }
                                                ]
                                            }
                                        ]
                                    ]
                                },
                                '$$value'
                            ]
                        }
                    }
                }
            }
        }
    }
}

but i have this error :

Error: command failed: {
    "ok" : 0,
    "errmsg" : "Invalid $addFields :: caused by :: FieldPath field names may not contain '.'.",
    "code" : 16412,
    "codeName" : "Location16412"
} : aggregate failed :....

thank you for your help

Answer 1

The best way to filter elements in a subarray is by using an Aggregation with $match and $project.

Example:

[{
  $match: { 
    field2.field4.sfield1: 'svalue1',
    field2.field4.sfield2: 'svalue2'
  }
}, {
  $project: {
    'field2.field4': {
      $filter: {
          input: '$field2.field4',
          as: 'item',
          cond: { $and: [
             {$eq: ["$$item.sfield1","svalue1"]},
             {$eq: ["$$item.sfield2","svalue2"]}
          ]}
      }
    }
  }
}]