mongodbmongooseaggregation-frameworkaggregation

How to Unwind / populate an Array in Mongoose Aggregate Pipeline


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.


Solution

  • This aggregation should get you the desired output.

    1. $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.
    2. $set: the comments array to the output from a $filter matching only comments that have an _id equal to ObjectId("6678d63114c2fef2955395f0").
    3. $unwind: the comments array for the next stage. There should only be one object since you matched on _id.
    4. $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.