MongoDB Aggregate to get average from document and of array elements?

MongoDBDatabaseBig Data Analytics

For this, use $avg along with $group and aggregate(). Let us create a collection with documents −

> db.demo598.insertOne(
...    {
...       Information:'Student',
...       id:100,
...       details:[
...          {Name:'Chris',Marks:75},
...          {Name:'Bob',Marks:55}
...       ]
...    }
... );
{
   "acknowledged" : true,
   "insertedId" : ObjectId("5e947fccf5f1e70e134e2694")
}
> db.demo598.insertOne(
...    {
...       Information:'Student',
...       id:101,
...       details:[
...          {Name:'Chris',Marks:75},
...          {Name:'Bob',Marks:45}
...       ]
...    }
... );
{
   "acknowledged" : true,
   "insertedId" : ObjectId("5e947fcdf5f1e70e134e2695")
}

Display all documents from a collection with the help of find() method −

> db.demo598.find();

This will produce the following output −

{ "_id" : ObjectId("5e947fccf5f1e70e134e2694"), "Information" : "Student", "id" : 100, "details" : [
   { "Name" : "Chris", "Marks" : 75 },
   { "Name" : "Bob", "Marks" : 55 }
] }
{ "_id" : ObjectId("5e947fcdf5f1e70e134e2695"), "Information" : "Student", "id" : 101, "details" : [
   { "Name" : "Chris", "Marks" : 75 },
   { "Name" : "Bob", "Marks" : 45 }
] }

Following is the query to get average from the document and array elements −

> db.demo598.aggregate([
...
...    { "$group": {
...       "_id": "Information",
...       "id": { "$avg": "$id" },
...       "details": { "$push": "$details" }
...    }},
...    { "$unwind": "$details" },
...    { "$unwind": "$details" },
...    { "$group": {
...       "_id": { "Information": "$_id", "Name": "$details.Name" },
...       "id": { "$avg": "$id" },
...       "AvgValue": { "$avg": "$details.Marks" }
...    }},
...    { "$sort": { "_id": 1 } },
...    { "$group": {
...       "_id": "$_id.Information",
...       "id": { "$avg": "$id" },
...       "details": { "$push": {
...          "Name": "$_id.Name",
...          "MarksAvg": "$AvgValue"
...       }}
...    }}
... ]).pretty();

This will produce the following output −

{
   "_id" : "Information",
   "id" : 100.5,
   "details" : [
      {
         "Name" : "Bob",
         "MarksAvg" : 50
      },
      {
         "Name" : "Chris",
         "MarksAvg" : 75
      }
   ]
}
raja
Published on 15-May-2020 07:04:58
Advertisements