node.js - 查询数组中的匹配日期

Question

我创建了一个模型架构，其中包含一些嵌套字段，其中之一是时间戳字段:

{_id: Object_id,
  name: string,
someArray: [{Timestamp: Date, otherFields: ...},{Timestamp: Date, otherFields...},...],
...,
}

时间戳具有以下类型:时间戳:日期 例如(时间戳:2018-06-01T14:57:45.757647Z)

现在，我只想查询数组中的那些文档，这些文档在作为参数从 API url 接收的开始日期和结束日期之间...

/link/Collection/:start.:end.:id

我的路由器 url(带有参数字符串作为查询)如下所示:

http://localhost:6000/link/Collection/2018-06-01T14:50:45.2018-06-01T15:17:45.29

我在 mongoose/node (express) 中检索数据的查询函数如下所示:

exports.list_by_start_end_date_ID = function(req, res) {
    console.log(req.body)
    d_id=req.params.id;
    start = req.params.start;
    end = req.params.end;
    console.log(d_id);
    console.log(start)
    console.log(end)
    console.log(new Date(start));
    console.log(new Date(end));
    //SomeColl.findById(d_id, "myCollection").where("myCollection.Timestamp").gte(new Date(start)).lte(new Date(end))
    SomeColl.findById(d_id, "myCollection",{"myCollection.Timestamp": {"$gte": new Date(start), "$lte": new Date(end)}})
    .exec( function(err, fps) {
        if (err)
            res.send(err);
        res.json(fps);
    });
};

我被退回了:

[{"Timestamp":"2018-06-01T14:57:45.757647Z"...},{"Timestamp":"2018-06-01T15:27:45.757647Z"...},{"Timestamp":"2018-06-01T15:12:45.757647Z"...}]

我没有收到任何错误，我也可以从开始和结束参数创建新的 Date(start) 并且它是正确的，但是如您所见，不应返回时间为 15:27 的文档...

我尝试了查询字符串的两个版本(也注释掉了版本)，我还尝试了我作为参数(开始/结束)传递给 url 的空白 ISO 日期格式字符串。但都没有用。我如何比较 mongoose 中的日期并返回正确的文档？

编辑:我试图通过忽略 db api 操作来找到解决方法，并仅使用 javascript 解析数组的正确文档(子文档)..:

myColl.findById(d_id)
    .exec( function(err, fps) {
        if (err) {
        console.log(err);
            res.send(err);
        }
        else {          
            //console.log(fps["someArray"])
            laenge = fps["someArray"].length;
            console.log(laenge);
            startts = +new Date(start)
            endts = +new Date(end)
            TSarray = []
            console.log(startts,endts)
            for (let doc of fps["someArray"]) {
                ts = +new Date(doc["Timestamp"])
                //console.log(doc)
                if ((ts >= startts) && (ts <= endts)){
                    TSarray.push(doc)   
                    //console.log(TSarray)                              
                }
            }       
            res.json(TSarray)   
        //res.json(fps);
    }
    })//.then((res) => res.json())
};

但是，当我想从数组中获取结果时，出现了 HTTP 304 错误。我还没有找到如何检索单个文档的数组字段的相应子文档(基于过滤条件)。我是否必须使用投影来仅获取数组字段，然后对该数组使用一些过滤条件来获取正确的子文档，或者它通常如何工作？

//编辑2:我尝试使用 mongoDB 聚合框架，但返回 []:

myColl.aggregate([{$match: {"id":d_id},
            someArray: {
                $filter: {
                    input: "$someArray",
                    as: "fp",
                    cond: {$and: [
                        {$gte: [ "$$fp.Timestamp", new Date(start)]},
                        {$lte: [ "$$fp.Timestamp", new Date(end)]}
                    ]}

                }
            }
        }
    }]).exec( function(err, fps) {
        if (err) {
        console.log(err);
            res.send(err);
        }
        else {  
            console.log(fps)
            res.json(fps);
    }
    })}
;

这也不行，这个查询有什么问题吗？如何使用过滤条件在 mongoose 中指定日期范围？

//编辑3:经过 5 天的工作，我终于设法根据时间戳返回了正确的文档。但是，要从 14:00:00 开始获取文件，我必须输入 16:00:00 作为 url 参数……我知道这可能与 UTC 和时区有关……我的 tz 是柏林，所以我认为它的 UTC +2 因为 MongoDB 服务器在纽约我认为...我怎样才能最好地解决这个问题？

这是我的功能:

myColl.findById(d_id, "someArray")
    .exec( function(err, fps) {
        if (err) {
        console.log(err);
            res.send(err);
        }
        else {  
            startts = +new Date(start)
            endts = +new Date(end)
            TSarray = []
            for (let doc of fps["Fahrplanabschnitte"]) {
                ts = + new Date(doc["Timestamp"]                    
                if ((ts >= startts) && (ts <= endts)){
                    TSarray.push(doc)   

                }               
            }   
            //for (let a of TSarray) {console.log(a)};
            res.json(TSarray);
        }
    })
};

Answer 1

