I have two collections properties and property_prices and the relation is one property many prices. So I am trying to join them and then find min value from property_prices.monthly_unit_price.unit_price. So I could get the Properties with their prices and min unit_price value from entire property pricing.

Property Collection

{
    "_id": "1",
    "status": "Approved",
    "name": "My Property Lake"
}

Property Price Collection where monthly_unit_price have objects from Jan - Dec

{
    "property_prices": [
        {
            "property_id": "1",
            "block_id": "ABC",
            "monthly_unit_price": [{ "month": "Jan", "unit_price": 100 }, { "month": "Dec", "unit_price": "1200" }],
        },
        {
            "property_id": "1",
            "block_id": "DEF",
            "monthly_unit_price": [{ "month": "Jan", "unit_price": "200" }, { "month": "Dec", "unit_price": "2400" }],
        }
    ]
}

Basically I want to get the min value from property_prices unit_price for property_id 1

So I tried using aggregate and lookup but I cant get the min value for entire property from property_prices. Here is what I tried

await Property.aggregate([
    {
        $lookup: {
          from: 'property_prices',
          as: 'property_prices',
          let: { property_id: '$_id' },
          pipeline: [
            {
                $match: {
                    $expr: {
                    $and: [
                        { $eq: ['$property_id', '$$property_id'] },
                        { $eq: ['$status', 'Completed'] },
                    ]
                    }
                }
            },
            
          ]
        },
    },
    {
        $unwind: "$property_prices"
    },
    {
        $group: {
            _id: '$property_prices.property_id',
            minInvestment: { "$min": "$property_prices.monthly_unit_price.unit_price" }
        }
    },
]);

Result I am expecting is

{
    "_id": "1",
    "status": "Approved",
    "name": "My Property Lake",
    "property_prices": [
        {
            "property_id": "1",
            "block_id": "ABC",
            "monthly_unit_price": [{ "month": "Jan", "unit_price": 100 }, { "month": "Dec", "unit_price": "1200" }],
        },
        {
            "property_id": "1",
            "block_id": "DEF",
            "monthly_unit_price": [{ "month": "Jan", "unit_price": "200" }, { "month": "Dec", "unit_price": "2400" }],
        }
    ],
    "minInvestment":100
}

CodePudding user response：

You are on the right track, you just need to "massage" the document structure a little bit more due to the fact it's a nested array. here is a quick example of doing so using the $map and $reduce operators.

Notice I also had to cast the values to number type using $toInt, I recommend these sort of things to be handled at update/insertion time instead.

db.properties.aggregate([
  {
    $lookup: {
      from: "property_prices",
      as: "property_prices",
      let: {
        property_id: "$_id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$property_id",
                    "$$property_id"
                  ]
                },
                {
                  $eq: [
                    "$status",
                    "Completed"
                  ]
                }
              ]
            }
          }
        }
      ]
    }
  },
  {
    $addFields: {
      minInvestment: {
        $min: {
          $reduce: {
            input: {
              $map: {
                input: "$property_prices",
                as: "property",
                in: {
                  $map: {
                    input: "$$property.monthly_unit_price",
                    as: "price",
                    in: {
                      $toInt: "$$price.unit_price"
                    }
                  }
                }
              }
            },
            initialValue: [],
            in: {
              "$concatArrays": [
                "$$value",
                "$$this"
              ]
            }
          }
        }
      }
    }
  }
])

Mongo Playground