Filtering deeply nested array of objects in Mongo DB

Question

I have a collection in Mongo DB which looks like this -

const clientsColection = { 
    "_id" : ObjectId("5ec8492c27ecdc17362b86cb"),
    "clientName" : "data" ,  
    "users" : [
        {
            "roles" : [], 
            "operations" : [], 
            "_id" : ObjectId("5ecac60ab527bd0ba4a615cf"), 
            "isAdmin" : false, 
            "username" : "Adduser"
        }, 
        {
            "roles" : [], 
            "operations" : [], 
            "_id" : ObjectId("5ecac60ab527bd0ba4a616cf"), 
            "isAdmin" : false, 
            "username" : "new"
        }
    ], 
    "kpiObj" : [
        {
            "kpiName" : "epsilon", 
            "resultObj" : {
                "result" : [
                    {
                        "mark" : 4, 
                        "plz" : "01069"
                    }, 
                    {
                        "mark" : 5, 
                        "plz" : "01067"
                    }
                ], 
            }
        }, 
        {
            "kpiName" : "epsilon2", 
            "resultObj" : {
                "result" : [
                    {
                        "mark" : 3, 
                        "plz" : "01069"
                    }, 
                    {
                        "mark" : 1, 
                        "plz" : "01067"
                    }
                ], 
            }
        }
    ] 
}

I am trying to perform a filter on a nested array of objects using aggregate, project, filter operators but I have not been successful yet in getting the expected output.

I want to achieve the following:-

• stage 1: matching the users array object for users.username and retrieving the matching array of objects (only 1 always).
• stage 2: match the output from stage 1 with kpiName in kpiObj and retrieve the matching array of objects (only one always).
• stage 3. match the output from stage 2 with mark in resultObj and retrieve the matching array of objects.

I've spent the last 3 days watching several tutorials and going through several stack overflow questions but hadn't been able to get the expected output. I've been able to get the expected output for stage1 by using following query. Any help would be greatly appreciated.

db.getCollection("clientsCollection").aggregate([
    { $match: { 'users.username': 'Adduser' } },
    {
        $project: {
            users: {
                $filter: {
                    input: '$users',
                    as: 'user',
                    cond: { $eq: ['$$user.username', 'Adduser'] }
                }
            }, 'kpiObj.kpiName':1, 'kpiObj.resultObj.result.score':1 , 'kpiObj.resultObj.result.plz':1
        }
    }
])

OUTPUT*

for matching username as Adduser, kpiObj.kpiName as epsilon and kpiObj.resultObj.result.mark as 4, I am expecting following output :-

const clientsColection = { 
    "_id" : ObjectId("5ec8492c27ecdc17362b86cb"),
    "clientName" : "data" ,  
    "users" : [
        {
            "username" : "Adduser"
        }
    ], 
    "kpiObj" : [
        {
            "kpiName" : "epsilon", 
            "resultObj" : {
                "result" : [
                    {
                        "mark" : 4, 
                        "plz" : "01069"
                    }
                ], 
            }
        }
    ] 
}

Answer 1

Try below aggregation query :

db.collection.aggregate([
    /** Filter for docs with possible conditions */
    {
      $match: {
        "users.username": "Adduser",
        "kpiObj.kpiName": "epsilon",
        "kpiObj.resultObj.result.mark": 4
      }
    },
    /** Re-create `users` array with new users array with only matching object */
    {
      $addFields: { users: { $filter: { input: "$users", cond: { $eq: [ "$$this.username", "Adduser" ] } } } }
    },
    /** Re-create `kpiObj` array with new kpiObj array with only matching object */
    {
      $addFields: {
        kpiObj: {
          $filter: { input: "$kpiObj", cond: { $eq: [ "$$this.kpiName", "epsilon" ] } }
        }
      }
    },
    /** Unwind (Split array into objects - here you'll have only 1) for flexibility to iterate over `kpiObj.resultObj.result` */
    {
      $unwind: "$kpiObj"
    },
    /** Re-create `kpiObj.resultObj.result` array with new result array with only matching object */
    {
      $addFields: {
        "kpiObj.resultObj.result": {
          $filter: { input: "$kpiObj.resultObj.result", cond: { $eq: [ "$$this.mark", 4 ] } }
        }
      }
    },
    /** remove docs where there is not match on `kpiObj.resultObj.result` */
    {
      $match: { "kpiObj.resultObj.result": { $ne: [] } }
    },
    /** I would consider `kpiObj` & `users` as single objects `{}`, 
     * ratherthan array of single objects `[{}]`
     * Just in case if you need to be an array making object to array
     *  */
    {
      $addFields: { kpiObj: [ "$kpiObj" ] }
    }
  ])

Test : mongoplayground

Note :

You can use $project instead of $addFields to limit fields in each stage. As you wanted add this stage at last :

  {
    $project: {
      "users.username": 1,
      kpiObj: 1
    }
  }

or replace $filter with $map while iterating on users array & just return required fields rather than entire object.