SPL assists MongoDB: To filter a JavaScript array based on matches

The data of a collection (named Windows) in the MongoDB database is as follows, with two time fields forming an interval:

[
    {
      "start_time": "2024-05-12T12:00:00Z",
      "end_time": "2024-05-12T14:00:00Z",
      "device_id": "1"
    },
    {
      "start_time": "2024-05-12T07:00:00Z",
      "end_time": "2024-05-12T09:00:00Z",
      "device_id": "2"
    },
    {
      "start_time": "2024-05-12T01:00:00Z",
      "end_time": "2024-05-12T03:00:00Z",
      "device_id": "3"
    }
]

Requirement: Use the following JSON string as a parameter to filter out the records (usually multiple) that meet the criteria in the collection above.
The filtering condition is that the device IDs are equal and the timestamp falls within the interval between start_time and end_time (closed at both ends).

{ device_id: 1, timestamp: "2024-05-12T13:00:00Z"}, 
{ device_id: 3, timestamp: "2024-05-12T13:00:00Z"},
{ device_id: 4, timestamp: "2024-05-12T13:00:00Z"}

The expected results are as follows:

[{ device_id: 1, timestamp: "2024-05-13T13:00:00Z"}]

The syntax of MongoDB query itself is quite cumbersome, and a simple filtering is very long. Here is a relatively concise way to write it:

db.windows.aggregate([
  {
    $set: {
      timestamps: {
        $filter: {
          input: [
            {
              device_id: 1,
              timestamp: "2024-05-12T13:00:00Z"
            },
            {
              device_id: 3,
              timestamp: "2024-05-12T13:00:00Z"
            },
            {
              device_id: 4,
              timestamp: "2024-05-12T13:00:00Z"
            }
          ],
          cond: {
            $and: [
              {
                $eq: [
                  "$device_id",
                  {
                    $toString: "$$this.device_id"
                  }
                ]
              },
              {
                $lte: [
                  "$start_time",
                  "$$this.timestamp"
                ]
              },
              {
                $gte: [
                  "$end_time",
                  "$$this.timestamp"
                ]
              }
            ]
          }
        }
      }
    }
  },
  {
    // default for preserveNullAndEmptyArrays is false anyway
    $unwind: "$timestamps"
  },
  {
    $replaceWith: "$timestamps"
  }
])

SPL has a concise syntax similar to SQL and also supports associated filtering, which can implement it in one line:

SPL code

The first three lines are readings, the fourth line is parameters, the fifth line is query code, and the last line returns the results to JSON format.

esProc SPL – Free Trial Available, Download Now!✨👉🏻 esProc SPL FREE Download