I am using $facet to do several groupings and calculations in an aggregate which I'm planning to build a view with. I have the 3 sections of the groups working independently but now I want to merge all the returned object arrays into a single array by _id.

This is the schema I am working with. The tasks array exists inside another document.

   "tasks": [
      {
        "resource": {
          "uuid": "string"
        },
        "supervisor_assessment": {
          "datetime": "string",
          "rating": 0
        },
        "manager_assessment": {
          "datetime": "string",
          "rating": 0
        }
      }
   ]

My aggregate so far

[
  {
    '$unwind': {
      'path': '$tasks'
    }
  }, {
    '$facet': {
      'vmratings': [
        {
          '$group': {
            '_id': {
              'resource': '$tasks.resource.uuid', 
              'rating': '$tasks.venue_manager_assessment.rating'
            }, 
            'count': {
              '$sum': 1
            }, 
            'vmratingavg': {
              '$avg': '$tasks.venue_manager_assessment.rating'
            }
          }
        }, {
          '$group': {
            '_id': '$_id.resource', 
            'vmcounts': {
              '$push': {
                'rating': '$_id.rating', 
                'count': '$count'
              }
            }
          }
        }
      ], 
      'sratings': [
        {
          '$group': {
            '_id': {
              'resource': '$tasks.resource.uuid', 
              'rating': '$tasks.supervisor_assessment.rating'
            }, 
            'count': {
              '$sum': 1
            }
          }
        }, {
          '$group': {
            '_id': '$_id.resource', 
            'scounts': {
              '$push': {
                'rating': '$_id.rating', 
                'count': '$count'
              }
            }
          }
        }
      ], 
      'totalAvg': [
        {
          '$group': {
            '_id': '$tasks.resource.uuid', 
            'vmratingavg': {
              '$avg': '$tasks.venue_manager_assessment.rating'
            }, 
            'sratingavg': {
              '$avg': '$tasks.supervisor_assessment.rating'
            }, 
            'sratingcount': {
              '$sum': '$tasks.supervisor_assessment.rating'
            }, 
            'vmratingcount': {
              '$sum': '$tasks.venue_manager_assessment.rating'
            }
          }
        }
      ]
    }
  }
]

This gives me an output like this (on my test data)

{
    "vmratings": [
        {
            "_id": "string2",
            "vmcounts": [
                {
                    "rating": 2,
                    "count": 53
                },
                {
                    "rating": 3,
                    "count": 1
                }
            ]
        },
        {
            "_id": "string",
            "vmcounts": [
                {
                    "rating": 2,
                    "count": 53
                },
                {
                    "rating": 1,
                    "count": 1
                }
            ]
        }
    ],
    "sratings": [
        {
            "_id": "string2",
            "scounts": [
                {
                    "rating": 1,
                    "count": 53
                },
                {
                    "rating": 4,
                    "count": 1
                }
            ]
        },
        {
            "_id": "string",
            "scounts": [
                {
                    "rating": 5,
                    "count": 1
                },
                {
                    "rating": 1,
                    "count": 53
                }
            ]
        }
    ],
    "totalAvg": [
        {
            "_id": "string2",
            "vmratingavg": 2.0185185185185186,
            "sratingavg": 1.0555555555555556,
            "sratingcount": 57,
            "vmratingcount": 109
        },
        {
            "_id": "string",
            "vmratingavg": 1.9814814814814814,
            "sratingavg": 1.0740740740740742,
            "sratingcount": 58,
            "vmratingcount": 107
        }
    ]
}

Im trying to get the last step to work for the aggregate, merging those three arrays (totalAvg, sratings, vmratings) into one single object array using the _id as the key.

CodePudding user response：

db.collection.aggregate([
  {
    $project: {
      result: {
        $map: {
          input: { $range: [ 0, { $size: "$vmratings" } ] },
          as: "x",
          in: {
            $let: {
              vars: { id: { $arrayElemAt: [ "$vmratings._id", "$$x" ] } },
              in: {
                _id: "$$id",
                vmratings: {
                  $first: {
                    $filter: {
                      input: "$vmratings",
                      as: "q",
                      cond: { $eq: [ "$$q._id", "$$id" ] }
                    }
                  }
                },
                sratings: {
                  $first: {
                    $filter: {
                      input: "$sratings",
                      as: "q",
                      cond: { $eq: [ "$$q._id", "$$id" ] }
                    }
                  }
                },
                totalAvg: {
                  $first: {
                    $filter: {
                      input: "$totalAvg",
                      as: "q",
                      cond: { $eq: [ "$$q._id", "$$id" ] }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  },
  {
    $unwind: "$result"
  },
  {
    $replaceWith: {
      $mergeObjects: [ "$result.sratings", "$result.totalAvg", "$result.vmratings" ]
    }
  }
])

mongoplayground