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

CodePudding user response：

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

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

CodePudding user response：

You can use $filter inside a $project to filter out from the field4 array. After that you will get field4: [] for those with 0 filtered elements. To remove those can use $match to test if $field4 has an element at index 0 (if empty no element at index 0).

db.collection.aggregate([
  {
    $project: {
      "field0": 1,  //show field0 in final result
      "field1": 1,  //show field1 in final result
      "field2.field3": 1,  //show field2.field3 in final result
      "_id": 0,     //hide _id in final result
      "field2.field4": {
        $filter: {
          input: "$field2.field4",
          as: "d",       //can put any alias here
          cond: {        //filtering condition
            $and: [
              {
                $eq: [
                  "$$d.sfield1",
                  "svalue111"
                ]
              },
              {
                $eq: [
                  "$$d.sfield2",
                  "svalue211"
                ]
              }
            ]
          }
        }
      }
    }
  },
  {
    "$match": {       //to remove the empty field4 array part 
      "field2.field4.0": {  //testing if field4 array has an element 
        "$exists": true
      }
    }
  }
])

demo