Not sure why I’m not getting this… I just want to return the results without the outer brackets, which I’ve done before fine, but the way my last stage is grouping, it sets up the results in a way that gives errors with $mergeObjects (error: input is an array) and $concatArrays (input is an object).
Pipeline:
User.aggregate([
{
$match: {
_id: userId
}
},
{
$lookup: {
from: 'clients',
localField: "clients",
foreignField: "_id",
as: "Clients",
pipeline: [
{
$project: {
_id: 1,
fullName: "$fullName",
contracts: {$last: "$contracts"}
}
},
{
$project: {
_id: 1,
fullName: 1,
contracts: {
type: "$contracts.contType",
hours: "$contracts.contHours"
}
}
}
]
}
},
{
$lookup: {
from: "visits",
localField: "visits",
foreignField: "_id",
as: "Visits",
pipeline: [
{
$match: {
$and: [
{visitStart: {$gte: new Date(start)}},
{visitEnd: {$lte: new Date(end)}}
]
}
},
{
$project: {
_id: 1,
client: "$client",
hours: "$totalHours",
month: {
"$month": "$visitEnd"
},
year: {
"$year": "$visitEnd"
}
}
},
{
$project: {
_id: 1,
client: 1,
hours: 1,
date: {
$concat: [
{
$substr: [
"$year",
0,
4
]
},
"-",
{
$substr: [
"$month",
0,
2
]
}
]
}
}
},
{
$group: {
_id: {
date: "$date",
client: "$client"
// hours: "$hours"
},
hoursCount: {
$sum: "$hours"
}
}
},
{
$group: {
_id: "$_id.client",
hours: {
$push: {
date: "$_id.date",
count: "$hoursCount"
}
}
}
},
{
$project: {
_id: 0,
client: "$_id",
hours: {
$sortArray: {input: "$hours", sortBy: {date: 1}}
}
}
}
]
}
},
{
$project: {
_id: 0,
"Clients": 1,
"Visits": 1
}
},
{
$project: {
result: {
$map: {
input: "$Clients",
as: "client",
in: {
$mergeObjects: [
"$$client",
{
$arrayElemAt: [
{
$filter: {
input: "$Visits",
as: "visits",
cond: { $eq: ["$$visits.client", "$$client._id"]}
}
},
0
]
}
]
}
}
}
}
},
Result:
[
{
"result": [
{
"_id": "6205a8313fe12d6b4ec354c4",
"fullName": "Johnny Cochrane",
"contracts": {
"type": "Quarterly",
"hours": 120
},
"client": "6205a8313fe12d6b4ec354c4",
"hours": [
{
"date": "2023-4",
"count": 5.51
},
{
"date": "2023-5",
"count": 6
},
{
"date": "2023-6",
"count": 2
}
]
},
{
"_id": "6217c1b73fe12d6b4ec3550e",
"fullName": "Sheila Thompson",
"contracts": {
"type": "Quarterly",
"hours": 140
},
"client": "6217c1b73fe12d6b4ec3550e",
"hours": [
{
"date": "2023-4",
"count": 5.5
},
{
"date": "2023-5",
"count": 4
},
{
"date": "2023-6",
"count": 2
}
]
}
]
}
]
I’ve tried various options around a final stage like this, but cannot seem to get it right:
{
$replaceRoot: {
newRoot: {
$mergeObjects: [
"$$ROOT",
"$result"
]
}
}
},
Ideally I’d just have the actual data array like so:
[
{
"_id": "6205a8313fe12d6b4ec354c4",
"fullName": "Johnny Cochrane",
"contracts": {
"type": "Quarterly",
"hours": 120
},
"client": "6205a8313fe12d6b4ec354c4",
"hours": [
{
"date": "2023-4",
"count": 5.51
},
{
"date": "2023-5",
"count": 6
},
{
"date": "2023-6",
"count": 2
}
]
},
{
"_id": "6217c1b73fe12d6b4ec3550e",
"fullName": "Sheila Thompson",
"contracts": {
"type": "Quarterly",
"hours": 140
},
"client": "6217c1b73fe12d6b4ec3550e",
"hours": [
{
"date": "2023-4",
"count": 5.5
},
{
"date": "2023-5",
"count": 4
},
{
"date": "2023-6",
"count": 2
}
]
}
]
Thanks for any guidance.
2
Answers
Not sure what I wasn't getting, but needed to $unwind "$result" - turned the array to object, then $replaceRoot. For some reason I had thought I did that... maybe just moving too fast.
.pop()it.