Mongoose: Aggregate query in a nested array structure

Question

I'm getting a little issue with a Mongo DB query.

I have a collection called "Answers" with the following structure

{
  "_id": ObjectId("560acfcb904a29446a6d2617"),
  "path_id": ObjectId("560acae1904a29446a6d2610"),
  "step_id": "kids",
  "user_id": ObjectId("559ae27684ff12e88d194eb7"),
  "answers": [
    {
      "id": "kids_q",
      "question": "Do you have kids?",
      "answer": "No"
    }
  ]
}

As you can see answers is an array and it can have one or many objects, but always is an array.

First, I want to get the total of answers in a step_id

I get that using the following query using aggregate

Answer.aggregate( {
        $match: {
            'path_id': {
                $eq: path_id
            },
            'step_id': {
                $eq: step_id
            },
            ''

        }
    }, {
        $group: {
            _id: {
                step_id: '$step_id'
            },
            count: {
                $sum: 1
            }
        }
    }, function( err, results ) {
        if ( err ) {
            deferred.reject( err );
        }
        deferred.resolve( results );
    } );

That works great.

Second, I want to get how many of that answers match against the question and the answer.

Let's use the Do you have kids? question as example, I want to know how many answers are Yes, running a query in the command line I get the correct result:

db.answers.find( {
    path_id: ObjectId( '560acae1904a29446a6d2610' ),
    'answers.0.question': 'Do you have kids?',
    'answers.0.answer': 'Yes'
} )

I want to translate that query into an aggregate query using mongoose and avoid to have hard coded the array answers.0.question because that answer can be stored in a random index, maybe in the index 1, maybe in the index 7.

Any help is appreciated.

Thanks

Answer 1

Really not sure if .aggregate() is really what you want for any of this. If I understand correctly you have these documents in your collection that has an array of answers to questions and that of course those answers are not in any set position within the array. But it also does not seem likely that any one document has more than one of the same answer type.

So it seems to me is that all you really want is an $elemMatch on the array element values and determine the count of documents that contain it:

Answer.count({
    "path_id": "560acae1904a29446a6d2610",
    "answers": { 
        "$elemMatch": { 
            "question": "Do you have kids?",
            "answer": "Yes"
        }
    }
},function(err,count) {

});

The $elemMatch operator applies all of it's conditions just like another query to each element of the array. So multiple conditions of an "and" need to be met on the same element for it to be valid. No need to do this by index.

If you wanted something broader, and then only really if it was possible for each document to contain more than one possible match in the array for those conditions, then you would use .aggregate() with a condition to filter and count the matches within the array.

Answer.aggregate(
    [
       { "$match": {
           "answers": { 
               "$elemMatch": { 
                   "question": "Do you have kids?",
                   "answer": "Yes"
               }
           }
       }},
       { "$unwind": "$answers" },
       { "$match": {
           "answers.question": "Do you have kids?",
           "answers.answer": "Yes"
       }},
       { "$group": {
           "_id": "$path_id",
           "count": { "$sum": 1 }
       }}
    ],
    function(err,results) {

    }
);

But I'd only be doing something like that if indeed you had multiple possible matches in the array and you needed multiple keys to group on within the results.

So if it's just about matching documents that happen to have those details in one array entry, then just use $elemMatch for the query, and at most then just $group on the count for given keys and don't bother with filtering the array content via $unwind.

Answer.aggregate(
    [
       { "$match": {
           "answers": { 
               "$elemMatch": { 
                   "question": "Do you have kids?",
                   "answer": "Yes"
               }
           }
       }},
       { "$group": {
           "_id": "$path_id",
           "count": { "$sum": 1 }
       }}
    ],
    function(err,results) {

    }
);

So if there is really only one possible match within the array, then just count the documents instead

Answer 2

Use $unwind and then $match to filter only answers to the question you are looking for:

var steps = [
  {
    $match: {
      'path_id': ObjectId("560acae1904a29446a6d2610"),
      'step_id': 'kids'
    }
  },
  { $unwind : "$answers" },
  {
    $match: {
      "answers.question": 'Do you have kids?'
    }
  },
  {
    $group: {
        _id: '$answers.answer',
        count: {
            $sum: 1
        }
      }
    }
  ];
Answer.aggregate(steps, function( err, results ) {
        //do whatever you want with the results
} );