Details
-
Bug
-
Resolution: Done
-
Major - P3
-
None
-
3.4.0, 3.4.1
-
None
-
ALL
-
-
Query 2017-01-23
Description
The use of four filters in a $match clause include documents, which should be omitted.
Unless I have made a stupid mistake, all four match filters should be implicitly "$and"'ed together, however the two "$or" clauses seem to cause a conflict, although they are on different fields. I reproduced this on Ubuntu Linux 16.4 with 3.4.1 and on Mac OS X El Capitan with 3.4.0
const a_types = [ |
"Service A1", |
"Service A2" |
];
|
|
|
const b_types = [ |
"Service B1", |
"Service B2" |
];
|
|
|
db.test_in.aggregate([
|
{ "$match" : |
{
|
"REMOTEID" : 123456, |
"$or" : [ |
{ "SERVICE" : { "$in" : a_types } }, |
{ "SERVICE" : { "$in" : b_types } } |
],
|
"$and" : [ |
{ "BEGINTIME" : { "$gte" : ISODate("2016-05-20T00:00:00+01:00") } }, |
{ "BEGINTIME" : { "$lt" : ISODate("2016-07-10T00:00:00+01:00") } } |
],
|
"$or" : [ |
{ "DATAVOLUMEIN" : { "$gt" : 0 } }, |
{ "DATAVOLUMEOUT" : { "$gt" : 0 } } |
]
|
}
|
},
|
{ "$out" : "test_out" } |
]);
|
|
|
db.test_out.find().forEach(printjson);
|
|
There are two arrays of values, which define interesting documents that should be included and are given as criteria for the first {{ $or }} filter step.
The next {{ $and }} filter steps reduces the matching documents for a time range.
The last {{ $or }} step should include only documents where either incoming or outgoing data volume has been recorded.
Test data:
{"_id":{"$oid":"58517e5c6222df30f9c588e4"},"SERVICE":"Service A1","BEGINTIME":{"$date":"2016-06-01T21:47:17.000Z"},"ENDTIME":{"$date":"2016-06-01T21:47:52.000Z"},"REMOTEID":123456,"DATAVOLUMEIN":40135,"DATAVOLUMEOUT":26655}
|
{"_id":{"$oid":"58517f5e6222df30f9d294f5"},"SERVICE":"Service A1","BEGINTIME":{"$date":"2016-06-02T15:15:05.000Z"},"ENDTIME":{"$date":"2016-06-02T15:21:01.000Z"},"REMOTEID":123456,"DATAVOLUMEIN":157422,"DATAVOLUMEOUT":40293}
|
{"_id":{"$oid":"585184036222df30f9232c58"},"SERVICE":"Service A2","BEGINTIME":{"$date":"2016-06-07T14:03:54.000Z"},"ENDTIME":{"$date":"2016-06-07T14:06:48.000Z"},"REMOTEID":123456,"DATAVOLUMEIN":79908,"DATAVOLUMEOUT":53567}
|
{"_id":{"$oid":"585184036222df30f9232cb7"},"SERVICE":"Service A1","BEGINTIME":{"$date":"2016-06-07T14:03:58.000Z"},"ENDTIME":{"$date":"2016-06-07T14:04:00.000Z"},"REMOTEID":123456,"DATAVOLUMEIN":513,"DATAVOLUMEOUT":1415}
|
{"_id":{"$oid":"58518c006222df30f9d9d23e"},"SERVICE":"Service B3","BEGINTIME":{"$date":"2016-06-17T23:00:00.000Z"},"ENDTIME":{"$date":"2016-06-18T16:27:28.000Z"},"REMOTEID":123456,"DATAVOLUMEIN":3028368,"DATAVOLUMEOUT":2998838}
|
{"_id":{"$oid":"58518e9f6222df30f911d51b"},"SERVICE":"Service A1","BEGINTIME":{"$date":"2016-06-21T14:04:05.000Z"},"ENDTIME":{"$date":"2016-06-21T14:09:22.000Z"},"REMOTEID":123456,"DATAVOLUMEIN":88841,"DATAVOLUMEOUT":47682}
|
{"_id":{"$oid":"58518f8f6222df30f92c5513"},"SERVICE":"Service B3","BEGINTIME":{"$date":"2016-06-22T23:00:00.000Z"},"ENDTIME":{"$date":"2016-06-23T13:55:11.000Z"},"REMOTEID":123456,"DATAVOLUMEIN":745738,"DATAVOLUMEOUT":742730}
|
{"_id":{"$oid":"585190036222df30f939268a"},"SERVICE":"Service A2","BEGINTIME":{"$date":"2016-06-23T13:58:16.000Z"},"ENDTIME":{"$date":"2016-06-23T14:04:13.000Z"},"REMOTEID":123456,"DATAVOLUMEIN":80679,"DATAVOLUMEOUT":32105}
|
{"_id":{"$oid":"58516d4c1e92ee2b277445da"},"SERVICE":"Service A1","BEGINTIME":{"$date":"2016-07-05T14:04:33.000Z"},"ENDTIME":{"$date":"2016-07-05T14:05:23.000Z"},"REMOTEID":123456,"DATAVOLUMEIN":46317,"DATAVOLUMEOUT":23222}
|
The output shows that "Service B3" documents are included but they are not desired and not included in the b_types array:
{
|
"_id" : ObjectId("58518c006222df30f9d9d23e"),
|
"SERVICE" : "Service B3",
|
"BEGINTIME" : ISODate("2016-06-17T23:00:00Z"),
|
"ENDTIME" : ISODate("2016-06-18T16:27:28Z"),
|
"REMOTEID" : 123456,
|
"DATAVOLUMEIN" : 3028368,
|
"DATAVOLUMEOUT" : 2998838
|
}
|