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

In that example, what criteria should be tested against kpiName? What are you trying to get in terms of output documents? — Joe
– Joe, Commented Jun 4, 2020 at 23:26

whoami - fakeFaceTrueSoul · Accepted Answer · 2020-06-05 00:08:20Z

4

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.

answered Jun 5, 2020 at 0:08

whoami - fakeFaceTrueSoul

18.1k6 gold badges35 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

relu Over a year ago

Thank you very much. It is more than perfect :) Can you recommend some good tutorials over the internet that help can me introduce to these concepts of esp Mongo v 4.x?

whoami - fakeFaceTrueSoul Over a year ago

@newbie : Check this :: university.mongodb.com , That's free & directly from MongoDB.

Collectives™ on Stack Overflow

Filtering deeply nested array of objects in Mongo DB

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related