CODEWITHSUNDEEP
mongodbmongooseaggregation-framework
user1790300
user1790300

Reputation: 1735

How to join between two collections when foreign key in an array of ObjectIs's

Let's say I have the following collection structure for mongodb:

The mongoose schema is listed below:

UserSchema: Schema = new Schema({
    userName: {type: Schema.Types.String, minlength: 6, maxlength: 10, required: true},
    emailAddress: {type: Schema.Types.String, minlength: 8, maxlength: 55, required: true}
}

ProductSchema: Schema = new Schema({
    sku: {type: Schema.Types.String, minlength: 1, maxlength: 30, required: true},
    price: {type: Schema.Types.Number, required: true}
}

ProductRewardSchema: Schema = new Schema({
    products: [{productId: {type: Schema.Types.ObjectId, required: true, ref: 'product'}}],
    pointsNeeded: {type: Schema.Types.Number, required: true},
    rewardAmount: {type: Schema.Types.Number, required: true}
}

ProductUserSchema: Schema = new Schema({
    productId: {type: Schema.Types.ObjectId, ref: 'product'},
    userId: {type: Schema.Types.ObjectId, ref: 'user'}
}

DB Version: 3.6

and I need to calculate the points users have accumulated to determine the rewards they are eligibility for. How should I perform this in mongodb? I know how this could be done in sql just not nosql.

I am trying something like this but am not sure this is right:

db.productUsers.aggregate(
    { "$unwind": "productRewards.products" },
    {
        "$lookup": {
            from: "productRewards",
            localField: "productRewards.products.productId",
            foreignField: "productId",
            as: "rewards"
        }
    },
    {
        "$group": {
            userId: "$userId", productId: "$productId", total:  { $sum: "$price" }
        }
    },
    {
        "$project": {
            userId: 1,
            productId: 1,
            total: 1
        }
    }
)

Additionally I am expecting a json output like this:

{ "userId": "ObjectId string", "name": "product name string", "total": 50 }

Upvotes: 0

Views: 2371

Answers (1)

s7vr
s7vr

Reputation: 75914

I'll start from productUsers collection and join to productRewards collection on productId and work from there.

Something like

db.productUsers.aggregate(
    {
        "$lookup": {
            from: "productRewards",
            localField: "productId",
            foreignField: "products.productId",
            as: "rewards"
        }
    },
    {
        "$project": {
            userId: 1,
            productId: 1,
            points: {$arrayElemAt:["$rewards.pointsNeeded", 0]}
        }
    }
)

Upvotes: 2

Related Questions