I have the following document in MongoDB

{
    "product_id": "10001"
    "product_name": "Banana"
    "product_date": "2022-10-20T00:00:00.000 00:00"
    "product_price": 255.15
    "dates": {
        "2022-10-10": {
            "recorded_price": 195.15
        },
        "2022-10-15": {
            "recorded_price": 230.20
        },
        "2022-10-20": {
            "recorded_price": 255.20
        }
    }
}

I would like to add a new field named "min_7day_price" which would select the minimum price from the date object in the past 7 days.

Something like this:

{
    "product_id": "10001"
    "product_name": "Banana"
    "product_date": "2022-10-20T00:00:00.000 00:00"
    "product_price": 255.15
    "dates": {
        "2022-10-10": {
            "recorded_price": 195.15
        },
        "2022-10-15": {
            "recorded_price": 230.20
        },
        "2022-10-20": {
            "recorded_price": 255.20
        }
    },
    "min_7day_price": 230.20
}

I tried using aggregation to create a new field and convert the object to an array but I can't filter the values inside.

{
  "min_7day_price": {
    $objectToArray: "$dates"
  }
}

CodePudding user response：

One option is to use update with pipeline:

1. Convert the dictionary to array
2. Use $reduce to keep only one item from it, by iterating and comparing the current item: $$this to the best item so far: $$value
3. Format the answer

db.collection.update({},
[
  {$set: {datesArr: {$objectToArray: "$dates"}}},
  {$set: {
      datesArr: {
        $reduce: {
          input: "$datesArr",
          initialValue: {
            k: {$dateAdd: {startDate: "$$NOW", amount: -7, unit: "day"}},
            v: {recorded_price: {$max: "$datesArr.v.recorded_price"}}
          },
          in: {
            $cond: [
              {$and: [
                  {$gte: [{$dateFromString: {dateString: "$$this.k"}}, "$$value.k"]},
                  {$lte: ["$$this.v.recorded_price", "$$value.v.recorded_price"]}
              ]},
              {
                k: {$dateFromString: {dateString: "$$this.k"}},
                v: "$$this.v.recorded_price"
              },
              "$$value"
            ]
          }
        }
      }
    }
  },
  {$set: {min_7day_price: "$datesArr.v", datesArr: "$$REMOVE"}}
])

See how it works on the playground example