Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mongodb aggregation framework group by two fields

I'm querying my database using aggregation and pipeline, with two separate queries:

 $groups_q = array(
            '$group' => array(
                '_id' => '$group_name',
                'total_sum' => array('$sum' => 1)
                )
            );

  $statuses_q = array(
            '$group' => array(
                '_id' => '$user_status',
                'total_sum' => array('$sum' => 1)
                )
            );

$data['statuses'] = $this->mongo_db->aggregate('users',$statuses_q);
$data['groups'] = $this->mongo_db->aggregate('users',$groups_q);

And I'm getting what I want:

Array
(
[statuses] => Array
    (
        [result] => Array
            (
                [0] => Array
                    (
                        [_id] => Inactive
                        [total_sum] => 2
                    )

                [1] => Array
                    (
                        [_id] => Active
                        [total_sum] => 5
                    )

            )

        [ok] => 1
    )

[groups] => Array
    (
        [result] => Array
            (
                [0] => Array
                    (
                        [_id] => Accounting 
                        [total_sum] => 1
                    )

                [1] => Array
                    (
                        [_id] => Administrator
                        [total_sum] => 2
                    )

                [2] => Array
                    (
                        [_id] => Rep
                        [total_sum] => 1
                    )
            )

        [ok] => 1
    )

)

I don't want to query my database twice. Is there is a better way to do it? How can I accomplish it with one query? Should I use $project operator?

like image 918
castt Avatar asked Mar 29 '13 03:03

castt


People also ask

How do I group multiple fields in MongoDB?

For the implementation of the phenomenon of groups according to multiple fields, we need to have some data in the database. We will create a database first. This is done by declaring the name of the database with the keyword “use.” For this implementation, we are using a database “demo.”

Can we use multiple group in MongoDB?

Mongodb group by multiple fields using Aggregate operation First, the key on which the grouping is based is selected and then the collection is divided into groups according to the selected key value. You can then create a final document by aggregating the documents in each group.

WHAT IS group in MongoDB aggregation?

Group operator (also known as an accumulator operator) is a crucial operator in the MongoDB language, as it helps to perform various transformations of data. It is a part of aggregation in MongoDB. MongoDB is an open-source NoSQL database management program. NoSQL is an alternative to traditional relational databases.

Which aggregation method is preferred for use by MongoDB?

The pipeline provides efficient data aggregation using native operations within MongoDB, and is the preferred method for data aggregation in MongoDB. The aggregation pipeline can operate on a sharded collection.


1 Answers

You can't use a single aggregate() to do two grouped counts with your desired result format. Once the data has been grouped the first time you no longer have the details needed to create the second count.

The straightforward approach is to do two queries, as you are already doing ;-).

Thoughts on alternatives

If you really wanted to get the information in one aggregation query you could group on both fields and then do some manipulation in your application code. With two fields in the group _id, results are going to be every combination of group_name and status.

Example using the mongo shell :

db.users.aggregate(
    { $group: {
         _id: { group_name: "$group_name", status: "$status" },
         'total_sum': { $sum: 1 }
    }}
)

That doesn't seem particularly efficient and lends itself to some convoluted application code because you have to iterate the results twice to get the expected groupings.

If you only wanted the unique names for each group instead of the names + counts, you could use $addToSet in a single group.

The other obvious alternative would be to do the grouping in your application code. Do a single find() projecting only the group_name and status fields, and build up your count arrays as you iterate the results.

like image 68
Stennie Avatar answered Sep 28 '22 09:09

Stennie