Aggregation: Grouping based on array element in MongoDB

Question

I am new to MongoDB, trying to write an aggregation function such that my output for the input should be same as below

[
  {
    "_id": {
      "month": 1,
      "year": 2022
    },
    "childServices": [
       {"service":"MCT Latency", "sli":99.9},
       {"service":"MCT Packet Loss", "sli":99.9}
      ],
    "service": "Network"
  },
  {
    "_id": {
      "month": 2,
      "year": 2022
    },
    "childServices": [
       {"service":"MCT Latency", "sli":98.9},
       {"service":"MCT Packet Loss", "sli":99.9}
      ]
    "service": "Network",
  }
]

Tried with below, but it's not grouping each childService by date.

[{
 $unwind: {
  path: '$childServices'
 }
}, {
 $group: {
  _id: {
   month: {
    $month: '$date'
   },
   year: {
    $year: '$date'
   }
  },
  service: {
   $first: '$service'
  },
  childServices: {
   $first: '$childServices.service'
  },
  sli: {
   $avg: '$childServices.availability'
  }
 }
}, {
 $sort: {
  '_id.month': 1,
  '_id.year': 1
 }
}]

SAMPLE DATA

[{
  "_id": {
    "$oid": "62fc99c00f5b1cb61d5f1072"
  },
  "service": "Network",
  "date": "01/02/2022 00:32:51",
  "childServices": [
    {
      "service": "MCT Latency",
      "availability": 99.9,
    },
    {
      "service": "MCT Packet Loss",
      "availability": 99.9,
    }
},
{
  "_id": {
    "$oid": "62fc99df0f5b1cb61d5f1073"
  },
  "service": "Network",
  "date": "02/02/2022 00:32:51",
  "childServices": [
    {
      "service": "MCT Latency",
      "availability": 98.3,
   },
      "service": "MCT Packet Loss",
      "availability": 99.9,
   }
 }
]

Basically, I want to get into the childService > pick each service > group them by month+year and get their monthly avg.

Charchit Kapoor · Accepted Answer · 2022-08-23 14:43:13Z

1

Convert the date from a string to a date type, before grouping, like this:

db.collection.aggregate([
  {
    $unwind: {
      path: "$childServices"
    }
  },
  {
    $addFields: {
      date: {
        "$toDate": "$date"
      }
    }
  },
  {
    $group: { <---- Here we are grouping the data for each distinct combination of month, year and child service. This needs to be done because we are using $first accumulator
      _id: {
        month: {
          $month: "$date"
        },
        year: {
          $year: "$date"
        },
        service: "$childServices.service"
      },
      service: {
        $first: "$service"
      },
      childServices: {
        $first: "$childServices.service"
      },
      sli: {
        $avg: "$childServices.availability"
      }
    }
  },
  {
    "$group": { <-- In this group, we groupBy month and year, and we push the child services record into an array, using $push. This gives us, for every month and year, the average of all distinct childservices
      "_id": {
        month: "$_id.month",
        year: "$_id.year"
      },
      "childServices": {
        "$push": {
          service: "$childServices",
          sli: "$sli"
        }
      }
    }
  },
  {
    $sort: {
      "_id.month": 1,
      "_id.year": 1
    }
  }
])

Playground link.

edited Aug 23, 2022 at 14:43

answered Aug 23, 2022 at 12:46

Charchit Kapoor

9,3242 gold badges11 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ankit Chauhan Over a year ago

Date is already in date type. In Playground, it's not including second childService i.e. MCT Packet Loss. Each month should have an array of all the childServices with their avg for that month. Updated my question with correct expected output

Ankit Chauhan Over a year ago

Thank you so much, it seems to be working. If possible please add comments in playground about the first group aggregation, so that I can understand it.

Charchit Kapoor Over a year ago

Added the comments in the answer itself

Collectives™ on Stack Overflow

Aggregation: Grouping based on array element in MongoDB

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related