mongo group query how to keep fields
MongodbMongodb Problem Overview
Everybody. In mongo group query, the result shows only the key(s) in arguments. How to keep the first document in each group like mysql query group. for example:
-------------------------------------------------------------------------
| name | age | sex | province | city | area | address |
-------------------------------------------------------------------------
| ddl1st | 22 | 纯爷们 | BeiJing | BeiJing | ChaoYang | QingNianLu |
| ddl1st | 24 | 纯爷们 | BeiJing | BeiJing | XuHui | ZhaoJiaBangLu |
| 24k | 220 | ... | .... | ... | ... | ... |
-------------------------------------------------------------------------
db.users.group({key: { name: 1},reduce: function ( curr, result ) { result.count ++ },initial: {count : 0 } })
result:
[{ "name" : "ddl1st", "count" : 1},{ "name" : "24k", "count" : 1}]
How to get the following:
[ { "name" : "ddl1st", "age" : 22, "sex" : "纯爷们", "province" : "BeiJing", "city" : "BeiJing", "area" : "ChaoYang", "address" : "QingNianLu", "count" : 1 }, { "name" : "24k", "age" : 220, "sex" : "...", "province" : "...", "city" : "...", "area" : "...", "address" : "...", "count" : 1}]
Mongodb Solutions
Solution 1 - Mongodb
If you want to keep the information about the first matching entries for each group, you can try aggregating like:
db.test.aggregate([{
$group: {
_id : '$name',
name : { $first: '$name' },
age : { $first: '$age' },
sex : { $first: '$sex' },
province : { $first: '$province' },
city : { $first: '$city' },
area : { $first: '$area' },
address : { $first: '$address' },
count : { $sum: 1 },
}
}]);
Solution 2 - Mongodb
[edited to include comment suggestions]
I came here looking for an answer but wasn't happy with the selected answer (especially given it's age). I found this answer that is a better solution (adapted):
db.test.aggregate({
$group: {
_id: '$name',
person: { "$first": "$$ROOT" },
count: { $sum: 1 }
},
{
"$replaceRoot": { "newRoot": { "$mergeObjects": ["$person", { count: "$count" }]} }
}
}
Solution 3 - Mongodb
By the way, if you want to keep not only the first document, you can use$addToSet For example:
db.test.aggregate({
$group: {
_id: '$name',
name : { $addToSet: '$name' }
age : { $addToSet: '$age' },
count: { $sum: 1 }
}
}
Solution 4 - Mongodb
You can try out this
db.test.aggregate({
{ $group:
{ _id: '$name',count: { $sum: 1 }, data: { $push: '$$ROOT' } } },
{
$project: {
_id:0,
data:1,
count :1
}
}
}
Solution 5 - Mongodb
Use $first
with the $$ROOT
document and then use $replaceRoot
with the first field.
db.test.aggregate([
{ "$group": {
"_id": "$name",
"doc": { "$first": "$$ROOT" }
}},
{ "$replaceRoot": { "newRoot": "$doc" }}
])
Solution 6 - Mongodb
Just a quick update if one faces the same issue with documents with numerous fields.
One can use the power of combining the $replaceRoot
pipeline stage and the $mergeObjects
pipeline operator.
db.users.aggregate([
{
$group: {
_id: '$name',
user: { $first: '$$ROOT' },
count: { $sum: 1 }
},
},
{
$replaceRoot: {
newRoot: { $mergeObjects: [{ count: '$count' }, '$user'] }
}
}
])
Solution 7 - Mongodb
This is what i did, it works fine.
db.person.aggregate([
{
$group: { _id: '$name'}, // pass the set of field to be grouped
age : { $first: '$age' }, // retain remaining field
count: { $sum: 1 } // count based on your group
},
{
$project:{
name:"$_id.name",
age: "$age",
count: "$count",
_id:0
}
}])
Solution 8 - Mongodb
I didn't know about .group
helper, but if you prefer to go with the Aggregation Framework, then you'll have to specify which fields to return. Correct me if I'm wrong, but in SQL you would have to do that anyway.
Well, this is how you would do it with the Aggregation Framework mentioned before:
db.test.aggregate({
$group: {
_id: { name: "$name", city: "$city", fieldName: "$fieldName" },
count: { $sum: 1 }
}
})
Solution 9 - Mongodb
I created this function to generalise reversing an unwind stage... let me know if you guys come across any bugs with it, but it's working well for me!
const createReverseUnwindStages = unwoundField => {
const stages = [
//
// Group by the unwound field, pushing each unwound value into an array,
//
// Store the data from the first unwound document
// (which should all be the same apart from the unwound field)
// on a field called data.
// This is important, since otherwise we have to specify every field we want to keep individually.
//
{
$group: {
_id: '$_id',
data: {$first: '$$ROOT'},
[unwoundField]: {$push: `$${unwoundField}`},
},
},
//
// Copy the array of unwound fields resulting from the group into the data object,
// overwriting the singular unwound value
//
{
$addFields: {[`data.${unwoundField}`]: `$${unwoundField}`},
},
//
// Replace the root with our data object
//
{
$replaceRoot: {
newRoot: '$data',
},
},
]
return stages
}
Solution 10 - Mongodb
If you want to project all fields document this use below query.
db.persons.aggregate({
{ $group: { _id: '$name', data: { $push: '$$ROOT' }, total: { $sum: 1 }} },
{
$project: {
_id:0,
data:1,
total :1
}
}
}
Solution 11 - Mongodb
I like to put everything that is going to be used with the $first option into a dictionary to extract from at the end.
{'$set':
{'collection_name':
'collection_item1': '$collection_item1',
'collection_item2': '$collection_item2',
...
}
}
Now, just copy the dictionary and you no longer have to lug around all that information 1 at a time!
{'$group':
'_id': ['$id'],
'collection_name': {'$first': '$collection_name'}
}
Solution 12 - Mongodb
Here is the answer >>>>
$m = new \MongoDB\Driver\Manager();
$command = new \MongoDB\Driver\Command([
'aggregate' => 'mytestusers',
'pipeline' => [
['$match' => ['name' => 'Pankaj Choudhary']],
['$unwind'=>'$skills'],
['$lookup' => array('from'=>'mytestskills','localField'=>'skills','foreignField'=>'_id','as'=>'sdfg')],
['$unwind'=>'$sdfg'],
['$group'=>array('_id'=>array('_id'=>'$_id','name'=>'$name','email'=>'$email'),'skills'=>array('$push'=>'$skills'),'sdfg'=>array('$push'=>'$sdfg'))],
],
'cursor' => new \stdClass,
]);
$cursor = $m->executeCommand('targetjob-plus', $command);
$result = $cursor->toArray();