Aggregate and reduce a nested array based upon an ObjectId

user_7376526 0
Viewed 201

I have an Event document structured like so and I'm trying to query against the employeeResponses array to gather all responses (which may or may not exist) for a single employee:

[
  { 
    ...
    eventDate: 2019-10-08T03:30:15.000+00:00,
    employeeResponses: [
      {
        _id:"5d978d372f263f41cc624727", 
        response: "Available to work.", 
        notes: ""
      },
      ...etc
    ];
  }
];

My current mongoose aggregation is:

const eventResponses = await Event.aggregate([
  {
    // find all events for a selected month
    $match: {
      eventDate: {
        $gte: startOfMonth,
        $lte: endOfMonth,
      },
    },
  },
  {
    // unwind the employeeResponses array
    $unwind: {
      path: "$employeeResponses",
      preserveNullAndEmptyArrays: true,
    },
  },
  {
    $group: {
      _id: null,
      responses: {
        $push: {
          // if a response id matches the employee's id, then 
          // include their response; otherwise, it's a "No response."
          $cond: [
            { $eq: ["$employeeResponses._id", existingMember._id] },
            "$employeeResponses.response",
            "No response.",
          ],
        },
      },
    },
  },
  { $project: { _id: 0, responses: 1 } },
]);

As you'll no doubt notice, the query above won't work after more than 1 employee records a response because it treats each individual response as a T/F condition, instead of all of the responses within the employeeResponses array as a single T/F condition.

As a result, I had remove all subsequent queries after the initial $match and do a manual reduce:

const responses = eventResponses.reduce((acc, { employeeResponses }) => {
  const foundResponse = employeeResponses.find(response => response._id.equals(existingMember._id));

  return [...acc, foundResponse ? foundResponse.response : "No response."];
}, []);

I was wondering if it's possible to achieve the same reduce result above, but perhaps using mongo's $reduce function? Or refactor the aggregation query above to treat all responses within the employeeResponses as a single T/F condition?

The ultimate goal of this aggregation is extract any previously recorded employee's responses and/or lack of a response from each found Event within a current month and place their responses into a single array:

["I want to work.", "Available to work.", "Not available to work.", "No response.", "No response." ...etc]
mongoose aggregation-framework mongodb
1 Answers

You can use $filter with $map to reshape your data and filter by _id. Then you can keep using $push with $ifNull to provide default value if an array is empty:

db.collection.aggregate([
    {
        $addFields: {
            employeeResponses: {
                $map: {
                    input: {
                        $filter: {
                            input: "$employeeResponses",
                            cond: {
                                $eq: [ "$$this._id", "5d978d372f263f41cc624727"]
                            }
                        }
                    },
                    in: "$$this.response"
                }
            }
        }
    },
    {
        $group: {
            _id: null,
            responses: { $push: { $ifNull: [ { $arrayElemAt: [ "$employeeResponses", 0 ] }, "No response" ] } }
        }
    }
])

Mongo Playground

user_62389770
Related