How to group and a count fields in a mongoDB collection

user_1444101 0
Viewed 39

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.

mongodb-query mongodb
2 Answers

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 } }
])
user_30272660

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.

user_14093740
Related