0

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.

Improve this question

1 Answer 1

Reset to default
1

There was a typo and filter issue in your aggregation pipeline

  1. $ prefix was missing in subgroup filter
  2. 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()
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.