Avoid duplicate documents and Lookup with same 'as' key

Question

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 !

So, if in your example `leads` document, `"_id"` is changed to `{"$oid": "630273bbe0f3f82d85b149bd"}`, what do you want the output to look like? Or perhaps we need some more example docs to demonstrate all the issues and the correct output? [Your current query with example docs at mongoplayground.net](https://mongoplayground.net/p/f0LALuHjPCv) (with `"_id"` change). — rickhg12hs, Sep 11 '22 at 05:06
Please add your requested result, since currently it is not clear — nimrod serok, Sep 11 '22 at 07:25
Thank you very much for your response, 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 — Samuel Sitruk, Sep 11 '22 at 12:52
What happens if you have both `lead` and `leaduser` on the same booking? — nimrod serok, Sep 11 '22 at 13:39
The group by `tel` is a lookup group and I want the last created `Booking`, `lead` and `leadUser` is just the name on `as` in lookup, a booking has just a ref to `Users` or `Leads` Models, my problem is that I don't want same tel on result, the second problem is that I want the same key for `lead` and `leadUser` — Samuel Sitruk, Sep 11 '22 at 13:45
Does [this](https://mongoplayground.net/p/U0heLg0ATlt) do what you want? — nimrod serok, Sep 11 '22 at 13:47

nimrod serok · Accepted Answer · 2022-09-11T13:55:11.870

If I understand correctly, you want something like this:

After your $lookups, instead of `$unwind, concat the arrays and keep only the first item.
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

Avoid duplicate documents and Lookup with same 'as' key

1 Answers1