Mongoose aggregate issue with grouping by nested field

Question

I have a schema that stores user attendance for events:

event: 
  _id: ObjectId,
  ...
  attendances: [{
    user: {
      type: ObjectId,
      ref: 'User'
    },
    answer: {
      type: String,
      enum: ['yes', 'no', 'maybe']
    }
  }]
}

Sample data:

_id: '533483aecb41af00009a94c3',
attendances: [{
  user: '531770ea14d1f0d0ec42ae57',
  answer: 'yes',
}, {
  user: '53177a2114d1f0d0ec42ae63',
  answer: 'maybe',
}],

I would like to return this data in the following format when I query for all attendances for a user:

var attendances = {
  yes: ['533497dfcb41af00009a94d8'], // These are the event IDs
  no: [],
  maybe: ['533497dfcb41af00009a94d6', '533497dfcb41af00009a94d2']
}

I am not sure the aggegation pipeline will return it in this format? So I was thinking I could return this and modify it easily:

var attendances = [
  answer: 'yes',
  ids: ['533497dfcb41af00009a94d8'],
},{
  answer: 'no',
  ids: ['533497dfcb41af00009a94d8']
}, {
  answer: 'maybe',
  ids:  ['533497dfcb41af00009a94d6', '533497dfcb41af00009a94d2']
}];

My attempt is not succesful however. It doesn't group it by answer:

this.aggregate({
  $match: {
    'attendances.user': someUserId
}
}, {
  $project: {
    answer: '$attendances.answer' 
  }
}, {
  $group: {
    _id: '$answer',
    ids: {
      $addToSet: "$_id"
    }
  }
}, function(e, docs) {

});

Can I return the data I need in the first desired format and if not how can I correct the above code to achieve the desired result?

On that note - perhaps the map-reduce process would be better suited?

Can you post a sample document from the collection (in addition to the schema that you have posted already)? — Anand Jayabalan
– Anand Jayabalan, Commented Jul 30, 2014 at 21:41

Anand Jayabalan · Accepted Answer · 2014-07-30 22:04:10Z

1

The below query will help you get close to the answer you want. Although, it isn't exactly in the same format you are expecting, you get separate documents for each answer option and an array of event id's.

db.collection.aggregate([
    // Unwind the 'attendances' array
    {"$unwind" : "$attendances"}, 
    // Match for user
    {"$match" : {"attendances.user" : "53177a2114d1f0d0ec42ae63"}}, 
    // Group by answer and push the event id's to an array
    {"$group" : {_id : "$attendances.answer", eventids : {$push : "$_id"}}}
])

This produces the below output:

{
        "result" : [
                {
                        "_id" : "yes",
                        "eventids" : [
                                ObjectId("53d968a8c4840ac54443a9d6")
                        ]
                },
                {
                        "_id" : "maybe",
                        "eventids" : [
                                ObjectId("53d968a8c4840ac54443a9d7"),
                                ObjectId("53d968a8c4840ac54443a9d8")
                        ]
                }
        ],
        "ok" : 1
}

answered Jul 30, 2014 at 22:04

Anand Jayabalan

13.1k6 gold badges44 silver badges54 bronze badges

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

1 Comment

cyberwombat Over a year ago

That works - however I must specify new ObjectId(...) for the $match field. Any idea why it would not convert it from a string?

Collectives™ on Stack Overflow

Mongoose aggregate issue with grouping by nested field

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