mongodb unwind array nested inside an array of documents

Question

In MongoDB, I need to be able to unwind nested an array in a document inside an array inside the main document.

{
    "_id" : ObjectId("5808d700536d1a3d69f4cf51"),
    "last_name" : "Maity",
    "xiith_mark" : 58,
    "id" : "3539488",
    "first_name" : "Harshavardhan",
    "course_name" : "BE/B.Tech",
    "institute_name_string" : "Abhayapuri College, P.O. Abhayapuri",
    "profile_percentage" : 45,
    "xiith_mark_type" : "Percentage",
    "xth_mark_type" : "Percentage",
    "date_of_birth" : "14-April-1993",
    "xth_mark" : 30,
    "last_login" : 1470827224,
    "percentage" : 55,
    "job_details" : [
        {
            "status" : NumberLong(6),
            "applied_date" : NumberLong(1470831441),
            "job_id" : NumberLong(92928),
            "contact_viwed_status" : 0,
            "label_name" : [
                "shortlisted",
                "rejected"
            ],
            "questionnaire_status" : 0,
            "batch_id" : NumberLong(6),
            "call_letter" : NumberLong(812)
        }, 
        {
            "status" : NumberLong(6),
            "applied_date" : NumberLong(1470831441),
            "job_id" : NumberLong(92928),
            "contact_viwed_status" : 0,
            "label_name" : [
                "shortlisted",
                "rejected"
            ],
            "questionnaire_status" : 0,
            "batch_id" : NumberLong(6),
            "call_letter" : NumberLong(812)
        }
    ],
    "branch_name" : "Applied Electronics",
    "candidate_state_name" : "West Bengal",
    "candidate_city_name_string" : "Kolkata",
    "10" : 10,
    "12" : 12,
    "skills" : "",
    "gender" : "Male",
    "fw_id" : "FW15884830",
    "cgpa" : 0,
    "picture_path" : "",
    "hq_passout_year" : 2019
}

Based on the record above I need to count the job labels (job_details.label_name).

I have tried the following query:

db.response.aggregate(
    {"$match":type_match},
    {"$unwind": "$job_details" }, 
    {"$group": 
      {
        "_id":"$job_details.label_name",
        "count": {"$sum": 1 }
      }
    }
])

The output is:

{
   "count": 2,
   "_id": [
   "shortlisted",
   "rejected"
    ]
}

But I want the output to be:

[
  { 
      "count": 1,  
      "_id": "shortlisted"  
  },  
  {  
      "count": 1,  
      "_id": "rejected"  
  }
]

How can I get this output?

Are you asking how to count the number of elements in the "job_details.label_name" array? It may be easier to just perform the count on the client side. — Adam Harrison
– Adam Harrison, Commented Oct 21, 2016 at 5:02
Aggregate query you posted in question is syntactically incorrect. There is no starting "[" brace but there is an ending one. — inaitgaJ
– inaitgaJ, Commented Oct 21, 2016 at 7:00
I have improved the wording and formatting in your question, to make it more readable; also I added the tag aggregation-framework. — Vince Bowdren
– Vince Bowdren, Commented Oct 24, 2016 at 16:20

Community · Accepted Answer · 2020-06-20 09:12:55Z

14

In unwind stage, field should be an array field. If not array field, it treats it as array of 1 element.

From the docs:

Changed in version 3.2: $unwind stage no longer errors on non-array operands. If the operand does not resolve to an array but is not missing, null, or an empty array, $unwind treats the operand as a single element array.

Answer to your query:

db.response.aggregate([
    {
        $project:
        {
            "job_details.label_name":1,
            _id:0
        }
    },
    {
        $unwind:"$job_details.label_name"
    },
    {
        $group:
        {
            _id:"$job_details.label_name",
            count:{$sum:1}
        }
    }
])

Refer Shell Output

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Oct 21, 2016 at 5:59

inaitgaJ

1,48612 silver badges28 bronze badges

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

8 Comments

SuFi Over a year ago

{ "result" : [ ], "ok" : 1 }

inaitgaJ Over a year ago

no problem sugi, you can add one more unwind stage on parent array.

Peter Huang Over a year ago

with mongo 3.6, this doesn't work. the result is empty

whoami - fakeFaceTrueSoul Over a year ago

Yes maybe >3.6, I'm using 4.0 where using dot notation for nested array doesn't work, results to empty array, has to do $unwind twice, which is tedious. If anyone has a solution please let us know !!

Ahmet-Salman Over a year ago

@whoami-fakeFaceTrueSoul, I know your comment is from 3 years ago, but I'm kinda in the same pickle. Is there a way now to unwind an embedded array using a single $unwind

|

Collectives™ on Stack Overflow

mongodb unwind array nested inside an array of documents

1 Answer 1

From the docs:

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

From the docs:

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related