How to get original value in $group?-CodePudding

How I can get original value of a field in $group operator ? I'm using $group to get a max value in a time range, I also need to get the couple values in there.

Ex:

[{
  time: "2021-01-01T10:15:32.000",
  locationId: ObjectId("6170e536f294af00124d21d7"),
  value: 36,
  lat: "21.12213",
  lng: "82.01929",
  type: "temp"
}, {
  time: "2021-01-01T10:16:32.000",
  locationId: ObjectId("6170e536f294af00124d21d7"),
  value: 23,
  lat: "21.12213",
  lng: "82.01929",
  type: "temp"
}, {
  time: "2021-01-01T10:16:32.000",
  locationId: ObjectId("6170e536f294af00124d21d7"),
  value: 26,
  lat: "21.12213",
  lng: "82.01929",
  type: "humidity"
},{
  time: "2021-01-01T10:16:32.000",
  locationId: ObjectId("6170e536f294af00124d21d7"),
  value: 12,
  lat: "21.12213",
  lng: "82.01929",
  type: "humidity"
}]

This is data (temperature and humidity) of the same location, so lat, lng is same. I used $group to get MAX temperature and humidity of location in the days. so I group by the day only, not included the time, Like below:

[{
  $group: {
    _id: {
      time: {
        $dateToString: {
          format: "%Y-%m-%d",
          date: "$time"
        }
      },
      locId: "$locationId",
      type: "$type"
    },
    maxValue: {
      $max: "$value"
    },
    coordinate: {
      $push: {
        lat: "$lat",
        lng: "$lng"
      }
    }
  }
}]

But if so, temp and humidity is not in the same object because I grouped by type also, so I will group one more time to group by time only.

[{
  $group: {
    _id: {
      time: {
        $dateToString: {
          format: "%Y-%m-%d",
          date: "$time"
        }
      },
      locId: "$locationId",
      type: "$type"
    },
    maxValue: {
      $max: "$value"
    },
    coordinate: {
      $push: {
        lat: "$lat",
        lng: "$lng"
      }
    }
  }
}, {
  $group: {
    _id: "$_id.time",
    maxValues: {
      $push: {
         value: "$maxValue",
         type: "$_id.type"
      }
    },
    coordinates: {
      $last: {
        $slice: ["$coordinate", -1]
      }
    }
  }
}]

Expect result:

[{
  _id: "2021-01-01",
  maxValues: [{
    value: 36,
    type: "temp"
  }, {
    value: 26,
    type: "humidity"
  }],
  cooridinates: { lat: "21.12213" , lng: "82.01929" }
}]

The response is meet my expectation, but you can see, I have to push all lat, log have the same value to an array and in next group, I get the last object in array so push coordinates is redundant and take more performance. That why, sometime it throw an error: PlanExecutor error during aggregation :: caused by :: $push used too much memory and cannot spill to disk

So I need to find a new solution to optimize above query. Or to fix the error. Please give me a solution if you have experience at this. Thanks for advanced

CodePudding user response：

Faced similar issue recently, in your case you can try using $first to keep the constant values in $group as suggested over here mongo group query how to keep fields

{
  $group: {
    _id: {
      time: {
        $dateToString: {
          format: "%Y-%m-%d",
          date: "$time"
        }
      },
      locId: "$locationId",
      type: "$type"
    },
    maxValue: {
      $max: "$value"
    },
    lat:{$first:"$lat"},
    lng:{$first:"$lng"}

  }
}