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

user1790300 Source

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

  • users

    • id
    • userId
    • emailAddress
  • products

    • id
    • sku
    • price
  • productRewards

    • products: [{productId}],
    • pointsNeeded
    • rewardAmount
  • productUsers

    • productId
    • userId

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 }

mongodbmongooseaggregation-framework

Answers

answered 3 months ago Veeram #1

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]}
        }
    }
)

comments powered by Disqus