查询数组中的匹配日期 [英] Query for Matching Dates within Array
问题描述
我创建了一个带有一些嵌套字段的模型架构，其中之一是时间戳字段:
I have created a model schema with some nested fields in it, one of it is the Timestamp field:
{_id: Object_id,
name: string,
someArray: [{Timestamp: Date, otherFields: ...},{Timestamp: Date, otherFields...},...],
...,
}
时间戳记具有以下类型:Timestamp: Date
例如(Timestamp: 2018-06-01T14:57:45.757647Z)
the Timestamp has of type: Timestamp: Date
e.g (Timestamp: 2018-06-01T14:57:45.757647Z)
现在，我只想查询数组中位于开始日期和结束日期之间的那些文档，这些文档是从API url作为参数接收的...
Now, I want to query only those documents from the array, which are between a start and end date that are received as parameters from a API url...
/link/Collection/:start.:end.:id
我的路由器网址(带有参数字符串作为查询)如下所示:
My router url (with the parameter strings as query) looks like this:
http://localhost:6000/link/Collection/2018-06-01T14:50:45.2018-06-01T15:17:45.29
我在猫鼬/节点(查询)中的查询函数用于检索数据，如下所示:
My query function in mongoose / node (express) to retrieve the data looks like this:
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时间的文档...
I don't get any error, I also can create new Date(start) from start and end parameters and it's correct, but as you can see, the document with 15:27 time shouldn't be returned...
我尝试了两个版本的查询字符串(也注释掉了版本)，并且还尝试了将空白的ISO日期格式字符串作为参数(开始/结束)传递给url ..，但均无效.如何比较猫鼬中的日期并获得正确的文件回传?
I tried out both versions (also commented out version) of the query strings, and I also tried with the blank ISO Date format string that I passed as parameter (start / end) to the url.. but neither worked. How can I compare the dates in mongoose and get the correct documents passed back?
我试图通过忽略db api操作来找到一种解决方法，而只是使用javascript ..解析数组的正确文档(子文档):
I tried to find a workaround by ignoring db api operations, and just parsing the correct documents (subdocuments) of the array with 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错误. 我还没有找到如何检索一个文档的数组字段的相应子文档(基于过滤条件). 我是否必须使用投影仅获取数组字段，然后对该数组使用一些过滤条件以获取正确的子文档，或者它通常如何工作?
However, when I want to get the results from the array, I get HTTP 304 error.. I did not find out yet, how to retrieve the corresponding subdocuments (based on a filter criteria) of an array field of one single document.. Do I have to use projection to get only the array field, and then use some filter criteria on that array to get the right subdocuments, or how does it generally work?
// 我尝试使用mongoDB聚合框架，但返回了[]:
// I tried with the mongoDB aggregation framework, but get returned []:
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);
}
})}
;
这也不起作用，该查询有什么问题吗?如何使用过滤条件创建猫鼬的日期范围?
This also does not work, is there anything wrong with that query? How can I specify a date range in mongoose with the filter criteria condition?
// 经过5天的工作，我终于设法根据时间戳将正确的文档退回了.但是，要从14:00:00点获取文档，我必须输入16:00:00作为url参数...我知道它可能与UTC和时区有关...我的tz是柏林，所以我认为它作为MongoDB服务器的UTC +2在纽约，我想...我怎样才能最好地适应这个问题?
// After 5 days of work, I finally managed to get the right documents returned, based on a timestamp. However, to get documents from 14:00:00 o'clock, I have to enter 16:00:00 as url parameter... I know it probably has something to do with UTC and timezones... my tz is Berlin, so I think its UTC +2 as MongoDB servers are in NY I think... How can I best accomodate to that problem?
这是我的功能:
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);
}
})
};
推荐答案
您缺少 $elemMatch 运算符，用于基本查询和您尝试使用聚合框架的$filter 实际上语法不正确.
You're missing the $elemMatch operator on the basic query and the $filter you attempted with the aggregation framework actually has incorrect syntax.
因此，返回与数组中该范围内的日期匹配的文档是:
So returning the document matching the dates being within that range in the array is:
// 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); })
过滤要返回的实际数组元素是:
Filtering the actual array elements to be returned is:
// 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值，因为猫鼬的自动投射"在这里不起作用.通常，猫鼬会从模式中读取以确定如何投射数据，但是由于聚合管道改变了事物"，因此不会发生这种情况.
The things to be aware of are that in the aggregate() you need to actually "cast" the ObjectId value, because Mongoose "autocasting" does not work here. Normally mongoose reads from the schema to determine how to cast the data, but since aggregation pipelines "change things" then this does not happen.
$elemMatch 之所以存在，是因为如文档所述:
在嵌套在文档数组中的多个字段上指定条件时，可以指定查询，以使单个文档满足这些条件，或者数组中文档的任何组合(包括单个文档)都满足条件.
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.
使用$ elemMatch运算符在一组嵌入式文档上指定多个条件，以使至少一个嵌入式文档满足所有指定条件.
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.
In short $gte and $lt are an AND condition and count as "two", therefore the simple "dot notation" form does not apply. It's also $lt and not $lte, since it makes more sense to be "less than" the "next day" rather than looking for equality up to the "last millisecond".
$filter 的作用完全是它的名字建议并过滤"实际的数组内容，以便仅保留匹配项.
The $filter of course does exactly what it's name suggests and "filters" the actual array content so that only matching items are left behind.
完整的演示清单将创建两个文档，一个文档只有两个实际上与日期范围匹配的数组项.第一个查询显示正确的文档与范围匹配.第二个显示数组的过滤":
Full demonstration listing creates two documents, one having only two array items which actually match the date range. The first query shows the correct document is matched with the range. The second shows the "filtering" of the array:
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()
}
})()
两者相同，并提供相同的输出:
Both are the same and give the same output:
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
}
]
}
这篇关于查询数组中的匹配日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
