This is my third attempt at this. I cancelled the previous replies I was going to post because I was finding fundamental flaws that would make each of my previous solutions to fail. Now I got it. It is rough on the edge and would need clean-up. But keeping some of the fields until clean-up helps debugging and understanding.
The approach is that for each reading, I $lookup for all reading that falls within the following 1 hour. Using $facet, I split the readings withing the same hour into same tmp and different tmp.
Start with some variable to make the code cleaner:
collection_name = "sensors"
stable_period = { "amount" : 1 , "unit" : "hour" }
First stage is $unwind as tmp is an array.
_unwind_tmp = { "$unwind" : "$tmp" }
Then, we set a temporary field in the document that is the end of the stable period.
/* note that I use the variable stable_period to set arguments
to the $dateAdd so the field names in stable_period must match
the one used by $dateAdd
*/
_set_end = { "$set" : {
"_end" : { "$dateAdd" : {
"startDate" : "$createdOn" ,
...stable_period
} }
} }
The meat of the whole thing is a $lookup into itself to find other readings that fall withing the stable period (1 hour is this case). Basically we collect all reading withing the stable period and use $facet to split the reading into the same tmp and different tmp.
_lookup = { "$lookup" : {
"from" : collection_name ,
"as" : "_period" ,
"let" : {
"start" : "$createdOn" ,
"end" : "$_end" ,
"tmp" : "$tmp"
} ,
"pipeline" : [
/* the $set here is optional, but I like to keep temporary values for debugging
it was added because the $match after did not work the first time
so I needed to see if I was testing with the appropriate values
*/
{ "$set" : {
"_debug_start" : "$$start" ,
"_debug_end" : "$$end" ,
"_debug_tmp" : "$$tmp"
} } ,
{ "$match" : { "$expr" : { "$and" : [
{ "$gt" : [ "$createdOn" , "$$start" ] } ,
{ "$lte" : [ "$createdOn" , "$$end" ] }
] } } } ,
_unwind_tmp ,
{ "$facet" : {
"_same_tmp" : [ { "$match" : {
"$expr" : { "$eq" : [ "$tmp" , "$$tmp" ] }
} } ] ,
"_diff_tmp" : [
{ "$match" : {
"$expr" : { "$ne" : [ "$tmp" ,"$$tmp" ] }
} } ,
{ "$limit" : 1 }
]
} }
]
} }
Then we $unwind _period because we know there is only 1 document with the 2 facets and it is easier to work with.
_unwind_period = { "$unwind" : "$_period" }
The final $match for stable periods.
_match_stable = { "$match" : {
"_period._diff_tmp.0" : { "$exists" : false }
} }
Running the pipeline
[ _unwind_tmp , _set_end , _lookup , _unwind_period , _match_stable ]
provided the following only 1-hour stable period
{ _id: ObjectId("639ff4b553ba7b7edfc8f922"),
stateId: 795,
tmp: 24.93,
createdOn: 2022-12-14T00:40:42.069Z,
_end: 2022-12-14T01:40:42.069Z,
_period: { _same_tmp: [], _diff_tmp: [] } }
Thanks for the challenge.