CODEWITHSUNDEEP
mongodbmongodb-aggregation
mobius
mobius

Reputation: 5174

Multiple groups in mongodb aggregate pipeline

I have a collection which contains data like:

{
  attribute: 'value',
  date: ISODate("2016-09-20T18:51:05Z")
}

and I want to group by attribute to get a total count per attribute and at the same time group by attribute and $hour to get a count per attribute and hour.

I know I can do something like:

{
  $group: {
    _id: '$attribute'
    count: {
      $sum: 1
    }
  }
}

and

{
  $group: {
    _id: {
      attribute : '$attribute',
      hour: {
        '$hour' : '$date'
      }
    },
    count: {
      $sum: 1
    }
  }
},
{
  $group: {
    _id: {
      attribute: '$_id.attribute'
    },
    hours: {
      '$push': {
        hour: '$_id.hour',
        count: '$count'
      }
    }
  }
}

to get both results in two separate aggregations, but is there a way to get the result I want in one query?

Edit as requested:

Perhaps this is utterly wrong but ideally I would like to get a response like:

{
  _id: "attribute1",
  total: 20, // Total number of attribute1 documents

  // And the breakdown of those 20 documents per hour
  hours: [{
    hour: 10,
    count: 8
  },
  {
    hour: 14,
    count: 12
  }]
}

However the whole point of the question is whether this can be done (and how) on one query in mongodb. Not in two queries that would be merged on the application layer

Upvotes: 1

Views: 1653

Answers (1)

Ori Popowski
Ori Popowski

Reputation: 10662

You can first group by attribute-hour pairs and their corresponding occurrences.
Then, you project the attribute, a pair of hour-count, and a copy of that count.
Finally, you group by the attribute alone, $push the pairs, and sum the counts.

[
    {
        $group: {
            _id: {
                attribute: "$attribute",
                hour: { "$hour": "$date" }
            },
            count: {
                $sum: 1
            }
        }
    },
    {
        $project: {
            attribute: "$_id.attribute",
            pair: { hour: "$_id.hour", count: "$count" },
            count: "$count",
            _id: 0
        }
    },
    {
        $group: {
            _id: "$attribute",
            hours: {
                $push: "$pair"
            },
            total: {
                $sum: "$count"
            }
        }
    }
]

Upvotes: 1

Related Questions