I am trying to merge an object inside an array with the same date but with a different key name for the status key.
I have 2 collections users and canteens
The query I am trying to get the result but am not able to figure out how to merge the object with the same Date
OUTPUT
User.aggregate([
{ $sort: { workerId: 1 } },
{
$lookup: {
from: "canteens",
localField: "_id",
foreignField: "employeeId",
pipeline: [
{
$match: {
Date: {
$gte: new Date(fromDate),
$lte: new Date(toDate),
},
},
},
{
$project: {
Date: 1,
status: 1,
},
},
],
as: "canteens",
},
},
{
$project: {
_id: 1,
workerId: 1,
workerFirstName: 1,
workerSurname: 1,
workerDepartment: 1,
workerDesignation: 1,
locationName: 1,
canteenData: "$canteens",
},
},
]);
[
{
"_id": "60e6fd3616dd663e84a925e2",
"workerFirstName": "Firstaname",
"workerSurname": "lastname",
"workerId": "1",
"locationName": "location",
"workerDesignation": "designation",
"workerDepartment": "department",
"canteenData": [
{
"_id": "63b285b9e92eee614feb7be1",
"status": "LUNCH",
"Date": "2023-01-02T00:00:00.000Z"
},
{
"_id": "63b2db8db10c24487201e0a2",
"status": "DINNER",
"Date": "2023-01-02T00:00:00.000Z"
},
{
"_id": "63b39b247adbeb50bfbe3503",
"status": "BREAK FAST",
"Date": "2023-01-03T00:00:00.000Z"
},
{
"_id": "63b3d248c076184fb07ff2c4",
"status": "LUNCH",
"Date": "2023-01-03T00:00:00.000Z"
},
{
"_id": "63b42b8ccb57a4cb7af34015",
"status": "DINNER",
"Date": "2023-01-03T00:00:00.000Z"
},
{
"_id": "63b4ef71e038498fe6634506",
"status": "BREAK FAST",
"Date": "2023-01-04T00:00:00.000Z"
}
]
},
{
"_id": "60e6fd3616dd663e84a925e2",
"workerFirstName": "Firstaname1",
"workerSurname": "lastname1",
"workerId": "2",
"locationName": "location",
"workerDesignation": "designation",
"workerDepartment": "department",
"canteenData": [
{
"_id": "63b285b9e92eee614feb7be1",
"status": "LUNCH",
"Date": "2023-01-02T00:00:00.000Z"
},
{
"_id": "63b2db8db10c24487201e0a2",
"status": "DINNER",
"Date": "2023-01-02T00:00:00.000Z"
},
{
"_id": "63b39b247adbeb50bfbe3503",
"status": "BREAK FAST",
"Date": "2023-01-03T00:00:00.000Z"
},
{
"_id": "63b3d248c076184fb07ff2c4",
"status": "LUNCH",
"Date": "2023-01-03T00:00:00.000Z"
},
{
"_id": "63b42b8ccb57a4cb7af34015",
"status": "DINNER",
"Date": "2023-01-03T00:00:00.000Z"
},
{
"_id": "63b4ef71e038498fe6634506",
"status": "BREAK FAST",
"Date": "2023-01-04T00:00:00.000Z"
}
]
}
]
The output I am trying to get
[
{
"_id": "60e6fd3616dd663e84a925e2",
"workerFirstName": "Firstanem",
"workerSurname": "lastname",
"workerId": "1",
"locationName": "location",
"workerDesignation": "designation",
"workerDepartment": "department",
"canteenData": [
{
"_id": "63b285b9e92eee614feb7be1",
"status1": "LUNCH",
"status2": "DINNER",
"Date": "2023-01-02T00:00:00.000Z"
},
{
"_id": "63b39b247adbeb50bfbe3503",
"status1": "BREAK FAST",
"status2": "LUNCH",
"status3": "DINNER",
"Date": "2023-01-03T00:00:00.000Z"
},
{
"_id": "63b4ef71e038498fe6634506",
"status1": "BREAK FAST",
"Date": "2023-01-04T00:00:00.000Z"
}
]
},
{
"_id": "60e6fd3616dd663e84a925e2",
"workerFirstName": "Firstanem1",
"workerSurname": "lastname1",
"workerId": "2",
"locationName": "location",
"workerDesignation": "designation",
"workerDepartment": "department",
"canteenData": [
{
"_id": "63b285b9e92eee614feb7be1",
"status1": "LUNCH",
"status2": "DINNER",
"Date": "2023-01-02T00:00:00.000Z"
},
{
"_id": "63b39b247adbeb50bfbe3503",
"status1": "BREAK FAST",
"status2": "LUNCH",
"status3": "DINNER",
"Date": "2023-01-03T00:00:00.000Z"
},
{
"_id": "63b4ef71e038498fe6634506",
"status1": "BREAK FAST",
"Date": "2023-01-04T00:00:00.000Z"
}
]
}
]
CodePudding user response：
One option is to add 2 steps into your $lookup pipeline aggregation:
{$group: {
_id: "$Date",
_idVal: {$first: "$_id"},
data: {$addToSet: "$status"}
}},
{$replaceRoot: {
newRoot: {
$mergeObjects: [
{_id: "$_idVal", Date: "$_id"},
{$arrayToObject: {
$reduce: {
input: "$data",
initialValue: [],
in: {$concatArrays: [
"$$value",
[{k: {$concat: [
"status",
{$toString: {$add: [{$size: "$$value"}, 1]}}
]},
v: "$$this"}]
]}
}
}}
]
}
}}
See how it works on the playground example
CodePudding user response：
It's not easy to create status1, status2, ... variables dynamically how do we know BREAK FAST should be status1 and not status2.
Alternative solution: We $group inside correlated subqueries and push all status values into an array
db.users.aggregate([
{
"$lookup": {
"from": "canteens",
"localField": "_id",
"foreignField": "employeeId",
pipeline: [
{
// Put your custom filters here
$match: {}
},
{
$group: {
_id: "$Date",
//pick "first" canteens _id
id: {
$first: "$_id"
},
status: {
$push: "$status"
}
}
},
{
$project: {
_id: "$id",
Date: "$_id",
status: 1
}
},
],
as: "canteenData",
}
}
])