Aggregation with sorting, summations and matches

Working with Data
, ,
1

Hi,

I have a large replica set, and in one of my collections, I have documents similar to the following:

{
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.

Here is what I’m trying to figure out. I have a subset of devices that I need to query for at once, and on the same date. Usually I’ll be searching for 5-10 devices, on the same date, and I need to find the time when all 5-10 devices are >= 27, and the summation of the amperages at that time is the lowest. The end result that I’m looking for is the time that this occurred.

I had been going down the path of searching for these devices with $in, and specifying the voltage, which works fine, but it didn’t guarantee that all 5 devices met that requirement.

Any suggestions on how to accomplish something like this?

Thanks

3

Hi @Mark_Windrim,

Would it be possible for you to provide a small data set that illustrates what you are looking for and the expected output given that data set?

When you say:

  • What’s a “date” it’s an entire day? So that would be all the docs for 5 to 10 devices from 00:00:00 to 23:55:00?
  • What must be >= 27?

I’m struggling to understand the actual query in English already so I’m not in a position to translate it into MQL just yet =).

Oh and are you using the “new” timeseries collection or is it a regular collection?

Cheers,
Maxime.