Get min value from array of object using aggregate and lookup mongodb

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
}

1 answer

  • answered 2021-10-23 09:08 Tom Slabbaert

    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

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum