Mongodb 数据库中某 collection(名为 windows) 数据如下，其中 2 个时间字段组成了区间：

[
    {
      "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"
    }
]

要求：以下面 json 串为参数，过滤出上面 collection 中符合条件的记录（通常多条）。
过滤条件为：device_id 相等且 timestamp 落在 start_time 和 end_time 组成的区间中（两端闭）。

{ 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"}

期望结果如下：

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

mongodb query 本身的查询语法就比较繁琐，简单的过滤写起来也很长，以下是一种较简洁的写法：

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 有类似 SQL 的简洁语法，也支持关联的过滤，一行即可实现：

	A
1	=mongo_open@d("mongodb://127.0.0.1:27017/local")
2	=mongo_shell@d(A1, "{'find':'windows','projection':{'_id':0}}")
3	=mongo_close(A1)
4	[{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"} ]
5	=A4.select(A2.select@1(int(A2.device_id)==A4.device_id && A2.start_time<=A4.timestamp && A2.end_time>=A4.timestamp))
6	=json(A5)

前三行是读数，第四行是参数，第五行为查询代码，最后一行将结果转回 json 格式。

问题来源:https://stackoverflow.com/questions/78468218/how-to-filter-a-javascript-array-based-on-matches-from-a-mongodb-collection