How to find a change for a field with MongoDB aggregation

For example there is a collection device_status which stores the different states for the devices. The task is to find the devices which passed from off to on at least one time.

{ "device" : "device1", "state" : "on", "ts": ISODate("2018-06-07T17:05:29.340+0000") }
{ "device" : "device2", "state" : "off", "ts": ISODate("2018-06-08T17:05:29.340+0000") }
{ "device" : "device3", "state" : "on", "ts": ISODate("2018-06-09T17:05:29.340+0000")}
{ "device" : "device3", "state" : "shutdown", "ts": ISODate("2018-06-09T18:05:29.340+0000")}
{ "device" : "device2", "state" : "load", "ts": ISODate("2018-06-09T19:05:29.340+0000") }
{ "device" : "device2", "state" : "on", "ts": ISODate("2018-06-10T17:05:29.340+0000") }
{ "device" : "device3", "state" : "off", "ts": ISODate("2018-06-11T17:05:29.340+0000") }
{ "device" : "device1", "state" : "idle", "ts": ISODate("2018-06-11T18:05:29.340+0000") }
{ "device" : "device3", "state" : "on", "ts": ISODate("2018-06-12T17:05:29.340+0000") }
...

The first stage is to sort the data by device and date.

{
    "$sort": {"device": 1, "ts": 1}
}

The second is to group by device and keep states as list.

{
    "$group": {
        "_id": "$device",
        "states": {"$push": "$state"}
    }
}

The third is to find the change from off to on with the $reduce operator.

{
    "$project": {
        "_id": 1,
        "switch": {
            "$reduce": {
                "input": "$states",
                "initialValue": null,
                "in": {
                    "$cond": {
                        "if": {"$and": [{"$eq": ["$$value", null]}, {"$eq": ["$$this", "off"]}]},
                        "then": false,
                        "else": {
                            "$cond": {
                                "if": {"$and": [{"$eq": ["$$value", false]}, {"$eq": ["$$this", "on"]}]},
                                "then": true,
                                "else": "$$value"
                            }
                        }
                    }
                }
            }
        }
    }
}

It’s an equivalent of javascript

["on", "on", "off", "off", "on", "off"].reduce(
    function(acc, value) {
        if(acc===null && value==="off") { 
            return false; 
        } else if(acc===false && value==="on") { 
            return true;
        } else { 
            return acc; 
        }
    }, 
    null);

Then the results can be filtered

{
    "$match": {
        "switch": true
    }
}

The full query looks like:

db.device_status.aggregate([
    {
        "$sort": {"device": 1, "ts": 1}
    },
    {
        "$group": {
            "_id": "$device",
            "states": {"$push": "$state"}
        }
    },
    {
        "$project": {
            "_id": 1,
            "switch": {
                "$reduce": {
                    "input": "$states",
                    "initialValue": null,
                    "in": {
                        "$cond": {
                            "if": {"$and": [{"$eq": ["$$value", null]}, {"$eq": ["$$this", "off"]}]},
                            "then": false,
                            "else": {
                                "$cond": {
                                    "if": {"$and": [{"$eq": ["$$value", false]}, {"$eq": ["$$this", "on"]}]},
                                    "then": true,
                                    "else": "$$value"
                                }
                            }
                        }
                    }
                }
            }
        }
    },
    {
        "$match": {
            "switch": true
        }
    }
])
comments powered by Disqus