Using aggregate on documents that contain arrays?

Question

I have a collection that contains documents with a structure like below:

{
   answers: [
      {
          Title: 'What's your age?',
          values: [
                      {value: true, label: '18-30'}
                  ]
      },
      {
          Title: 'What's your favorite color(s)?',
          values: [
                      {value: true, label: 'Red'},
                      {value: true, label: 'Green'}
                  ]
      }
   ]
},
{
   answers: [
      {
          Title: 'What's your age?',
          values: [
                      {value: true, label: '31-40'}
                  ]
      },
      {
          Title: 'What's your favorite color(s)?',
          values: [
                      {value: true, label: 'Red'}
                  ]
      }
   ]
}

I'd like to get the counts for the various answers. So for the above data I'd like to get something like:

{
   {
      Title: "What's your age?",
      AgeTotals: {
        "18-30": 1,
        "31-40": 1
      }
   },
   {
      Title: "What's your favorite color(s)?",
      ColorTotals: {
        "Red": 2,
        "Green": 1
      }
   }
}

My first thought was to do something like this:

db.test.aggregate(
    {$unwind: "$answers"},
    {$unwind: "$answers.values"},
    {$group:{
        _id: "$answers.values.label",
        totals: {$sum: 1}
        }
    }
);

But this generates the output:

{ "_id" : "31-40", "totals" : 1 }
{ "_id" : "Red", "totals" : 2 }
{ "_id" : "Green", "totals" : 1 }
{ "_id" : "18-30", "totals" : 1 }

How would I go about getting the totals for each answer rather than the above?

UPDATE:

Alternatively I would be ok if the data were output in the following format:

{ "title: "What's your age?", "_id" : "31-40", "totals" : 1 }
{ "title": "What's your favorite color?", "_id" : "Red", "totals" : 2 }
{ "title": "What's your favorite color?", "_id" : "Green", "totals" : 1 }
{ "title: "What's your age?", "_id" : "18-30", "totals" : 1 }

BatScream · Accepted Answer · 2014-12-24 19:44:00Z

You got pretty close.

You need to add answers.Title to the first group _id, to get it listed on the document.
Group the records once again based on the answer title, and accumulate the counts for various labels using the $push operator.

Updated Code:

db.test.aggregate([
    {$unwind: "$answers"},
    {$unwind: "$answers.values"},
    {$group:{"_id": {"answer":"$answers.Title",
                     "label":"$answers.values.label"},
              "totals": {$sum: 1}}
    },
    {$group:{"_id":"$_id.answer",
             "totals":{$push:{"label":"$_id.label",
                              "count":"$totals"}}}} 
]);

Sample o/p:

{
        "_id" : "What's your favorite color(s)?",
        "totals" : [
                {
                        "label" : "Green",
                        "count" : 1
                },
                {
                        "label" : "Red",
                        "count" : 2
                }
        ]
}
{
        "_id" : "What's your age?",
        "totals" : [
                {
                        "label" : "31-40",
                        "count" : 1
                },
                {
                        "label" : "18-30",
                        "count" : 1
                }
        ]
}

When you say, you want to get the below output:

{
   {
      Title: "What's your age?",
      AgeTotals: {
        "18-30": 1,
        "31-40": 1
      }
   },
   {
      Title: "What's your favorite color(s)?",
      ColorTotals: {
        "Red": 2,
        "Green": 1
      }
   }
}

You notice that the output contains a value field as its key. (The value of the field label - "Red", is a key.). The Aggregation framework has limits, one of its limits is that a value from a field cannot be transformed into a key in the output document. To achieve that you need to perform map-reduce.

Chris Seymour · Accepted Answer · 2014-12-24 19:46:33Z

For your alternative output you could do:

db.test.aggregate([
{"$unwind": "$answers"},
{"$group": {'_id': {"title": "$answers.Title", "value": "$answers.values.label" }}},
{"$unwind": "$_id.value"},
{"$group": {"_id": {"title": "$_id.title", "value": "$_id.value"}, "total": {"$sum": 1}}},
{"$project": {"title": "$_id.title", "value": "$_id.value", "total": 1, "_id": 0}}])

Which produces:

{
    "result" : [ 
        {
            "total" : 1,
            "title" : "What's your age?",
            "value" : "18-30"
        }, 
        {
            "total" : 1,
            "title" : "What's your favorite color(s)?",
            "value" : "Green"
        }, 
        {
            "total" : 2,
            "title" : "What's your favorite color(s)?",
            "value" : "Red"
        }, 
        {
            "total" : 1,
            "title" : "What's your age?",
            "value" : "31-40"
        }
    ],
    "ok" : 1
}

This output has the benefit of a consistence structure where only meta-data is stored as keys.

Collectives™ on Stack Overflow

Using aggregate on documents that contain arrays?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related