我正在嘗試從我得到的陣列中獲取特定欄位aggregate，lookup以及一些cond
下面你可以看到我的查詢
const attendanceData = await User.aggregate([
{
$match: {
lastLocationId: Mongoose.Types.ObjectId(typeId),
isActive: true,
},
},
{
$project: {
_id: 1,
workerId: 1,
workerFirstName: 1,
workerSurname: 1,
},
},
{
$lookup: {
from: "attendances",
localField: "_id",
foreignField: "employeeId",
as: "attendances",
},
},
{
$set: {
attendances: {
$filter: {
input: "$attendances",
cond: {
$and: [
{
$gte: ["$$this.Date", new Date(fromDate)],
},
{
$lte: ["$$this.Date", new Date(toDate)],
},
{
$eq: ["$$this.createdAs", dataType],
},
{
$eq: ["$$this.status", true],
},
{
$eq: ["$$this.workerType", workerType],
},
],
},
},
},
},
},
{ $skip: 0 },
{ $limit: 10 },
]);
作為回應的資料我在下面得到
{
"attendanceSheet": [
{
"_id": "60dd77c14524e6c116e16aaa",
"workerFirstName": "MEGHRAJ",
"workerSurname": "JADHAV",
"workerId": "2036",
"attendances": [
{
"_id": "6130781085b5055a15c32f2u",
"workerId": "2036",
"workerFullName": "MEGHRAJ JADHAV",
"workerType": "Employee",
"Date": "2022-10-01T00:00:00.000Z",
"createdAs": "ABSENT"
},
{
"_id": "6130781085b5055a15c32f2u",
"workerId": "2036",
"workerFullName": "MEGHRAJ JADHAV",
"workerType": "Employee",
"Date": "2022-10-02T00:00:00.000Z",
"createdAs": "ABSENT"
}
]
},
{
"_id": "60dd77c24524e6c116e16c0f",
"workerFirstName": "SANJAY",
"workerSurname": "DUTTA",
"workerId": "2031",
"attendances": [
{
"_id": "6130781a85b5055a15c3455y",
"workerId": "2031",
"workerFullName": "SANJAY DUTTA",
"workerType": "Employee",
"Date": "2022-10-02T00:00:00.000Z",
"createdAs": "ABSENT"
}
]
}
]
}
但我想要像下面這樣的資料，不是每個欄位中的幾個欄位
{
"attendanceSheet": [
{
"_id": "60dd77c14524e6c116e16aaa",
"workerFirstName": "MEGHRAJ",
"workerSurname": "JADHAV",
"workerId": "2036",
"attendances": [
{
"_id": "6130781085b5055a15c32f2u",
"Date": "2022-10-01T00:00:00.000Z",
"createdAs": "ABSENT"
},
{
"_id": "6130781085b5055a15c32f2u",
"Date": "2022-10-02T00:00:00.000Z",
"createdAs": "ABSENT"
}
]
},
{
"_id": "60dd77c24524e6c116e16c0f",
"workerFirstName": "SANJAY",
"workerSurname": "DUTTA",
"workerId": "2031",
"attendances": [
{
"_id": "6130781a85b5055a15c3455y",
"Date": "2022-10-02T00:00:00.000Z",
"createdAs": "ABSENT"
}
]
}
]
}
uj5u.com熱心網友回復：
您可以通過將所有匹配項放在"$lookup" "pipeline".
db.users.aggregate([
{
"$match": {
"lastLocationId": ObjectId("0123456789abcdef01234567"),
"isActive": true
}
},
{
"$project": {
"workerId": 1,
"workerFirstName": 1,
"workerSurname": 1
}
},
{
"$lookup": {
"from": "attendances",
"localField": "_id",
"foreignField": "employeeId",
"as": "attendances",
// do all the matching here
"pipeline": [
{
"$match": {
"Date": {
// fromDate, toDate
"$gte": ISODate("2022-09-01T00:00:00Z"),
"$lte": ISODate("2022-09-30T23:59:59Z")
},
// dataType
"createdAs": "ABSENT",
"status": true,
// workerType
"workerType": "Employee"
}
},
{
"$project": {
"Date": 1,
"createdAs": 1
}
}
]
}
},
{$skip: 0},
{$limit: 10}
])
在mongoplayground.net上試試。
uj5u.com熱心網友回復：
從你所擁有的到所請求的輸出的一種選擇是 to $mapand $reduce：
db.collection.aggregate([
{
$set: {
attendanceSheet: {
$map: {
input: "$attendanceSheet",
as: "external",
in: {
$mergeObjects: [
"$$external",
{
attendances: {
$reduce: {
input: "$$external.attendances",
initialValue: [],
in: {
$concatArrays: [
"$$value",
[
{
_id: "$$this._id",
createdAs: "$$this.createdAs",
Date: "$$this.Date"
}
]
]
}
}
}
}
]
}
}
}
}
}
])
看看它在操場上的例子是如何作業的
uj5u.com熱心網友回復：
以下修改對我有用
const attendanceData = await User.aggregate([
{
$match: {
lastLocationId: Mongoose.Types.ObjectId(typeId),
isActive: true,
},
},
{
$project: {
_id: 1,
workerId: 1,
workerFirstName: 1,
workerSurname: 1,
},
},
{
$lookup: {
from: "attendances",
localField: "_id",
foreignField: "employeeId",
as: "attendances",
},
},
{
$set: {
attendances: {
$filter: {
input: "$attendances",
cond: {
$and: [
{
$gte: ["$$this.Date", new Date(fromDate)],
},
{
$lte: ["$$this.Date", new Date(toDate)],
},
{
$eq: ["$$this.createdAs", dataType],
},
{
$eq: ["$$this.status", true],
},
{
$eq: ["$$this.workerType", workerType],
},
],
},
},
},
},
},
{
$set: {
attendances: {
$reduce: {
input: "$attendances",
initialValue: [],
in: {
$concatArrays: [
"$$value",
[
{
_id: "$$this._id",
createdAs: "$$this.createdAs",
Date: "$$this.Date",
},
],
],
},
},
},
},
},
{ $skip: 0 },
{ $limit: 10 },
]);
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/530356.html