聚合和减少基于ObjectId的嵌套数组 [英] Aggregate and reduce a nested array based upon an ObjectId

问题描述

我有一个结构如下的 Event 文档，我正在尝试针对 employeeResponses 数组进行查询，以收集所有响应(可能存在或可能不存在)单身员工:

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
    ];
  }
];

我当前的猫鼬聚合是:

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 } },
]);

您会毫无疑问地注意到，以上查询在一个以上的员工记录了一个响应后将无法使用，因为它将每个单独的响应都视为T/F条件，而不是所有响应 employeeResponses 数组中的strong>作为单个T/F条件.

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.

结果，我删除了最初的 $ match 之后的所有后续查询，并进行了手动减少:

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."];
}, []);

我想知道是否有可能达到上述相同的减少结果，但是也许使用mongo的 $ reduce 函数?还是重构上面的聚合查询，以将 employeeResponses 内的所有响应都视为单个T/F条件?

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?

此汇总的最终目标是从当月内每个发现的 Event 中提取以前记录的员工的响应和/或缺少响应，并将他们的响应放入一个单独的数组中:

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]

推荐答案

您可以使用 $ filter 与 $映射以重塑数据并通过 _id 进行过滤.然后，您可以将 $ push 与 $ ifNull 可在数组为空时提供默认值:

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" ] } }
        }
    }
])

蒙戈游乐场

这篇关于聚合和减少基于ObjectId的嵌套数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！