Join equivalent with mongoose/mongodb-CodePudding

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.

CodePudding user response：

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

guestUser.aggregate(
[{"$lookup": 
   {"from": "partnerOrders",
    "localField": "_id",
    "foreignField": "guest_user_id",
    "pipeline": [{"$match": {"partner_id": {"$eq": "TF"}}}],
    "as": "partnerOrders"}},
 {"$unwind": "$partnerOrders"},
 {"$replaceRoot": 
   {"newRoot": {"$mergeObjects": ["$partnerOrders", "$$ROOT"]}}},
 {"$project": {"partnerOrders": 0}}])