fetch issue in multiple nested array document from MongoDB

Question

I have multiple nested array in each document in each level. I have to fetch the document which matches below criteria:

1. empId : 123
2. address.country: "AUS"
3. group.primaryGroup.primary:"Y"
4. group.subGroup.primarySubGroup.primary : "Y"
5. group.subGroup.primarySubGroup.country : "AUS"
6. In group[0], if primaryGroup.primary = "Y" is not matching then ignore the group[0].
7. In group[0], if subGroup.primarySubGroup.primary = "Y" and subGroup.primarySubGroup.country = "AUS" is not matching then also ignore the entire group[0].
8. If primaryGroup and primarySubGroup matching the criteria then I have to fetch the particular group , primaryGroup and primarySubGroup array.
9. If none of the group[].primaryGroup[] is matching the criteria then I can ignore the entire document.
10. If none of the group[].subGroup[].subPrimaryGroup[] is matching the criteria then I can ignore the entire document.

I am not able to apply the criteria "point 7" which I mentioned above.

Below given the query which I tried:

db.temp.aggregate([{"$match": {"empId": 123,"address.country": "AUS","group.primaryGroup.primary": "Y","group.subGroup.primarySubGroup.primary": "Y","group.subGroup.primarySubGroup.country": "AUS"}}, {"$project": {"empId": 1,"mobile": 1,"address": {"$filter": {"input": "$address","as": "d","cond": {"$eq": ["$$d.country", "AUS"]}}},"group": {"$map": {"input": {"$filter": {"input": "$group","as": "b","cond": {"$in": ["Y", "$$b.primaryGroup.primary"]}}}, "as": "vp","in": {"groupId": "$$vp.groupId","primaryGroup": {"$filter": {"input": "$$vp.primaryGroup","as": "vp","cond": {"$eq": ["$$vp.primary", "Y"]}}},"subGroup": {"$map": {"input": {"$filter": {"input": "$$vp.subGroup","as": "np","cond": {"$and": [{"$in": ["Y", "$$np.primarySubGroup.primary"]}, {"$in": ["AUS", "$$np.primarySubGroup.country"]}]}}},"as": "n","in": {"subGroupId": "$$n.subGroupId","primarySubGroup": {"$filter": {"input": "$$n.primarySubGroup","as": "n","cond": {"$and": [{"$eq": ["$$n.primary", "Y"]}, {"$eq": ["$$n.country", "AUS"]}]}}}}}}}}}}}]).pretty()

I referred the URL (MongoDB nested array search using $map)

Input:

[
    {
        "empId": 123,
        "address": [
            {
                "street": "no.12 wilson street",
                "country":"AUS"
            },
            {
                "description": "No.32 watson street",
                "country":"CAN"
            }
        ],
        "mobile": 2387468238,
        "group": [
            {
                "groupId": 75227,
                "primaryGroup": [
                    {
                        "primary": "Y"
                    },
                    {
                        "primary": "N"
                    }
                ],
                "subGroup": [
                    {
                        "subGroupId": 123,
                        "primarySubGroup": [
                            {
                                "primary": "Y",
                                "country": "AUS"
                            },
                            {
                                "primary": "N",
                                "country": "IND"
                            }
                        ]
                    },
                    {
                        "subGroupId": 234,
                        "primarySubGroup": [
                            {
                                "primary": "N",
                                "country": "USA"
                            },
                            {
                                "primary": "Y",
                                "country": "IND"
                            }
                        ]
                    },
                    {
                        "subGroupId": 432,
                        "primarySubGroup": [
                            {
                                "primary": "Y",
                                "country": "AUS"
                            },
                            {
                                "primary": "N",
                                "country": "CAN"
                            }
                        ]
                    }
                ]
            }, {
                "groupId": 33333,
                "primaryGroup": [
                    {
                        "primary": "Y"
                    },
                    {
                        "primary": "N"
                    }
                ],
                "subGroup": [
                    {
                        "subGroupId": 6734,
                        "primarySubGroup": [
                            {
                                "primary": "Y",
                                "country": "CAN"
                            },
                            {
                                "primary": "N",
                                "country": "IND"
                            }
                        ]
                    },
                    {
                        "subGroupId": 9864,
                        "primarySubGroup": [
                            {
                                "primary": "N",
                                "country": "IND"
                            },
                            {
                                "primary": "Y",
                                "country": "USA"
                            }
                        ]
                    }
                ]
            }, {
                "groupId": 44444,
                "primaryGroup": [
                    {
                        "primary": "N"
                    },
                    {
                        "primary": "N"
                    }
                ],
                "subGroup": [
                    {
                        "subGroupId": 6734,
                        "primarySubGroup": [
                            {
                                "primary": "Y",
                                "country": "AUS"
                            },
                            {
                                "primary": "N",
                                "country": "IND"
                            }
                        ]
                    },
                    {
                        "subGroupId": 9864,
                        "primarySubGroup": [
                            {
                                "primary": "N",
                                "country": "IND"
                            },
                            {
                                "primary": "Y",
                                "country": "USA"
                            }
                        ]
                    }
                ]
            }
        ]
    }
]

