Go MongoDB nested query with $lookup

Issue

I have quite a complex query that I need for my project and can’t seem to find a working implementation for it. My function below works, but now I need to add a $lookup statement to populate the profile object. As you can see every Match has a PartnerA and partnerB of type Partner. Every Partner has a LiteOffer witch has a profile. The profile needs to be added from the profile collection using PartnerA.ID and PartnerB.ID as the localFields for lookup.

Basically the issue is this: I receive one Partner.ID in my function which is also an id of a profile in a profile collection. Then I need to filter out all matches from the matches collection that have the same Partner.ID either in PartnerA or in PartnerB (only one of the Partners will have the same ID. The other one will have a different one). Finally I have to use mongo $lookup to retrieve the profiles for both PartnerA.Offer.Profile and PartnerB.Offer.Profile using the corresponding Partner.ID

Here is the Match struct (I cut out many of the fields so it would be easier to read):

type Match struct {
    ID        primitive.ObjectID `json:"_id,omitempty" bson:"_id,omitempty"`
    PartnerA  Partner            `json:"partnerA" bson:"partnerA"`
    PartnerB  Partner            `json:"partnerB" bson:"partnerB"`
    Unlocked  bool               `json:"unlocked" bson:"unlocked"`
    DeletedAt time.Time          `json:"deletedAt,omitempty" bson:"deletedAt,omitempty"`
}

type Partner struct {
    ID               primitive.ObjectID `json:"id,omitempty" bson:"id,omitempty"`
    Offer            LiteOffer          `json:"offer,omitempty" bson:"offer,omitempty"`
    LooksInteresting bool               `json:"looksInteresting" bson:"looksInteresting"`
    Unlocked         bool               `json:"unlocked" bson:"unlocked"`
}

type LiteOffer struct {
    ID           primitive.ObjectID `json:"id,omitempty" bson:"id,omitempty"`
    Tags         []Tag              `json:"tags" bson:"tags,omitempty"`
    Profile      Profile            `json:"profile,omitempty" bson:"profile,omitempty"`
}

type Profile struct {
    ID          primitive.ObjectID `json:"id,omitempty" bson:"id" diff:"-"`
    Name        string             `json:"name,omitempty" bson:"name,omitempty" diff:"-"`
    Surname     string             `json:"surname,omitempty" bson:"surname,omitempty" 
}

Here is my function:

func getMatchesByProfileId(id primitive.ObjectID) (*[]Match, error) {
    var matches []Match

    filter := bson.M{
        "$or": []bson.M{
            {
                "partnerA.id":               id,
                "partnerA.looksInteresting": false,
            },
            {
                "partnerB.id":               id,
                "partnerB.looksInteresting": false,
            },
        },
        "unlocked":  false,
        "deletedAt": nil,
    }

    ctx, _ := db.GetTimeoutContext()
    result, err := getMatchCollection().Find(ctx, filter)
    if err != nil {
        log.Error("Could not find matches, Error: ", err)
        return nil, err
    }

    for result.Next(ctx) {
        var m Match
        if err = result.Decode(&m); err != nil {
            log.Error("Could not decode offer in getMatchesByProfileId", err)
            return nil, err
        }
        matches = append(matches, m)
    }
    return &matches, nil
}

Here is a pipeline that I used that works, but now I need to somehow combine these two queries into one:

    pipeline := mongo.Pipeline{
        {{"$match", match}},
        {{"$lookup", bson.M{
            "from":         "profile",
            "localField":   "partnerA.id",
            "foreignField": "_id",
            "as":           "profile",
        }}},
        {{"$unwind", "$profile"}},
    }

I hope this explained everything. It has taken me hours and I can’t find the solution. any help would be highly appreciated.

If you have any questions feel free to ask.

Thank you!

Solution

So I managed To solve this issue myself and here is the function code:

func getMatchesByProfileId(id primitive.ObjectID) (*[]Match, error) {
    var matches []Match

    match := bson.D{
        {"unlocked", false},
        {"deletedAt", nil},
        {"$or", []bson.M{
            {
                "partnerA.id":               id,
                "partnerA.looksInteresting": false,
            },
            {
                "partnerB.id":               id,
                "partnerB.looksInteresting": false,
            },
        }},
    }

    pipeline := mongo.Pipeline{
        {{"$match", match}},
        {{"$lookup", bson.M{
            "from":         "profile",
            "localField":   "partnerA.id",
            "foreignField": "_id",
            "as":           "partnerA.offer.profile",
        }}},
        {{"$unwind", "$partnerA.offer.profile"}},

        {{"$lookup", bson.M{
            "from":         "profile",
            "localField":   "partnerB.id",
            "foreignField": "_id",
            "as":           "partnerB.offer.profile",
        }}},
        {{"$unwind", "$partnerB.offer.profile"}},
    }

    ctx, _ := db.GetTimeoutContext()
    cursor, err := getMatchCollection().Aggregate(ctx, pipeline)
    if err != nil {
        log.Error("Could not aggregate matches, Error: ", err)
        return nil, err
    }

    defer cursor.Close(ctx)

    for cursor.Next(ctx) {
        var m Match
        if err = cursor.Decode(&m); err != nil {
            log.Error("Could not decode matches in getMatchesByProfileId error: ", err)
            return nil, err
        }
        matches = append(matches, m)
    }
    return &matches, nil
}

Answered By – Marius

Answer Checked By – Cary Denson (GoLangFix Admin)

Leave a Reply

Your email address will not be published.