Hi,
I'm not sure if I'm doing something wrong (more likely) or if there's a bug with the 'aggregate' mode of the "mongodb in" node from @dceejay and @knolleary's contribution node-red-node-mongodb (less likely? ).
My exemplary mongo collection looks as follows:
db.DemoCollection.save(
{
"label" : "NX17",
"color" : "blue",
"data" : {
"A" : [
23.5,
14.78,
48.58,
58.41
],
"B" : [
43.43,
48.25,
47.08,
41.35
],
"timestamp" : [
NumberLong(1491177600),
NumberLong(1491264000),
NumberLong(1491350400),
NumberLong(1491436800)
]
}
})
Now the working query in mongo db shell looks like this:
db.DemoCollection.aggregate([
{ $match : { label : "NX17" } },
{
$project : {
"_id" : 0,
"label" : 1,
"color" : 1,
"tuple" : {
$map: {
input : {
$filter: {
input : {
$zip: { inputs : [ "$data.A", "$data.B", "$data.timestamp" ] }
},
as: "timeWithIndex",
cond: {
$let: {
vars: {
time: { $arrayElemAt: [ "$$timeWithIndex", 2 ] }
},
in: { $gte: [ "$$time", 1491264000 ] }
}
}
}
},
as: "el",
in: {
"timestamp": { $arrayElemAt: [ "$$el", 2 ] },
"A": { $arrayElemAt: [ "$$el", 0 ] },
"B": { $arrayElemAt: [ "$$el", 1 ] }
}
}
}
}
}
])
which yields:
{
"label" : "NX17",
"color" : "blue",
"tuple" : [
{
"timestamp" : NumberLong(1491264000),
"A" : 14.78,
"B" : 48.25
},
{
"timestamp" : NumberLong(1491350400),
"A" : 48.58,
"B" : 47.08
},
{
"timestamp" : NumberLong(1491436800),
"A" : 58.41,
"B" : 41.35
}
]
}
The message payload which I'm preparing in a function...
var newMsg = {};
newMsg.collection = "DemoCollection";
newMsg.payload = {
"$match" : { "label" : "NX18" }
/*"$project" : {
"_id" : 0,
"color" : 1,
"tuple" : {
"$map": {
"input" : {
"$filter": {
"input" : {
"$zip": { "inputs" : [ "$data.A", "$data.B", "$data.timestamp" ] }
},
"as": "timeWithIndex",
"cond": {
"$let": {
"vars": {
"time": { "$arrayElemAt": [ "$$timeWithIndex", 2 ] }
},
"in": { "$gte": [ "$$time", 1491264000 ] }
}
}
}
},
"as": "el",
"in": {
"ts": { "$arrayElemAt": [ "$$el", 2 ] },
"A": { "$arrayElemAt": [ "$$el", 0 ] },
"B": { "$arrayElemAt": [ "$$el", 1 ] },
}
}
}
}*/
};
return newMsg;
in this small flow:
(in case you wanna import it & try out:)
[{"id":"7f827284.95852c","type":"mongodb in","z":"9ba23ed4.19e7f","mongodb":"bab2560a.6eb3c8","name":"Aggregate","collection":"","operation":"aggregate","x":690,"y":200,"wires":[["ce61a4c3.299968","5f078a55.6949b4","f99c8631.a59ce8"]]},{"id":"306391a1.ffb48e","type":"inject","z":"9ba23ed4.19e7f","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":260,"y":200,"wires":[["199a26c1.9261b9"]]},{"id":"ce61a4c3.299968","type":"debug","z":"9ba23ed4.19e7f","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":970,"y":200,"wires":[]},{"id":"bc7faa93.0fc4d8","type":"ui_button","z":"9ba23ed4.19e7f","name":"Refresh","group":"5a4200da.62c9e","order":3,"width":"3","height":1,"passthru":true,"label":"Refresh Table","tooltip":"Refresh data table","color":"","bgcolor":"","icon":"refresh","payload":"","payloadType":"str","topic":"","x":260,"y":160,"wires":[["199a26c1.9261b9"]]},{"id":"199a26c1.9261b9","type":"function","z":"9ba23ed4.19e7f","name":"set msg.payload","func":"var newMsg = {};\nnewMsg.collection = \"DemoCollection\";\nnewMsg.payload = {\n \"$match\" : { \"label\" : \"NX18\" }\n /*\"$project\" : {\n \"_id\" : 0,\n \"color\" : 1,\n \"tuple\" : {\n \"$map\": {\n input : {\n $filter: {\n input : {\n $zip: { \"inputs\" : [ \"$data.A\", \"$data.B\", \"$data.timestamp\" ] }\n },\n as: \"timeWithIndex\",\n cond: {\n $let: {\n vars: {\n time: { \"$arrayElemAt\": [ \"$$timeWithIndex\", 2 ] }\n },\n in: { \"$gte\": [ \"$$time\", 1491264000 ] }\n }\n }\n }\n },\n as: \"el\", \n in: { \n ts: { $arrayElemAt: [ \"$$el\", 2 ] },\n A: { $arrayElemAt: [ \"$$el\", 0 ] }, \n B: { $arrayElemAt: [ \"$$el\", 1 ] },\n }\n }\n }\n }*/\n};\n\nreturn newMsg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":480,"y":200,"wires":[["7f827284.95852c","358017f9.714fd8"]]},{"id":"358017f9.714fd8","type":"debug","z":"9ba23ed4.19e7f","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":670,"y":160,"wires":[]},{"id":"bab2560a.6eb3c8","type":"mongodb","hostname":"kaspar","topology":"direct","connectOptions":"authSource=admin","port":"27017","db":"SYMBOL_HISTORY","name":"MongoDB"},{"id":"5a4200da.62c9e","type":"ui_group","name":"MongoDB (Test-Area)","tab":"a9b97a36.b1b538","order":1,"disp":true,"width":"20","collapse":false},{"id":"a9b97a36.b1b538","type":"ui_tab","name":"History (Tests)","icon":"poll","order":4,"disabled":false,"hidden":false}]
As you can see, in the above msg.payload I commented out the "$project" part because no matter what I try the result is always the full document being returned. No zipping, no projection, no matching.
Then I came up with the idea to change the $match by filtering the label to 'NX18' instead of 'NX17' just in order to see that there should be an empty payload being returned.
But, this is also not the case - again it yields the full document.
I'm out of options here after trying hours with many different ways to write the whole array and its fields. Can anyone help a lone Desperado?
I would highly appreciate anyone's ideas.
Cheers,
Marcel