你错过了 $elemMatch基本查询和 $filter 上的运算符您尝试使用聚合框架实际上语法不正确。

因此返回与数组中该范围内的日期匹配的文档是:

// Simulating the date values
var start = new Date("2018-06-01"); // otherwise new Date(req.params.start)
var end = new Date("2018-07-01");   // otherwise new Date(req.params.end)

myColl.find({ 
  "_id": req.params.id,
  "someArray": {
    "$elemMatch": {  "$gte": start, "$lt": end  }
  }
}).then( doc => {
  // do something with matched document
}).catch(e => { console.err(e); res.send(e); })

过滤实际返回的数组元素是:

// Simulating the date values
var start = new Date("2018-06-01");
var end = new Date("2018-07-01");

myColl.aggregate([
  { "$match": { 
    "_id": mongoose.Types.ObjectId(req.params.id),
    "someArray": {
      "$elemMatch": { "$gte": start, "$lt": end }
    }
  }},
  { "$project": {
    "name": 1,
    "someArray": {
      "$filter": {
        "input": "$someArray",
        "cond": {
          "$and": [
            { "$gte": [ "$$this.Timestamp", start ] }
            { "$lt": [ "$$this.Timestamp", end ] }
          ]
        }
      }
    }
  }}
]).then( docs => {
  /* remember aggregate returns an array always, so if you expect only one
   * then it's index 0
   *
   * But now the only items in 'someArray` are the matching ones, so you don't need 
   * the code you were writing to just pull out the matching ones
   */
   console.log(docs[0].someArray);
  
}).catch(e => { console.err(e); res.send(e); })

需要注意的是，在 aggregate() 中，您需要实际“转换”ObjectId 值，因为 Mongoose“自动转换”在这里不起作用.通常 Mongoose 从模式中读取以确定如何转换数据，但由于聚合管道“改变了事情”，所以这不会发生。

$elemMatch是因为as the documentation says :

When specifying conditions on more than one field nested in an array of documents, you can specify the query such that either a single document meets these condition or any combination of documents (including a single document) in the array meets the conditions.

Use $elemMatch operator to specify multiple criteria on an array of embedded documents such that at least one embedded document satisfies all the specified criteria.

简而言之$gte和 $lt是一个 AND 条件并计为“两个”，因此简单的“点符号”形式不适用。也是$lt而不是 $lte ，因为“小于”“第二天”比寻找“最后一毫秒”的平等更有意义。

$filter当然，正如它的名字所暗示的那样，并“过滤”实际的数组内容，以便只留下匹配的项目。

---

演示

完整的演示列表创建了两个文档，其中一个只有两个实际匹配日期范围的数组项。第一个查询显示正确的文档与范围匹配。第二个显示数组的“过滤”:

const { Schema, Types: { ObjectId } } = mongoose = require('mongoose');

const uri = 'mongodb://localhost/test';

mongoose.Promise = global.Promise;
mongoose.set('debug',true);

const subSchema = new Schema({
  timestamp: Date,
  other: String
});

const testSchema = new Schema({
  name: String,
  someArray: [subSchema]
});

const Test = mongoose.model('Test', testSchema, 'filtertest');

const log = data => console.log(JSON.stringify(data, undefined, 2));

const startDate = new Date("2018-06-01");
const endDate = new Date("2018-07-01");

(function() {

  mongoose.connect(uri)
    .then(conn =>
      Promise.all(Object.entries(conn.models).map(([k,m]) => m.remove()))
    )
    .then(() =>
      Test.insertMany([
        {
          _id: "5b1522f5cdac0b6da18f7618",
          name: 'A',
          someArray: [
            { timestamp: new Date("2018-06-01"), other: "C" },
            { timestamp: new Date("2018-07-04"), other: "D" },
            { timestamp: new Date("2018-06-10"), other: "E" }
          ]
        },
        {
          _id: "5b1522f5cdac0b6da18f761c",
          name: 'B',
          someArray: [
            { timestamp: new Date("2018-07-04"), other: "D" },
          ]
        }
      ])
    )
    .then(() =>
      Test.find({
        "someArray": {
          "$elemMatch": {
            "timestamp": { "$gte": startDate, "$lt": endDate }
          }
        }
      }).then(docs => log({ docs }))
    )
    .then(() =>
      Test.aggregate([
        { "$match": {
          "_id": ObjectId("5b1522f5cdac0b6da18f7618"),
          "someArray": {
            "$elemMatch": {
              "timestamp": { "$gte": startDate, "$lt": endDate }
            }
          }
        }},
        { "$addFields": {
          "someArray": {
            "$filter": {
              "input": "$someArray",
              "cond": {
                "$and": [
                  { "$gte": [ "$$this.timestamp", startDate ] },
                  { "$lt": [ "$$this.timestamp", endDate ] }
                ]
              }
            }
          }
        }}
      ]).then( filtered => log({ filtered }))
    )
    .catch(e => console.error(e))
    .then(() => mongoose.disconnect());

})()

或者更现代一点的 async/await 语法:

const { Schema, Types: { ObjectId } } = mongoose = require('mongoose');

