Aggregation: Grouping based on array element in MongoDB-CodePudding

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.

CodePudding user response：

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.