title: Performing a join against a sub subdocument in mongodb
body: <p>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.  </p>
  
  <p>I would like to print out the following json:</p>
  
  <pre><code>{"userId": "user1", "StoreName": "Store 1", "ProductName": "Crest Toothpaste", "# of Purchases": 200}
  </code></pre>
  
  <p>I am using version 3.6 of mongodb</p>
  
  <ul>
  <li>stores
  
  <ul>
  <li>id</li>
  <li>name</li>
  <li>city</li>
  <li>state</li>
  <li>zipCode</li>
  </ul></li>
  <li>users
  
  <ul>
  <li>id</li>
  <li>userId</li>
  <li>emailAddress</li>
  <li>products: [{      productId,      stores: [{storeId}]     }]</li>
  </ul></li>
  <li>products
  
  <ul>
  <li>id</li>
  <li>sku</li>
  <li>price</li>
  <li>productRewards: [{        pointsNeeded,       rewardAmount    }]</li>
  </ul></li>
  </ul>
  
  <p>Here is a sample of the schema:</p>
  
  <pre><code>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},
  
  }
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>db.user.aggregate(
      { $unwind : "$products" },
      { $unwind : "$products.stores" },
      { "$lookup": {
          from: "stores",
          localField: "storeId",
          foreignField: "_id",
          as: "stores"
      }
  })
  </code></pre>
  
  <p>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.</p>