CODEWITHSUNDEEP
mongodbmongooseaggregation-framework
user1790300
user1790300

Reputation: 1735

Performing a join against a sub subdocument in mongodb

I am trying to do a left join using mongodb to get a count of the number of each product was purchased in each store per user. I am new to aggregates and nosql and am having a problem seeing how to join the users.products.stores to the stores table to get the name of the store and the products table to the users.products.productId to get the name of the product.

I would like to print out the following json:

{"userId": "user1", "StoreName": "Store 1", "ProductName": "Crest Toothpaste", "# of Purchases": 200}

I am using version 3.6 of mongodb

Here is a sample of the schema:

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}
    products: [{productId: {type: Schema.Types.ObjectId, required: true, ref: 'product’},
              stores: [{storeId: {type: Schema.Types.ObjectId, required: true, ref: ‘store’}]]
})

ProductSchema: Schema = new Schema({
    name: {type: Schema.Types.String, minlength: 1, maxlength: 30, required: true},
    price: {type: Schema.Types.Number, required: true},
    productRewards: [{
        pointsNeeded: {type: Schema.Types.Number, required: true},
        rewardAmount: {type: Schema.Types.Number, required: true}
    }]
}

StoreSchema: Schema = new Schema({
        name: {type: Schema.Types.String, minlength: 10, maxlength: 30, required: true},
        city: {type: Schema.Types.String, minlength: 1, maxlength: 35, required: true},
    state: {type: Schema.Types.String, minlength: 1, maxlength: 35, required: true},
    zipCode: {type: Schema.Types.String, minlength: 5, maxlength: 13, required: true},

}

I tried to do something like this but I am not getting it to only pull where the memberId fields are equal on the left and right side of the join instead it seems to be more like a full join:

db.user.aggregate(
    { $unwind : "$products" },
    { $unwind : "$products.stores" },
    { "$lookup": {
        from: "stores",
        localField: "storeId",
        foreignField: "_id",
        as: "stores"
    }
})

How could I perform the necessary joins considering one is a sub subdocument? I am confused about what each join would look especially the sub subdocument one.

Upvotes: 0

Views: 1199

Answers (1)

s7vr
s7vr

Reputation: 75934

You can use below aggregation.

The query below performs a multiple $lookup one for each product and store information followed by $group on store, user and product to count the no of purchases.

$unwind the product embedded array followed by $lookup to get product info.

$unwind the store embedded array inside the product followed by $lookup to get store info.

db.users.aggregate([
  {"$unwind":"$products"},
  {"$lookup":{
    "from":"products",
    "localField":"products.productId",
    "foreignField":"_id",
    "as":"products.product"
  }},
  {"$unwind":"$products.stores"},
  {"$lookup":{
    "from":"stores",
    "localField":"products.stores.storeId",
    "foreignField":"_id",
    "as":"products.stores"
  }},
  {"$group":{
    "_id":{
      "userId":"$userId",
      "storeId":"$products.stores.storeId",
      "productId":"$products.productId"
    },
    "StoreName":{"$first":{"$arrayElemAt":["$products.stores.name", 0]}},
    "ProductName":{"$first":{"$arrayElemAt":["$products.product.name", 0]}},
    "# of Purchases":{"$sum":1}
  }},
  {"$project":{
    "_id":0,
    "userId":"$_id.userId",
    "StoreName":1,
    "ProductName":1,
    "# of Purchases":1
  }}
])

Upvotes: 2

Related Questions