I'm working with Mongoose and trying to unwind/populate the comments.attachments field within an aggregation pipeline. Here's the relevant portion of my code:
const result = await Ticket.aggregate([
{ $match: { $expr: { $eq: ['$_id', { $toObjectId: "667293eca3a67d7c5d9ff1e2" }] } } },
{ $unwind: '$comments' },
{ $match: { $expr: { $eq: ['$comments._id', { $toObjectId: "667382f627df37eb4e7a3220" }] } } },
])
This query successfully retrieves a ticket document and unwinds the comments array. However, the comments.attachments field remains an empty array even though there are attachments associated with the comment.
[
{
_id: new ObjectId('667293eca3a67d7c5d9ff1e2'),
referenceNo: 'TCK_06-19-2024_0001',
subject: new ObjectId('6670dad080ea03a681f70295'),
description: 'pero ang totoo, di bali na ako. ikaw lang iniisip ko.',
priority: 'medium',
status: 'open',
initiator: new ObjectId('6670da9d80ea03a681f70292'),
createdBy: new ObjectId('667106fff8abeeb0d87341ba'),
assignedTo: null,
attachments: [ [Object], [Object] ],
comments: {
userId: new ObjectId('6670db4180ea03a681f7029e'),
content: '2nd comment w atta',
attachments: [Array],
_id: new ObjectId('667382f627df37eb4e7a3220'),
createdAt: 2024-06-20T01:16:38.190Z,
updatedAt: 2024-06-20T01:16:38.190Z
},
createdAt: 2024-06-19T08:16:44.129Z,
updatedAt: 2024-06-20T02:14:51.282Z,
__v: 0
}
]
Here are my models for reference:
Ticket Model
const mongoose = require('mongoose');
const Schema = mongoose.Schema;
const ticketCommentSchema = require('./ticketCommentModel');
const ticketAttachmentSchema = require('./ticketAttachmentModel');
const ticketSchema = new Schema({
// ... other schema properties
comments: [ticketCommentSchema],
attachments: {
type: [ticketAttachmentSchema],
required: true,
validate: [attachmentsArray => attachmentsArray.length > 0, 'At least one attachment is required']
},
}, {
timestamps: true
});
module.exports = mongoose.model('Ticket', ticketSchema);
Ticket Comment Model
const mongoose = require('mongoose');
const Schema = mongoose.Schema;
const ticketAttachmentSchema =
require('./ticketAttachmentModel');
const ticketCommentSchema = new Schema({
userId: {
type: mongoose.Schema.Types.ObjectId,
ref: 'User',
required: true
},
content: {
type: String,
trim: true,
required: true
},
attachments: [ticketAttachmentSchema]
}, {
timestamps: true
});
module.exports = ticketCommentSchema;
Ticket Attachment Model
const mongoose = require('mongoose');
const Schema = mongoose.Schema;
const ticketAttachmentSchema = new Schema({
// ... attachment schema properties
}, {
timestamps: true
});
module.exports = ticketAttachmentSchema;
My question is:
How can I modify the aggregation pipeline to unwind or populate the comments.attachments field and retrieve the attachment details within the query results?
I've searched online for solutions, but haven't found anything specific to this scenario. Any help would be greatly appreciated!
I try this aggregation pipeline
const result = await Ticket.aggregate([
{ $match: { $expr: { $eq: ['$_id', { $toObjectId: "667293eca3a67d7c5d9ff1e2" }] } } },
{ $unwind: '$comments' },
{ $match: { $expr: { $eq: ['$comments._id', { $toObjectId: "667382f627df37eb4e7a3220" }] } } },
{
$unwind: {
path: "$comments.attachments",
preserveNullAndEmptyArrays: true
}
},
])`
and it returns this data
[
{
_id: new ObjectId('667293eca3a67d7c5d9ff1e2'),
referenceNo: 'TCK_06-19-2024_0001',
subject: new ObjectId('6670dad080ea03a681f70295'),
description: 'pero ang totoo, di bali na ako. ikaw lang iniisip ko.',
priority: 'medium',
status: 'open',
initiator: new ObjectId('6670da9d80ea03a681f70292'),
createdBy: new ObjectId('667106fff8abeeb0d87341ba'),
assignedTo: null,
attachments: [ [Object], [Object] ],
comments: {
userId: new ObjectId('6670db4180ea03a681f7029e'),
content: '2nd comment w atta',
attachments: [Object],
_id: new ObjectId('667382f627df37eb4e7a3220'),
createdAt: 2024-06-20T01:16:38.190Z,
updatedAt: 2024-06-20T01:16:38.190Z
},
createdAt: 2024-06-19T08:16:44.129Z,
updatedAt: 2024-06-20T02:14:51.282Z,
__v: 0
},
{
_id: new ObjectId('667293eca3a67d7c5d9ff1e2'),
referenceNo: 'TCK_06-19-2024_0001',
subject: new ObjectId('6670dad080ea03a681f70295'),
description: 'pero ang totoo, di bali na ako. ikaw lang iniisip ko.',
priority: 'medium',
status: 'open',
initiator: new ObjectId('6670da9d80ea03a681f70292'),
createdBy: new ObjectId('667106fff8abeeb0d87341ba'),
assignedTo: null,
attachments: [ [Object], [Object] ],
comments: {
userId: new ObjectId('6670db4180ea03a681f7029e'),
content: '2nd comment w atta',
attachments: [Object],
_id: new ObjectId('667382f627df37eb4e7a3220'),
createdAt: 2024-06-20T01:16:38.190Z,
updatedAt: 2024-06-20T01:16:38.190Z
},
createdAt: 2024-06-19T08:16:44.129Z,
updatedAt: 2024-06-20T02:14:51.282Z,
__v: 0
}
]
it turns comment.attachments to object but still doesn't populate the data of attachments, also the comment have 3 attachments.
This aggregation should get you the desired output.
$match
: the ticket
you want. Note the use of new mongoose.Types.ObjectId()
constructor so that you don't have to use an $expr
and convert $toObjectId
.$set
: the comments
array to the output from a $filter
matching only comments
that have an _id
equal to ObjectId("6678d63114c2fef2955395f0")
.$unwind
: the comments
array for the next stage. There should only be one object since you matched on _id
.$set
: the comments.attachments
array to the output from a $filter
matching only attachments
that have an _id
equal to ObjectId("6678d63114c2fef2955395f1")
.const result = await Ticket.aggregate([
{
$match: {
_id: new mongoose.Types.ObjectId("6678d3b714c2fef2955395ca"),
"comments._id": new mongoose.Types.ObjectId("6678d63114c2fef2955395f0")
}
},
{
$set: {
comments: {
$filter: {
input: "$comments",
as: "c",
cond: {
$eq: [
"$$c._id",
new mongoose.Types.ObjectId("6678d63114c2fef2955395f0")
]
}
}
}
}
},
{
$unwind: "$comments"
},
{
$set: {
"comments.attachments": {
$filter: {
input: "$comments.attachments",
as: "ca",
cond: {
$eq: [
"$$ca._id",
new mongoose.Types.ObjectId("6678d63114c2fef2955395f1")
]
}
}
}
}
}
]);
See HERE for a working example.