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

**URL:** <https://discourse.nodered.org/t/how-to-use-aggregate-mode-in-mongodb-in-node-properly/40196>\
**Category:** General\
**Created:** [1 February 2021 21:26 UTC](https://discourse.nodered.org/t/how-to-use-aggregate-mode-in-mongodb-in-node-properly/40196 "2021-02-01T21:26:21Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![CreativeWarlock](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/creativewarlock/32/102051_2.png) [@CreativeWarlock](https://discourse.nodered.org/u/CreativeWarlock)\
**Post date:** [1 February 2021 21:26 UTC](https://discourse.nodered.org/t/how-to-use-aggregate-mode-in-mongodb-in-node-properly/40196/1 "2021-02-01T21:26:22Z")

</div>

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](https://flows.nodered.org/node/node-red-node-mongodb) (less likely? 🙂 ).

My exemplary mongo collection looks as follows:

```auto
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:

```auto
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:

```auto
{
    "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...

```auto
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:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/5/4583a8165478238194539020db5f3b047f7e6cdf.png)

```auto
(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

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [1 February 2021 21:57 UTC](https://discourse.nodered.org/t/how-to-use-aggregate-mode-in-mongodb-in-node-properly/40196/2 "2021-02-01T21:57:00Z")

</div>

Hi Marcel .. thats some funky query you wrote there 😉

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

```auto
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;

```

---

<div class="post-metadata">

**Author:** ![CreativeWarlock](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/creativewarlock/32/102051_2.png) [@CreativeWarlock](https://discourse.nodered.org/u/CreativeWarlock)\
**Post date:** [1 February 2021 22:03 UTC](https://discourse.nodered.org/t/how-to-use-aggregate-mode-in-mongodb-in-node-properly/40196/3 "2021-02-01T22:03:55Z")

</div>

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

Cheeers!  
Marcel

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [15 February 2021 22:04 UTC](https://discourse.nodered.org/t/how-to-use-aggregate-mode-in-mongodb-in-node-properly/40196/4 "2021-02-15T22:04:47Z")

</div>

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