Menu
I have the following sample data:
{
"name": "Bob",
"mi": "K",
"martialStatus": "M",
"age": 30,
"city": "Paris",
"job": "Engineer"
}
{
"name": "Chad",
"mi": "M",
"martialStatus": "W",
"age": 31,
"city": "Paris",
"job": "Doctor"
}
{
"name": "Mel",
"mi": "A",
"martialStatus": "D",
"age": 31,
"city": "London",
"job": "Doctor"
}
{
"name": "Frank",
"mi": "F",
"martialStatus": "S",
"age": 30,
"city": "London",
"job": "Engineer"
}
I am trying to write a mongo query that would return results in the following format: "peopleCount": 4, "jobsList": { "job": "Doctor", "ageList": [ { "age": 31, "cityList": [ { "city": "London", "people": [ { "name": "Mel", "martialStatus": "D" } ] }, { "city": "Paris", "people": [ { "name": "Chad", "martialStatus": "W" } ] }, { "city": "Berlin", ... ... ] } ] }
To try on the first two level (jobsList and ageList), I am trying the below
db.colName.aggregate([
{
$group: {
_id: { job: "$job" },
jobsList: {
$push: {
age: "$age",
city: "$city",
name: "$name",
martialStatus: "$martialStatus"
}
}
}
},
{
$group: {
_id: { age: "$age" },
ageList: {
$push: {
city: "$city",
name: "$name",
martialStatus: "$martialStatus"
}
}
}
}
]);
The above however does not work although the first group/push part works... Any hints on how to get that output format/groupping?
933
We can group by single as well as multiple field from the collection, we can use $group operator in MongoDB to group fields from the collection and returns the new document as result. We are using $avg, $sum, $max, $min, $push, $last, $first and $addToSet operator with group by in MongoDB.
The $group stage separates documents into groups according to a "group key". The output is one document for each unique group key. A group key is often a field, or group of fields. The group key can also be the result of an expression.
What is Aggregation in MongoDB? Aggregation is a way of processing a large number of documents in a collection by means of passing them through different stages. The stages make up what is known as a pipeline. The stages in a pipeline can filter, sort, group, reshape and modify documents that pass through the pipeline.
db.colName.aggregate([
{
$group: {
_id: { job: "$job", age: "$age", city: "$city" },
people: { $push: { name: "$name", martialStatus: "$martialStatus" } }
}
},
{
$group: {
_id: { job: "$_id.job", age: "$_id.age" },
peopleCount: { $sum: { $size: "$people" } },
cityList: { $push: { city: "$_id.city", people: "$people" } },
}
},
{
$group: {
_id: { job: "$_id.job" },
peopleCount: { $sum: "$peopleCount" },
agesList: { $push: { age: "$_id.age", cityList: "$cityList" } }
}
},
{
$group: {
_id: null,
peopleCount: { $sum: "$peopleCount" },
jobsList: { $push: { job: "$_id.job", agesList: "$agesList" } }
}
},
{
$project: { _id: 0, peopleCount: 1, jobsList: 1 }
}
]);
on the provided by you collection gives me the result
{
"peopleCount" : 4,
"jobsList" :
[
{
"job" : "Engineer",
"agesList" :
[
{
"age" : 30,
"cityList" :
[
{
"city" : "London",
"people" :
[
{ "name" : "Frank", "martialStatus" : "S" }
]
},
{
"city" : "Paris",
"people" :
[
{ "name" : "Bob", "martialStatus" : "M" }
]
}
]
}
]
},
{
"job" : "Doctor",
"agesList" :
[
{
"age" : 31,
"cityList" :
[
{
"city" : "London",
"people" :
[
{ "name" : "Mel", "martialStatus" : "D" }
]
},
{
"city" : "Paris",
"people" :
[
{ "name" : "Chad", "martialStatus" : "W" }
]
}
]
}
]
}
]
}
that seems to be correct. Thought, I am not sure it's the best solution. I am new to aggregation-framework.
115
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
