Complex Mongo query in Python

Question

I have a collection named measurements. In this collection, there are only three fields: id (univoque), tmstamp (univoque) and value. Each row which contains a value greater than 0 is considered an alert. All the alerts that occur from the zero state to the next zero state is considered an episode. I want to query the data in such way that it returns it in episodes format. That is to say, each row is an episode.

In order to make it easier to understand, I'll put an example:

{"id":1, tmstamp:1577644027, value:0}
{"id":2, tmstamp:1577644028, value:0}
{"id":3, tmstamp:1577644029, value:1}
{"id":4, tmstamp:1577644030, value:1}
{"id":5, tmstamp:1577644031, value:2}
{"id":6, tmstamp:1577644032, value:2}
{"id":7, tmstamp:1577644033, value:3}
{"id":8, tmstamp:1577644034, value:2}
{"id":9, tmstamp:1577644035, value:1}
{"id":10, tmstamp:1577644036, value:0}
{"id":11, tmstamp:1577644037, value:1}
{"id":12, tmstamp:1577644038, value:1}
{"id":13, tmstamp:1577644039, value:1}
{"id":14, tmstamp:1577644040, value:0}

Given this data, the episodes would be:

episode1:

{"id":3, tmstamp:1577644029, value:1}
{"id":4, tmstamp:1577644030, value:1}
{"id":5, tmstamp:1577644031, value:2}
{"id":6, tmstamp:1577644032, value:2}
{"id":7, tmstamp:1577644033, value:3}
{"id":8, tmstamp:1577644034, value:2}
{"id":9, tmstamp:1577644035, value:1}

episode2:

{"id":11, tmstamp:1577644037, value:1}
{"id":12, tmstamp:1577644038, value:1}
{"id":13, tmstamp:1577644039, value:1}

My question is: is there any way to query the data in Mongo in order to obtain the result in this format without having to do these operations after the query itself?

Answer 1

You need to combine $facet and Array expression operators.
As @aws_apprentice mentioned, $bucket will do it for you if you know previously zero-state measure id's, as boundaries don't accepts expresions.

So, we need to separate zero-state and non-zero-state data. Let's call them: alerts (value = 0) and episode (value > 1).
For alerts, we store _id of each measurements with alerts into array (we need it to filter episodes). With $indexOfArray and $arrayElemAt we can take next _id_i+1 (filter episodes between _i and _i+1 ids).

ASSUMPTION

I've replaced id to _id to perform aggregation
You know how to translate MongoDB aggregate command in Python syntax

db.measurements.aggregate([
  {
    $facet: {
      alerts: [
        {
          $match: {
            value: 0
          }
        },
        {
          $group: {
            _id: "",
            ids: {
              $push: "$_id"
            }
          }
        }
      ],
      episodes: [
        {
          $match: {
            value: {
              $gt: 0
            }
          }
        }
      ]
    }
  },
  {
    $unwind: "$alerts"
  },
  {
    $addFields: {
      alert_idx: "$alerts.ids"
    }
  },
  {
    $unwind: "$alerts.ids"
  },
  {
    $project: {
      "k": {
        $concat: [
          "Episode",
          {
            $toString: {
              $indexOfArray: [
                "$alert_idx",
                "$alerts.ids"
              ]
            }
          }
        ]
      },
      "v": {
        $filter: {
          input: "$episodes",
          cond: {
            $and: [
              {
                $gt: [
                  "$$this._id",
                  "$alerts.ids"
                ]
              },
              {
                $lt: [
                  "$$this._id",
                  {
                    $arrayElemAt: [
                      "$alert_idx",
                      {
                        $sum: [
                          {
                            $indexOfArray: [
                              "$alert_idx",
                              "$alerts.ids"
                            ]
                          },
                          1
                        ]
                      }
                    ]
                  }
                ]
              }
            ]
          }
        }
      }
    }
  },
  {
    $match: {
      "v": {
        $ne: []
      }
    }
  }
])

MongoPlayground