I am trying to aggregate data with a foreign model. The structure I am trying to supercharge is the following:

{
    "_id" : ObjectId("62b489664cbb9bc8c947f19f"),
    "user_id" : ObjectId("61a775da4cbb9bc8c947edd9"),
    "product_types" : [ 
        {
            "type" : NumberLong(1),
            "product_id" : ObjectId("62b4890f4cbb9bc8c947e5ef"),
        }, 
        {
            "type" : NumberLong(1),
            "product_id" : ObjectId("62b4890f4cbb9bc8c947e5ed"),
        }
    ]
}

I am trying to add product data from product_id, and I think I am pretty close to it, but I am adding 2 identical products in an array instead of the correct one: Query:

db.getCollection('interests').aggregate([
    {
        $lookup:{
            from: "products",
            localField: "product_types.product_id",
            foreignField: "_id",
            as: "productInterestData"
        }
    },
    {
      $set: {
        "product_types.product": {
          $map: {
            input: "$product_types",
            in: {
              $mergeObjects: [
                "$this",
                {
                    $arrayElemAt: [
                      "$productInterestData",
                      {$indexOfArray: ["$productInterestData.id", "$this.id"]}
                    ]
                }
              ]
            }
          }
        }
      }
    },
    {$unset: "productInterestData"}
  ])

Result (with an array of 2 identical products, instead of the correct one):

{
    "_id" : ObjectId("62b489664cbb9bc8c947f19f"),
    "user_id" : ObjectId("61a775da4cbb9bc8c947edd9"),
    "product_types" : [ 
        {
            "type" : NumberLong(0),
            "product_id" : ObjectId("62b4890f4cbb9bc8c947e5ef"),
            "product" : [ 
                {
                    "_id" : ObjectId("62b4890f4cbb9bc8c947e5ef"),
                    "name" : "olive",
                }, 
                {
                    "_id" : ObjectId("62b4890f4cbb9bc8c947e5ef"),
                    "name" : "olive",
                }
            ]
        }, 
        {
            "type" : NumberLong(1),
            "product_id" : ObjectId("62b4890f4cbb9bc8c947e5ed"),
            "product" : [ 
                {
                    "_id" : ObjectId("62b4890f4cbb9bc8c947e5ef"),
                    "name" : "olive",
                }, 
                {
                    "_id" : ObjectId("62b4890f4cbb9bc8c947e5ef"),
                    "name" : "olive",
                }
            ]
        }
    ]
}

Any idea on how to fix the query to have only one product instead of an array of identical ones?

CodePudding user response：

Few small adjustments on the $set phase:

1. product_types, not product_types.product, in order to avoid duplication of the array. In order to nest it anther product add the key product in the $mergeObjects operation.
2. $productInterestData._id instead of $productInterestData.id
3. $$this instead of $this (we need two $ here)
4. $$this.product_id instead of $this.id

db.interests.aggregate([
  {
    $lookup: {
      from: "products",
      localField: "product_types.product_id",
      foreignField: "_id",
      as: "productInterestData"
    }
  },
  {
    $set: {
      product_types: {
        $map: {
          input: "$product_types",
          in: {
            $mergeObjects: [
              "$$this",
              {product:{
                $arrayElemAt: [
                  "$productInterestData",
                  {$indexOfArray: ["$productInterestData._id", "$$this.product_id"]}
                ]
              }}
            ]
          }
        }
      }
    }
  },
  {$unset: "productInterestData"}
])

See how it works on the playground example