const uri = 'mongodb://localhost/test';

mongoose.Promise = global.Promise;
mongoose.set('debug',true);

const subSchema = new Schema({
  timestamp: Date,
  other: String
});

const testSchema = new Schema({
  name: String,
  someArray: [subSchema]
});

const Test = mongoose.model('Test', testSchema, 'filtertest');

const log = data => console.log(JSON.stringify(data, undefined, 2));

(async function() {

  try {

    const startDate = new Date("2018-06-01");
    const endDate = new Date("2018-07-01");

    const conn = await mongoose.connect(uri);

    // Clean collections
    await Promise.all(Object.entries(conn.models).map(([k,m]) => m.remove()));

    // Create test items

    await Test.insertMany([
      {
        _id: "5b1522f5cdac0b6da18f7618",
        name: 'A',
        someArray: [
          { timestamp: new Date("2018-06-01"), other: "C" },
          { timestamp: new Date("2018-07-04"), other: "D" },
          { timestamp: new Date("2018-06-10"), other: "E" }
        ]
      },
      {
        _id: "5b1522f5cdac0b6da18f761c",
        name: 'B',
        someArray: [
          { timestamp: new Date("2018-07-04"), other: "D" },
        ]
      }
    ]);



    // Select matching 'documents'
    let docs = await Test.find({
      "someArray": {
        "$elemMatch": {
          "timestamp": { "$gte": startDate, "$lt": endDate }
        }
      }
    });
    log({ docs });

    let filtered = await Test.aggregate([
      { "$match": {
        "_id": ObjectId("5b1522f5cdac0b6da18f7618"),
        "someArray": {
          "$elemMatch": {
            "timestamp": { "$gte": startDate, "$lt": endDate }
          }
        }
      }},
      { "$addFields": {
        "someArray": {
          "$filter": {
            "input": "$someArray",
            "cond": {
              "$and": [
                { "$gte": [ "$$this.timestamp", startDate ] },
                { "$lt": [ "$$this.timestamp", endDate ] }
              ]
            }
          }
        }
      }}
    ]);
    log({ filtered });

    mongoose.disconnect();

  } catch(e) {
    console.error(e)
  } finally {
    process.exit()
  }

})()

两者是一样的并且给出相同的输出:

Mongoose: filtertest.remove({}, {})
Mongoose: filtertest.insertMany([ { _id: 5b1522f5cdac0b6da18f7618, name: 'A', someArray: [ { _id: 5b1526952794447083ababf6, timestamp: 2018-06-01T00:00:00.000Z, other: 'C' }, { _id: 5b1526952794447083ababf5, timestamp: 2018-07-04T00:00:00.000Z, other: 'D' }, { _id: 5b1526952794447083ababf4, timestamp: 2018-06-10T00:00:00.000Z, other: 'E' } ], __v: 0 }, { _id: 5b1522f5cdac0b6da18f761c, name: 'B', someArray: [ { _id: 5b1526952794447083ababf8, timestamp: 2018-07-04T00:00:00.000Z, other: 'D' } ], __v: 0 } ], {})
Mongoose: filtertest.find({ someArray: { '$elemMatch': { timestamp: { '$gte': new Date("Fri, 01 Jun 2018 00:00:00 GMT"), '$lt': new Date("Sun, 01 Jul 2018 00:00:00 GMT") } } } }, { fields: {} })
{
  "docs": [
    {
      "_id": "5b1522f5cdac0b6da18f7618",
      "name": "A",
      "someArray": [
        {
          "_id": "5b1526952794447083ababf6",
          "timestamp": "2018-06-01T00:00:00.000Z",
          "other": "C"
        },
        {
          "_id": "5b1526952794447083ababf5",
          "timestamp": "2018-07-04T00:00:00.000Z",
          "other": "D"
        },
        {
          "_id": "5b1526952794447083ababf4",
          "timestamp": "2018-06-10T00:00:00.000Z",
          "other": "E"
        }
      ],
      "__v": 0
    }
  ]
}
Mongoose: filtertest.aggregate([ { '$match': { _id: 5b1522f5cdac0b6da18f7618, someArray: { '$elemMatch': { timestamp: { '$gte': 2018-06-01T00:00:00.000Z, '$lt': 2018-07-01T00:00:00.000Z } } } } }, { '$addFields': { someArray: { '$filter': { input: '$someArray', cond: { '$and': [ { '$gte': [ '$$this.timestamp', 2018-06-01T00:00:00.000Z ] }, { '$lt': [ '$$this.timestamp', 2018-07-01T00:00:00.000Z ] } ] } } } } } ], {})
{
  "filtered": [
    {
      "_id": "5b1522f5cdac0b6da18f7618",
      "name": "A",
      "someArray": [
        {
          "_id": "5b1526952794447083ababf6",
          "timestamp": "2018-06-01T00:00:00.000Z",
          "other": "C"
        },
        {
          "_id": "5b1526952794447083ababf4",
          "timestamp": "2018-06-10T00:00:00.000Z",
          "other": "E"
        }
      ],
      "__v": 0
    }
  ]
}