Mongodb Aggregate nested subdocuments

mongodb, mongodb-query

Solution

First you should get the totals for each 'user'. (i.e. { $group: {_id: '$user', count: {'$sum': 1} }})

Then just group by null to create a document with the result, add each user to set and push the result from first grouping onto array field. (2nd grouping)

db.test5.aggregate(
  { $unwind: "$elements" },
  { $unwind: "$elements.votes.meta" },
  { $project: {_id: '$_id', user: '$elements.votes.meta.createdBy'} },
  { $group: {_id: '$user', count: {'$sum': 1} }},
  { $group: {
        _id: null, 
        users: {$addToSet: '$_id'}, 
        occurances: {$push: {'user': '$_id', count: '$count'}}
        }
   }
)

Result:

{
    "result" : [ 
        {
            "_id" : null,
            "users" : [ 
                "545ab39ef1b0c88a695fcf8d", 
                "545aaddcf1b0c88a695fcf84", 
                "5461c0e2c9c39a192c44226c"
            ],
            "occurances" : [ 
                {
                    "user" : "5461c0e2c9c39a192c44226c",
                    "count" : 2
                }, 
                {
                    "user" : "545aaddcf1b0c88a695fcf84",
                    "count" : 2
                }, 
                {
                    "user" : "545ab39ef1b0c88a695fcf8d",
                    "count" : 2
                }
            ]
        }
    ],
    "ok" : 1
}

Problem

Having list of documents ``` [ { "__v" : 21, "_id" : ObjectId("546330dbb8926d177052e9ff"), "code" : "WfvCc", "description" : "", "elements" : [ { "_id" : ObjectId("546471f61e13b76a0b20ccaf"), "comments" : [], "meta" : { "createdBy" : "545ab39ef1b0c88a695fcf8d", "modifiedAt" : "1415868918045", "createdAt" : "1415868918045" }, "title" : "awesome title", "votes" : { "count" : 3, "meta" : [ { "createdBy" : "545ab39ef1b0c88a695fcf8d", "_id" : ObjectId("546473831e13b76a0b20ccb7"), "createdAt" : "1415869315618" }, { "createdBy" : "545aaddcf1b0c88a695fcf84", "_id" : ObjectId("546473d71e13b76a0b20ccbc"), "createdAt" : "1415869399584" }, { "createdBy" : "5461c0e2c9c39a192c44226c", "_id" : ObjectId("546474041e13b76a0b20ccbe"), "createdAt" : "1415869444056" } ] } } ] }, { "__v" : 21, "_id" : ObjectId("546330dbb8926d177052e9ff"), "code" : "WfvCc", "description" : "", "elements" : [ { "_id" : ObjectId("546471f61e13b76a0b20ccaf"), "comments" : [], "meta" : { "createdBy" : "545ab39ef1b0c88a695fcf8d", "modifiedAt" : "1415868918045", "createdAt" : "1415868918045" }, "title" : "awesome title", "votes" : { "count" : 3, "meta" : [ { "createdBy" : "545ab39ef1b0c88a695fcf8d", "_id" : ObjectId("546473831e13b76a0b20ccb7"), "createdAt" : "1415869315618" }, { "createdBy" : "545aaddcf1b0c88a695fcf84", "_id" : ObjectId("546473d71e13b76a0b20ccbc"), "createdAt" : "1415869399584" }, { "createdBy" : "5461c0e2c9c39a192c44226c", "_id" : ObjectId("546474041e13b76a0b20ccbe"), "createdAt" : "1415869444056" } ] } } ] } ] ``` I would like to aggregate the list of users aka. `elements.votes.meta.createdBy` across the documents and count the total numbers of occurrences across the document. *Note that the `elements.votes.meta.createdBy` is unique per document, so in theory this should make is simpler. So far, I've ended up with a query: ``` db.sessions.aggregate( { $project: { meta: "$elements.votes.meta" }}, { $unwind: "$meta" }, { $group: { _id: "voters", voters: { $addToSet: "$meta.createdBy" } }} ) ``` Just to get completely stuck again. I know I need a double grouping, just can't seem to be able to figure it out. Any help appreciated.

Original source