Grouping after Mongoose aggregation lookup

Question

Actually im new to mongoDB and mongoose, and im tryig to get nested join using three schemas and grouping them.

const company = Schema(
  {
    title: {type: String, required: true},
  }
);

const plans = Schema(
  {
    companyId: {type: Schema.Types.ObjectId, ref: 'company', required: true},
    title: {type: String, required: true},
  }
);

const promotions = Schema(
  {
    planId: {type: Schema.Types.ObjectId, ref: 'plans', required: true},
    title: {type: String, required: true},
  }
);

I got the below result but separated, and I would like to group it, any help with this point would be appreciated?

[
  {
    _id: '621c2749ac447abf20a8a263',
    title: 'test 1',
    plans: {
      _id: '621c290ad6bce1084f900b0b',
      title: 'test 1',
      promotions: {
        _id: '621d1187b18de3c35fa3963b',
        title: 'test 1',
      },
    },
  },
  {
    _id: '621c2749ac447abf20a8a263',
    title: 'test 2',
    plans: {
      _id: '621c290ad6bce1084f900b0b',
      title: 'test 2',
      promotions: {
        _id: '621d1187b18de3c35fa3963d',
        title: 'test 2',
      },
    },
  },
];

The result that i want to achieve is:

[
  {
    title: 'company name',
    plans: [
            {
              title:'plan name',
              promotions: [
                { 
                  title:'promotion name'
                }
              ]
            },
            ...
          ]
  },
  ...
]

Answer 1

A nested "$lookup" is one way to do it.

db.company.aggregate([
  {
    // lookup plans matching companies
    "$lookup": {
      "from": "plans",
      "localField": "_id",
      "foreignField": "companyId",
      "pipeline": [
        {
          // lookup promotions matching plans
          "$lookup": {
            "from": "promotions",
            "localField": "_id",
            "foreignField": "planId",
            "as": "promotions"
          }
        }
      ],
      "as": "plans"
    }
  },
  {
    // drop unwanted fields
    "$project": {
      "_id": 0,
      "plans._id": 0,
      "plans.companyId": 0,
      "plans.promotions._id": 0,
      "plans.promotions.planId": 0
    }
  }
])

Try it on mongoplayground.net.