21

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?

Improve this question
3
  • 1
    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. 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. 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. Commented Oct 24, 2016 at 16:20

1 Answer 1

Reset to default
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

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

8 Comments

{ "result" : [ ], "ok" : 1 }
no problem sugi, you can add one more unwind stage on parent array.
with mongo 3.6, this doesn't work. the result is empty
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 !!
@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
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.