group by and union in Mongo DB

[Srinivas Pokala]

Hi,

Below code worked for sql query:  "select mid,yid from table where mid=1234 or yid=1234"


Code:

$session_query = array('$or'=> array(
                                    array("mid"=>"1234"),
                                    array("yid"=>"1234")
                                    ));
                                   
    $sessionCollections= $db->chat->find($session_query)->sort(array('date'=>1))->skip("0")->limit("10");

How to write 'group by' and 'union' operations in Mongo DB?

group by:

SELECT mid, yid FROM table WHERE (mid =1) OR (yid =1) GROUP BY mid, yid

Union:

(select distinct mid from table where yid=1 ) union (select distinct yid from table where mid=1)

Please help me.

Thanks
Srinivas

[Linda Qin]

Hi Srinivas,

From MongoDB version 2.2, you can use the $group operator in the aggregation framework to group documents.

The aggregation command for the "GROUP BY" query is as below. You would need to update the code to run the same function using the driver.

{code}

db.collection.aggregate([

{ $match : { $or : [ {mid : 1}, {yid : 1} ] }},

{ $group : { _id : { mid : "$mid", yid : "$yid" } }},

{ $project:{ _id : 0, mid : "$_id.mid", yid : "$_id.yid" }}

])

{code}

From MongoDB version 2.5.2, you can use the $setUnion operator in the aggregation framework to provide unions of data sets. 

The aggregation command for the UNION query is as below:

{code}

db.collection.aggregate([

{ $match : { $or : [ {mid:1},{yid:1} ] } },

{ $group : { _id : null, mids : { $addToSet : "$mid" }, yids : { $addToSet : "$yid" }}},

{ $project : { _id : 0, mids_yids : { $setUnion : ["$yids", "$mids"]}}}

])

{code}

Please note that MongoDB 2.5 release is a development release for the production 2.6 release. Currently the latest version is 2.6 release candidate 2.6.0-rc0.

Thanks,

Linda