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, Feb 22 '18 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, Feb 22 '18 at 18:54

s7vr · Accepted Answer · 2018-02-22T19:34:14.217

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 '18 at 19:34

answered Feb 22 '18 at 19:13

s7vr

73,656
11
106
127

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? – Praveen Dhasarathan Feb 22 '18 at 19:27
Yw. Fixed and verified. – s7vr Feb 22 '18 at 19:34
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. – Praveen Dhasarathan Feb 22 '18 at 20:01
I see. Is it only applicable to primarySubGroup ? What about primaryGroup ? – s7vr Feb 22 '18 at 20:17
Yes, Actually In subGroup[] array, subGroup[0].primarySubGroup criteria matches then only I need to take subGroup[0] otherwise I don't need it. – Praveen Dhasarathan Feb 22 '18 at 20:18
You can try `"subGroup":{ "$map":{ "input":{"$filter":{"input":"$$v.subGroup","as":"np","cond":{"$in":["Y", "$$np.primarySubGroup.primary",]}}}, "as":"n", "in":{ "subGroupId":"$$n.subGroupId", "primarySubGroup":{"$filter":{"input":"$$n.primarySubGroup","as":"mp","cond":{"$eq":["$$mp.primary","Y"]}}} } } }` in 3.4 – s7vr Feb 22 '18 at 20:25
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/165714/discussion-between-praveen-d-and-veeram). – Praveen Dhasarathan Feb 23 '18 at 11:03
could you please help me with this below changes: https://chat.stackoverflow.com/rooms/165714/discussion-between-praveen-d-and-veeram – Praveen Dhasarathan Feb 24 '18 at 11:10
Please create a new question with the details from chat – s7vr Feb 24 '18 at 13:42

MongoDB nested array search using $map

1 Answers1

Linked