I have a collection with the following type of documents:

{
  device: integer,
  date: string,
  time: string,
  voltage: double,
  amperage: double
}

Data is inserted as time series data, and a separate process aggregates and averages results so that this collection has a single document per device every 5 minutes. ie. time is 00:05:00, 00:10:00, etc.

I need to search for a specific group of devices (usually 5-10 at a time). I need the voltage to be >= 27.0, and I need to search for a single date.

That part is easy, but I need to only find data when all 5-10 systems at a time interval meet the 27.0 requirement. I'm not sure how to handle that requirement.

Once I know that, I then need to find the specific grouping of devices that have the lowest summation of the amperage field, and I need to return the time that this occurred.

So, lets assume I am going to search for 5 devices. I need to find the time when all 5 devices have a voltage >= 27.0 and the summation of the amperage field is the lowest.

I'm not sure how to require that all the devices meet the voltage requirement, and then for that group of devices, to then find the time when the amperage summation is the lowest.

Any questions would be great.
Thanks.

CodePudding user response：

You need to use $all operator.

Note: Provide please more information about "the summation of the amperage field is the lowest"

db.collection.aggregate([
  {
    $match: {
      device: { $in: [1, 2, 3] },
      date: "2022/10/01",
      voltage: { $gte: 27.0 }
    }
  },
  {
    $group: {
      _id: "$time",
      device: {
        "$addToSet": "$device"
      },
      amperage: {
        $min: "$amperage"
      },
      root: {
        $push: "$$ROOT"
      }
    }
  },
  {
    $match: {
      device: { $all: [ 1, 2, 3 ] }
    }
  }
])

MongoPlayground