mongoose aggregate lookup in project

Question

I currently have 3 different collections.

ColA

{
    id: 1,
    some_other_data: "fff"
}

ColB

{
    id: 1,
    colA_id: 1,
    spec_id: 5,
    data: "test"
}

and

ColC

{
    id: 5,
    colA_id: 1,
    name: "xxx"
}

My current code:

const list = await ColA.aggregate([
{
    $lookup: {
    from: "ColB",
    localField: "_id",
    foreignField: "ColA_id",
    as: "col_b_list",
    },
},
{
    $lookup: {
    from: "ColC",
    localField: "col_b_list.spec_id",
    foreignField: "_id",
    as: "col_c_list",
    },
},
{
    $project: {
    _id: 1,
    list: "$col_b_list",
    },
},
]);

return list;

I have following output.

[
    {
        "_id": "6123858b5b8dcc0e749c9e39",
        "list": [
            {
                "_id": "6123858b5b8dcc0e749c9e43",
                "spec_id": "60d33125f81840c010052e03",
                "createdAt": "2021-08-23T11:24:59.292Z",
                "updatedAt": "2021-08-23T11:24:59.292Z",
                "__v": 0
            },
            {
                "_id": "612386317dd1cb0ebcef1862",
                "spec_id": "60d33125f81840c010052e03",
                "createdAt": "2021-08-23T11:27:45.515Z",
                "updatedAt": "2021-08-23T11:27:45.515Z",
                "__v": 0
            }
        ]
    }
]

What I'm trying to achieve is to get spec_id in ColB, and the id from ColC together in one object.

But what I want is, that in the list object the looked up Collection ColC is included, like:

[
    {
        "_id": "6123858b5b8dcc0e749c9e39",
        "some_other_data": "fff",
        "list": [
            {
                "_id": "6123858b5b8dcc0e749c9e43",
                "spec_id": "60d33125f81840c010052e03",
                "name": "xxx",
                "createdAt": "2021-08-23T11:24:59.292Z",
                "updatedAt": "2021-08-23T11:24:59.292Z",
                "__v": 0
            },
            {
                "_id": "612386317dd1cb0ebcef1862",
                "spec_id": "60d33125f81840c010052e03",
                "name": "yyy",
                "createdAt": "2021-08-23T11:27:45.515Z",
                "updatedAt": "2021-08-23T11:27:45.515Z",
                "__v": 0
            }
        ]
    }
]

I tried to map it in the project stage, but somehow there where multiple same objects, so I could not get it working properly.

Thanks for the help in advance!

Answer 1

You can use nested $lookup, using lookup with aggregation pipeline,

const list = await ColA.aggregate([
  {
    $lookup: {
      from: "ColB",
      let: { id: "$_id" },
      pipeline: [
        {
          $match: {
            $expr: { $eq: ["$$id", "$ColA_id"] }
          }
        },
        {
          $lookup: {
            from: "ColC",
            localField: "spec_id",
            foreignField: "id",
            as: "ColC"
          }
        },
        {
          $addFields: {
            ColC: { $arrayElemAt: ["$ColC", 0] }
          }
        },
        // { $project: {} }
      ],
      as: "list"
    }
  }
])