Getting aggregate count for nested fields in mongo

Question

I have a mongo collection mimicking this java class. A student can be taught a number of subjects across campus.

 class Students {
     String studentName;
     Map<String,List<String>> subjectsByCampus;
}

So a structure will look like this

{
 _id: ObjectId("someId"),
 studentName:'student1',
subjectByCampusName:{
 campus1:['subject1','subject2'],
 campus2: ['subject3']
},
_class: 'fqnOfTheEntity'
}

I want to find the count of subjects offered by each campus or be able to query the count of subjects offered by a specific campus. Is there a way to get it through query?

I have both requirement.. but as of now , need for a student — Raman
– Raman, Commented Dec 9, 2022 at 17:06
I don't know if your schema is going to easily support either. For a given student - you can use the $size operator, but you'll need to either enumerate each campus# field manually or jump through some hoops with $objectToArray. And if you're trying to figure out the data for the entire collection, the database is going to have to process every single students document which will not scale well at all. — user20042973
– user20042973, Commented Dec 9, 2022 at 17:13
taking my code snippet, I want the output to be something like campus1:2, campus2:1. I am trying to write a query but not finding a way — Raman
– Raman, Commented Dec 9, 2022 at 18:01
based on @user20042973 answer you maybe looking for this: mongoplayground.net/p/adjX0QNkEK6 — R2D2
– R2D2, Commented Dec 9, 2022 at 20:02

user20042973 · Accepted Answer · 2022-12-09 19:34:47Z

1

Mentioned in the comments, but the schema here does not appear to be particularly well-suited toward gathering the data requested in the question. For an individual student, this is doable via $size and processing the object (as an array) via $map. For example, if we want your desired out put of campus1:2, campus2:1 for the sample document provided, a pipeline to produce that in a countsByCampus field might look as follows:

[
  {
    "$addFields": {
      "countsByCampus": {
        "$arrayToObject": {
          "$map": {
            "input": {
              "$objectToArray": "$subjectByCampusName"
            },
            "in": {
              "$mergeObjects": [
                "$$this",
                {
                  v: {
                    $size: "$$this.v"
                  }
                }
              ]
            }
          }
        }
      }
    }
  }
]

Playground demonstration here with an output document of:

  {
    "_class": "fqnOfTheEntity",
    "_id": "someId",
    "countsByCampus": {
      "campus1": 2,
      "campus2": 1
    },
    "studentName": "student1",
    "subjectByCampusName": {
      "campus1": [
        "subject1",
        "subject2"
      ],
      "campus2": [
        "subject3"
      ]
    }
  }

Doing that across the entire collection would involve $grouping the results together. This can be done but would be an extremely resource-intensive and likely slow operation.

answered Dec 9, 2022 at 19:34

user20042973

5,2202 gold badges6 silver badges17 bronze badges

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

1 Comment

Raman Over a year ago

this looks too complex to be honest.. but thanks for providing the details

Collectives™ on Stack Overflow

Getting aggregate count for nested fields in mongo

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related