MongoDB: -合并两个具有相同键的数组

Question

我有3个集合，用户,上座率,．我正在努力获得出勤率和在用户的帮助下我有一个date在移位考勤中的Date键并且我试图将两个数据合并为1按日期，如果不存在，那么该键应该是none

下面是我尝试的查询，它在某种程度上工作，但没有得到最终结果

User.aggregate([
{ $sort: { workerId: 1 } },
{
$lookup: {
from: "shifts",
localField: "_id",
foreignField: "employeeId",
pipeline: [
{
$match: {
date: {
$gte: new Date(fromDate),
$lte: new Date(toDate),
},
},
},
{
$project: {
date: 1,
shiftCode: 1,
},
},
{
$sort: {
date: 1,
},
},
],
as: "shifts",
},
},
{
$project: {
_id: 1,
workerId: 1,
shiftListData: "$shifts",
},
},
{
$lookup: {
from: "attendances",
localField: "_id",
foreignField: "employeeId",
pipeline: [
{
$match: {
Date: { $gte: new Date(fromDate), $lte: new Date(toDate) },
},
},
{
$project: {
inTime: 1,
name: 1,
Date: 1,
},
},
],
as: "attendances",
},
},
]);

输出

[
{
"workerId": "1005",
"shiftListData": [
{
"_id": "63875e8182ebbe13ee9531d4",
"shiftCode": "HOBGS_1100",
"date": "2022-12-31T00:00:00.000Z"
},
{
"_id": "63b277a2f6a8eccb2d95d407",
"shiftCode": "WO",
"date": "2023-01-01T00:00:00.000Z"
},
{
"_id": "63b27787f6a8eccb2d95cf30",
"shiftCode": "HOBGS_1100",
"date": "2023-01-02T00:00:00.000Z"
},
{
"_id": "63b277a2f6a8eccb2d95d409",
"shiftCode": "HOBGS_1100",
"date": "2023-01-03T00:00:00.000Z"
}
],
"attendances": [
{
"_id": "61307cd385b5055a15cec159",
"Date": "2022-12-31T00:00:00.000Z",
"inTime": "2022-12-31T11:16:10.000Z",
"name": "name2"
},
{
"_id": "63b236ef3980cffaf7715d62",
"inTime": "2023-01-02T07:14:08.000Z",
"Date": "2023-01-02T00:00:00.000Z",
"name": "name2"
}
]
},
{
"workerId": "1006",
"shiftListData": [
{
"_id": "63875e8182ebbe13ee9531d2",
"shiftCode": "HOBGS_1100",
"date": "2022-12-31T00:00:00.000Z"
},
{
"_id": "63b277a2f6a8eccb2d95d403",
"shiftCode": "WO",
"date": "2023-01-01T00:00:00.000Z"
},
{
"_id": "63b27787f6a8eccb2d95cf39",
"shiftCode": "HOBGS_1100",
"date": "2023-01-02T00:00:00.000Z"
},
{
"_id": "63b277a2f6a8eccb2d95d400",
"shiftCode": "HOBGS_1100",
"date": "2023-01-03T00:00:00.000Z"
}
],
"attendances": [
{
"_id": "61307cd385b5055a15cec158",
"Date": "2022-12-31T00:00:00.000Z",
"inTime": "2022-12-31T11:16:10.000Z",
"name": "name"
},
{
"_id": "63b236ef3980cffaf7715d69",
"inTime": "2023-01-02T07:14:08.000Z",
"Date": "2023-01-02T00:00:00.000Z",
"name": "name"
}
]
}
]

我想合并shiftListData和上座率按日期放入一个数组

示例:-

[
{
"workerId": "1005",
"newData": [
{
"_id": "63875e8182ebbe13ee9531d4",
"shiftCode": "HOBGS_1100",
"date": "2022-12-31T00:00:00.000Z",
"attendanceId": "61307cd385b5055a15cec159",
"attendanceDate": "2022-12-31T00:00:00.000Z",
"inTime": "2022-12-31T11:16:10.000Z",
"name": "name2"
},
{
"_id": "63b277a2f6a8eccb2d95d407",
"shiftCode": "WO",
"date": "2023-01-01T00:00:00.000Z"
},
{
"_id": "63b27787f6a8eccb2d95cf30",
"shiftCode": "HOBGS_1100",
"date": "2023-01-02T00:00:00.000Z",
"attendanceId": "63b236ef3980cffaf7715d62",
"inTime": "2023-01-02T07:14:08.000Z",
"attendanceDate": "2023-01-02T00:00:00.000Z",
"name": "name2"
},
{
"_id": "63b277a2f6a8eccb2d95d409",
"shiftCode": "HOBGS_1100",
"date": "2023-01-03T00:00:00.000Z"
}
]
},
{
"workerId": "1006",
"newData": [
{
"_id": "63875e8182ebbe13ee9531d2",
"shiftCode": "HOBGS_1100",
"date": "2022-12-31T00:00:00.000Z",
"attendanceId": "61307cd385b5055a15cec158",
"attendanceDate": "2022-12-31T00:00:00.000Z",
"inTime": "2022-12-31T11:16:10.000Z",
"name": "name"
},
{
"_id": "63b277a2f6a8eccb2d95d403",
"shiftCode": "WO",
"date": "2023-01-01T00:00:00.000Z"
},
{
"_id": "63b27787f6a8eccb2d95cf39",
"shiftCode": "HOBGS_1100",
"date": "2023-01-02T00:00:00.000Z",
"attendanceId": "63b236ef3980cffaf7715d69",
"inTime": "2023-01-02T07:14:08.000Z",
"attendanceDate": "2023-01-02T00:00:00.000Z",
"name": "name"
},
{
"_id": "63b277a2f6a8eccb2d95d400",
"shiftCode": "HOBGS_1100",
"date": "2023-01-03T00:00:00.000Z"
}
]
}
]

Answer 1

一个选项是在您的管道中添加一个步骤，该步骤使用$filter为每个shift项查找其在attendances中的匹配项:

{$set: {
shiftListData: {$map: {
input: "$shiftListData",
as: "shift",
in: {$mergeObjects: [
"$$shift",
{$ifNull: [
{$first: {
$filter: {
input: "$attendances",
cond: {$eq: ["$$this.Date", "$$shift.date"]}
}
}},
{}
]}
]}
}},
attendances: "$$REMOVE"
}}

看看它在操场的例子中是如何工作的