Join equivalent with mongoose/mongodb

Question

Most of my experience is with MySQL and I'm basically trying to do a join equivalent with Mongoose. I need to query all the users that have orders with a certain partner_id. This is how I would write the query in MySQL

SELECT * FROM guestUsers
LEFT JOIN partnerOrders ON guestUsers.id = partnerOrders.guest_user_id
WHERE partnerOrders.partner_id = "TF"

In my partnerOrders schema I have the guest_user_id field which is an objectId that references guestUsers

guest_user_id: { type: Schema.Types.ObjectId, ref: 'guestUser' }

Here is what I have so far with mongoose, but I may be approaching this completely wrong because of my lack of experience

const guest_users = await guestUserModel.aggregate( [
{
  $lookup: {
    from: 'partnerOrders', 
    localField: '_id', 
    foreignField: 'guest_user_id', 
    as: 'partnerOrders',
    let: {partner_id: "$partner_id"},
    pipeline: [
      {$match: {$expr: {$eq: [ "$partner_id",  "TF"] }}}
    ]
  }
}
])

Any help would be greatly appreciated, thank you for your time.

Answer 1

Query

• lookup with pipeline to match the "TF" also
• unwind to seperate the array members
• merge with root and replace it
• project the lookup field (information moved on root anyways)

*i couldn't test it on your sample data but i think with this you will be able to do it, else if you can add sample data and expected output.

PlayMongo

aggregate(
[{"$lookup": 
   {"from": "partnerOrders",
    "localField": "_id",
    "foreignField": "guest_user_id",
    "pipeline": [{"$match": {"partner_id": {"$eq": "TF"}}}],
    "as": "partnerOrders"}},
 {"$unwind": 
   {"path": "$partnerOrders", "preserveNullAndEmptyArrays": true}},
 {"$replaceRoot": 
   {"newRoot": {"$mergeObjects": ["$partnerOrders", "$$ROOT"]}}},
 {"$unset": ["partnerOrders"]}])