I have the following Mongodb document.
{
"_id" : ObjectId("62406bfaa1d66f8d99c6e97d"),
"skill": "Programming Language"
"supply" : [
{
"employeeName" : "A1",
"skillRating" : 3
},
{
"employeeName" : "A2",
"skillRating" : 4
},
{
"employeeName" : "A3",
"skillRating" : 4
},
{
"employeeName" : "A4",
"skillRating" : 4
},
{
"employeeName" : "A5",
"skillRating" : 3
},
{
"employeeName" : "A6",
"skillRating" : 4
},
{
"employeeName" : "A7",
"skillRating" : 2
},
{
"employeeName" : "A8",
"skillRating" : 2
},
{
"employeeName" : "A9",
"skillRating" : 4
},
{
"employeeName" : "A10",
"skillRating" : 3
},
{
"employeeName" : "A11",
"skillRating" : 3
},
{
"employeeName" : "A12",
"skillRating" : 3
},
{
"employeeName" : "A13",
"skillRating" : 2
},
{
"employeeName" : "A14",
"skillRating" : 4
},
{
"employeeName" : "A15",
"skillRating" : 4
}
]
}
How can I write a Mongodb query to produce the following output (i.e.: Get the count of occurrence of each value for a matching skill)
{
skillName : "Programming Language",
skillRating1: 0, <-- Count of skillRating with value 1
skillRating2: 3, <-- Count of skillRating with value 2
skillRating3: 5, <-- Count of skillRating with value 3
skillRating4: 7, <-- Count of skillRating with value 4
skillRating5: 0 <-- Count of skillRating with value 5
}
[Note: I am learning to write Mongodb queries]
3
Answers
Maybe something like this:
Explained:
playground
You can go with aggregation,
$unwind
to deconstruct the array$group
to get the sum of avg by _id and the avg$arrayToObject
to make the field to object with the help of$concat
. Because we need the skillRating1,skillRating2…$replaceRoot
to get the object to root document$project
to decide whether to show or notHere is the code,
Working Mongo playground
Here’s another version that also reports
skillRating
s with a zero count. This aggregation pipeline is essentially identical to @varman’s answer and adds a complex (to me anyway)"$set"
/"$map"
to create the extra fields.Try it mongoplayground.net.