使用聚合和查找mongodb從物件陣列中獲取最小值

我有兩個集合properties，property_prices并且關系是一個屬性很多價格。所以我試圖加入他們，然后從property_prices.monthly_unit_price.unit_price. 所以我可以從整個物業定價中獲得帶有價格和最小 unit_price 值的屬性。

Property Collection

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

Property Price Collection 其中monthly_unit_price 有1 月至12 月的物件

{
    "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" }],
        }
    ]
}

基本上我想從 property_prices 中獲取最小值unit_price為property_id1

所以我嘗試使用aggregateandlookup但我無法從property_prices. 這是我嘗試過的

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" }
        }
    },
]);

我期待的結果是

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

uj5u.com熱心網友回復：

您走在正確的軌道上，您只需要多“按摩”檔案結構，因為它是一個嵌套陣列。這是使用$mapand$reduce運算子執行此操作的快速示例。

請注意，我還必須使用 將值轉換為數字型別$toInt，我建議在更新/插入時處理這些型別的事情。

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"
              ]
            }
          }
        }
      }
    }
  }
])

蒙戈游樂場

標籤：MongoDB 猫鼬 nosql 总计的

上一篇：在準備好的陳述句和執行中重復的列名

下一篇：如何在mongodb中存盤代碼片段？