How to use 'aggregate' mode in 'mongoDB in' node properly?

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? :slight_smile: ).

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

Hi Marcel .. thats some funky query you wrote there :wink:

Try surrounding your aggregation with brackets [ < aggregation > ] so newMsg.payload will be an array

var newMsg = {};
newMsg.collection = "DemoCollection";
newMsg.payload = [ 
    { "$match" : { "label" : "NX17" } },
    {"$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;
2 Likes

HOLY MOLY! That was it?! Haha, thanks so much, mate!

Cheeers!
Marcel

2 Likes

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.