MongoDB nested array search using $map

Question

I have collection which contains nested array. I need to fetch the data based on below condition:

empId : 19107
address.country: "AUS"
group.primaryGroup.primary:"Y"
group.subGroup.primarySubGroup.primary : "Y"

Input:

{
    "empId": "19107",
    "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"
                        },
                        {
                            "primary": "N"
                        }
                    ]
                },
                {
                    "subGroupId": 234,
                    "primarySubGroup": [
                        {
                            "primary": "N"
                        },
                        {
                            "primary": "Y"
                        }
                    ]
                }
            ]
        }
    ]
}

I need the output as below:

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

Below given the query which I tried:

[{"$match" : {"empId":90, "address" : {"$elemMatch": {"country": {"$eq":"AUS"}}}, "group" :{"$elemMatch" : {"primaryGroup": {"$elemMatch" : {"primary": {"$eq": "Y"}}}, "subGroup" : {"$elemMatch" : { "primarySubGroup" : { "$elemMatch": {"primary" : {"$eq" : "Y"}}}}}}}}}, {"$project": {"empId":1, "mobile":1, "address": {"$filter" : {"input": "$address", "as": "d", "cond": {"$eq": ["$$d.country", "AUS"]}}}  , "group" : {"$map": {"input": "$group", "as" : "v", "in": {"primaryGroup": {"$filter": {"input": "$$v.primaryGroup", "as": "vp", "cond": {"$eq": ["$$vp.primary", "Y"]}}}}}}, "subGroup": {"$map" : {"input": "$group", "as" : "n", "in": {"primarySubGroup" : {"$filter": {"input": "$$n.group", "as" : "mp", "cond": {"$eq": ["$$mp.primarySubGroup.primary", "830090"]}}}}}}  }}]

I am new to mongoDB. I tried the below approach (Spring data Match and Filter Nested Array) but I am facing some issue in nested array fetch. ex: Instead of groupId, I need to compare the primaryGroup in $map which is present in group field.

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

Can you add the approach that you've tried and let us know where you are stuck ? — s7vr
– s7vr, Commented Feb 22, 2018 at 17:12
I have updated the query which I tried. I am not getting, how to make filter in subgroup and get the result inside group. Could help me correcting the query — Praveen Dhasarathan
– Praveen Dhasarathan, Commented Feb 22, 2018 at 18:54

s7vr · Accepted Answer · 2018-02-22 19:34:14Z

5

You can use below query.

Couple of things I've changed.

1.No $elemMatch is required for single criteria. Use dot notation instead.

2.Move the subgroup's $map inside group's $map operator.

[
  {"$match":{
    "empId":"19107",
    "address.country":"AUS",
    "group.primaryGroup.primary":"Y",
    "group.subGroup.primarySubGroup.primary":"Y"
  }},
  {"$project":{
    "empId":1,
    "mobile":1,
    "address":{"$filter":{"input":"$address","as":"d","cond":{"$eq":["$$d.country","AUS"]}}},
    "group":{
      "$map":{
        "input":"$group",
        "as":"v",
        "in":{
          "groupId":"$$v.groupId",
          "primaryGroup":{"$filter":{"input":"$$v.primaryGroup","as":"vp","cond":{"$eq":["$$vp.primary","Y"]}}},
          "subGroup":{
            "$map":{
              "input":"$$v.subGroup",
              "as":"n",
              "in":{
                "subGroupId":"$$n.subGroupId",
                "primarySubGroup":{"$filter":{"input":"$$n.primarySubGroup","as":"mp","cond":{"$eq":["$$mp.primary","Y"]}}}
              }
            }
          }
        }
      }
    }
  }}
]

edited Feb 22, 2018 at 19:34

answered Feb 22, 2018 at 19:13

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

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

9 Comments

Praveen Dhasarathan Over a year ago

Thanks for the query but it is not returning any result. In your query, I changed the empId to 19107 and tried but I didn't received any data. Am I missing something?

s7vr Over a year ago

Yw. Fixed and verified.

Praveen Dhasarathan Over a year ago

I am facing a small issue in the query which return empty if it is not finding any match in primarySubGroup array.ex: "subGroup" : [{ "subGroupId" : 123, "primarySubGroup" : [{"primary" : "N"},{"primary" : "N"} ]},{ "subGroupId" : 234, "primarySubGroup" : [{"primary" : "N"},{"primary" : "Y"} ]} ] In the above field 123 is not matching the criteria so I shouldn't get it. I should get only 234 but I am getting 123 array with primarySubGroup empty. Can you suggest me how to fix it.

s7vr Over a year ago

I see. Is it only applicable to primarySubGroup ? What about primaryGroup ?

Praveen Dhasarathan Over a year ago

Yes, Actually In subGroup[] array, subGroup[0].primarySubGroup criteria matches then only I need to take subGroup[0] otherwise I don't need it.

|

Collectives™ on Stack Overflow

MongoDB nested array search using $map

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related