output:

[
    {
        "empId": 123,
        "address": [
            {
                "street": "no.12 wilson street",
                "country":"AUS"
            }
        ],
        "mobile": 2387468238,
        "group": [
            {
                "groupId": 75227,
                "primaryGroup": [
                    {
                        "primary": "Y"
                    }
                ],
                "subGroup": [
                    {
                        "subGroupId": 123,
                        "primarySubGroup": [
                            {
                                "primary": "Y",
                                "country": "AUS"
                            }
                        ]
                    },
                    {
                        "subGroupId": 432,
                        "primarySubGroup": [
                            {
                                "primary": "Y",
                                "country": "AUS"
                            }
                        ]
                    }
                ]
            }
        ]
    }
]

Could you please help me with this. Thanks in advance.

score 1 · Accepted Answer · answered Feb 25 '18 at 06:24

There was a typo and filter issue in your aggregation pipeline

$ prefix was missing in subgroup filter
added $filter to remove group with 0 subGroups

pipeline

db.temp.aggregate([
   {
      "$match":{
         "empId":123,
         "address.country":"AUS",
         "group.primaryGroup.primary":"Y",
         "group.subGroup.primarySubGroup.primary":"Y",
         "group.subGroup.primarySubGroup.country":"AUS"
      }
   },
   {
      "$project":{
         "empId":1,
         "mobile":1,
         "address":{
            "$filter":{
               "input":"$address",
               "as":"d",
               "cond":{
                  "$eq":[ "$$d.country","AUS" ]
               }
            }
         },
         "group":{
            "$map":{
               "input":{
                  "$filter":{
                     "input":"$group",
                     "as":"b",
                     "cond":{
                        $and : [
                            { "$in":[ "Y", "$$b.primaryGroup.primary" ] }
                        ]
                     }
                  }
               },
               "as":"vp",
               "in":{
                  "groupId":"$$vp.groupId",
                  "primaryGroup":{
                     "$filter":{
                        "input":"$$vp.primaryGroup",
                        "as":"vc",
                        "cond":{
                           "$eq":[ "$$vc.primary", "Y" ]
                        }
                     }
                  },
                  "subGroup":{
                     "$map":{
                        "input":{
                           "$filter":{
                              "input":"$$vp.subGroup",
                              "as":"np",
                              "cond":{
                                 "$and":[
                                    { "$in":[ "Y", "$$np.primarySubGroup.primary" ] },
                                    { "$in":[ "AUS","$$np.primarySubGroup.country" ] }
                                 ]
                              }
                           }
                        },
                        "as":"n",
                        "in":{
                           "subGroupId":"$$n.subGroupId",
                           "primarySubGroup":{
                              "$filter":{
                                 "input":"$$n.primarySubGroup",
                                 "as":"mp",
                                 "cond":{
                                    "$and":[
                                       { "$eq":[ "$$mp.primary", "Y" ] },
                                       { "$eq":[ "$$mp.country", "AUS" ] }
                                    ]
                                 }
                              }
                           }
                        }
                     }
                  }
               }
            }
         }
      }
   },
   {"$project":
        {
             "empId":1,
             "mobile":1,
             "address":1,
             "group":{
                $filter : {
                    input : "$group",
                    as : "g",
                    cond : {$gt : [{$size : "$$g.subGroup"}, 0]}
                }
            }
        }
    }
]).pretty()

fetch issue in multiple nested array document from MongoDB

1 Answers1