I'm trying to make an aggregate work with mongoose but I'm having several problem,

Document from Users Model

{
  "_id": {
    "$oid": "630273bbe0f3f82d85b149bd"
  },
  "password": "$2b$12$2f04ijl0fDVHUgBF3rZi.eKfNYkzHZShHNPKfiDEu2NXEXhb.2O",
  "name": "name1",
  "tel": " 33612345678"
}

Document from Leads Model

{
  "_id": {
    "$oid": "6319e54c8cc8b7a5521ad266"
  },
  "name": "name2",
  "tel": " 33712345678"
}

I have an other Model Bookings with a ref of Users or Leads Models with which I would like to make aggregation,

There is an exemple of Booking Document:

{
  "_id": {
    "$oid": "6310a86c6303d0987fe6fac4"
  },
  "date": {
    "$date": "2022-09-01T14:36:12Z"
  },
  "user": {
    "$oid": "630273bbe0f3f82d85b149bd"
  },
  "ref": "User"
}

There is my aggregation:

const leads = await this.bookingModel.aggregate([
      {
        $group: {
          _id: {
            user: '$user'
          }
        }
      },
      {
        $lookup: {
          from: 'users',
          localField: '_id.user',
          foreignField: '_id',
          as: 'leadUser',
          pipeline: [
            {
              $project: {
                name: 1,
                tel: 1
              }
            }
          ]
        }
      },
      { $unwind: { path: '$leadUser', preserveNullAndEmptyArrays: true } },
      {
        $lookup: {
          from: 'leads',
          localField: '_id.user',
          foreignField: '_id',
          as: 'lead',
          pipeline: [
            {
              $project: {
                name: 1,
                tel: 1
              }
            }
          ]
        }
      },
      { $unwind: { path: '$lead', preserveNullAndEmptyArrays: true } }
    ])

There are several problems that I would like to solve,

First, I can't set the same key on the lookup with as, how to avoid putting lead and leadUser and put the same key ?

Also, how to $group by tel to avoid duplicate documents, the tel is in the two models, and now I just group with user but I don't want that User and Lead have same tel.

EDIT: There is the playground https://mongoplayground.net/p/TyYvasdyz_O

I would like the same key for lead and leadUser, and group by tel to remove duplicate

Thank you very much !

CodePudding user response：

If I understand correctly, you want something like this:

1. After your $lookups, instead of `$unwind, concat the arrays and keep only the first item.
2. Group by the $tel to keep it unique

db.bookings.aggregate([
  {$group: {_id: {user: "$user"}}},
  {$lookup: {
      from: "users",
      localField: "_id.user",
      foreignField: "_id",
      as: "leadUser",
      pipeline: [{$project: {name: 1, tel: 1}}]
    }
  },
  {$lookup: {
      from: "leads",
      localField: "_id.user",
      foreignField: "_id",
      as: "lead",
      pipeline: [{$project: {name: 1, tel: 1}}]
    }
  },
  {$project: {lead: {$first: {$concatArrays: ["$lead", "$leadUser"]}}}},
  {$group: {_id: "$lead.tel", lead: {$first: "$lead"}, user: {$first: "$_id.user"}}},
  {$project: {_id: {user: "$user"}, lead: 1}}
])

See how it works on the playground example