I'm really new to mongodb coming from a sql background and struggling to work out how to run a simple report that will group a value from a nested document with a count and in a sort order with highest count first.
I've tried so many ways from what I've found online but I'm unable to target the exact field that I need for the grouping.
Here is the collection.
{
"_id": {
"$oid": "6005f95dbad14c0308f9af7e"
},
"title": "test",
"fields": {
"6001bd300b363863606a815e": {
"field": {
"_id": {
"$oid": "6001bd300b363863606a815e"
},
"title": "Title Two",
"datatype": "string"
},
"section": "Section 1",
},
"6001bd300b363863423a815e": {
"field": {
"_id": {
"$oid": "6001bd3032453453606a815e"
},
"title": "Title One",
"datatype": "string"
},
"section": "Section 1",
},
"6001bd30453534863423a815e": {
"field": {
"_id": {
"$oid": "6001bd300dfgdfgdf06a815e"
},
"title": "Title One",
"datatype": "string"
},
"section": "Section 1",
}
},
"sections": ["Section 1"]
}
The result I need to get from the above example would be:
"Title One", 2
"Title Two", 1
Can anyone please point me in the right direction? Thank you so much.
Having dynamic field names is usually a poor design.
Try this one:
db.collection.aggregate([
{ $set: { fields: { $objectToArray: "$fields" } } },
{ $unwind: "$fields" },
{ $group: { _id: "$fields.v.field.title", count: { $count: {} } } },
{ $sort: { count: -1 } }
])
Here's another way to do it. The $project throws away everything except for the deep-dive to "title". Then just $unwind and $sortByCount.
db.collection.aggregate([
{
"$project": {
"titles": {
"$map": {
"input": {
"$objectToArray": "$fields"
},
"in": "$$this.v.field.title"
}
}
}
},
{
"$unwind": "$titles"
},
{
"$sortByCount": "$titles"
}
])
Try it on mongoplayground.net.