Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

MongoDB aggregate with foreign model inside array

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):

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

{
    "_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?

>Solution :

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

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading