Converting a mongo request from Robo 3T to node-red-node-mongodb

Hi

I have the following request that works fine in Robo 3T:

db.getCollection("medias").find({deleted:true,updated_at:{$gte:ISODate("2022-04-01T00:00:00.000Z"),$lt:ISODate("2022-04-15T00:00:00.000Z")}})

But I don't seem to be able to convert the date-range part in Node-RED using node-red-node-mongodb

I've used a function node in front of the mongodb-in node:


with:

    msg.payload = { 
        deleted: true, 
        updated_at: { 
            $gte: new Date("2022-01-01T00:00:00.000Z"), 
            $lt: new Date("2022-05-15T00:00:00.000Z") 
        } 
    }

    return msg;

If I only have the deleted: true part, the request works fine and I get all records.
But the date-range check doesn't work
I tried with "$gte" and "$lt" but no better luck

I've search for some mongoose sample codes as I believe the node is using that library

Any idea what I've could done wrong ?
Thanks

Hello .. try wrapping your dates with the mongo $date converter
For example :

let startDate = msg.payload.startDate || "2022-01-01T00:00:00.000Z";
let endDate = msg.payload.endDate || "2022-05-15T00:00:00.000Z";

msg.collection = "test"
msg.operation = "find"

msg.payload = { "updated_at": { "$gte": { "$date": startDate }, "$lte": { "$date": endDate } } };

return msg;

ps. the Mongodb nodes internally use the Mongodb Driver and not mongoose .. this may help you with your internet searches, in case there is a difference between the mongoose syntax.

Thanks @UnborN
I don't know why I thought it was mongoose...
Nevertheless I tried the following which I believe matches with your directions:

msg.payload = { 
    "deleted": true, 
    "updated_at": { 
        "$gte": {"$date": "2022-01-01T00:00:00.000Z"},
        "$lt":  {"$date": "2022-05-15T00:00:00.000Z"}
    } 
}
return msg;

But still not items returned.

hmm .. can you confirm from Robo 3T that indeed the field of updated_at is of type Date and not String
also confirm of the deleted field is Boolean

On further testing with some sample data, using new Date should have worked (it does for me)

msg.payload = { "ts": { 
"$gte": new Date("1984-03-05T13:00:00.000Z"), 
"$lte": new Date("1984-03-05T14:00:00.000Z") } 
};

[EDIT]
if you can export 20-30 records from your db collection and share as a .txt or .json file on the forum so we can replicate it

Hi

Regarding types, yes they are see below:
For the dataset, let me double check that I'm allowed to share
I will try again with Date() later today

Thanks

Sample data set for a little more than 30 documents
sampledocs.json (32.6 KB)

I loaded your sample data in a test collection and its working fine with new Date

Example Flow (produces a result of 9 records out of 37)

[{"id":"667e872dec7def34","type":"inject","z":"54efb553244c241f","name":"Inject","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":210,"y":900,"wires":[["790e11e0e3f797ee"]]},{"id":"790e11e0e3f797ee","type":"function","z":"54efb553244c241f","name":"Search between dates","func":"\n\nlet startDate = new Date(\"2022-02-23T10:17:49.268+00:00\");\nlet endDate = new Date(\"2022-02-23T16:04:15.590+00:00\");\n\nmsg.collection = \"test\"\nmsg.operation = \"find\"\n\nmsg.payload = {\n    \"deleted\": true,\n    \"updated_at\": { \"$gte\": startDate, \"$lte\": endDate }\n};\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[{"var":"mongodb","module":"mongodb"}],"x":440,"y":900,"wires":[["0cc1e5d2b9d5ea77"]]},{"id":"0cc1e5d2b9d5ea77","type":"mongodb in","z":"54efb553244c241f","mongodb":"39adf9d0.a8c876","name":"DB","collection":"test","operation":"find","x":670,"y":900,"wires":[["6460508f49dacb36"]]},{"id":"6460508f49dacb36","type":"debug","z":"54efb553244c241f","name":"Debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":830,"y":900,"wires":[]},{"id":"39adf9d0.a8c876","type":"mongodb","hostname":"localhost","topology":"direct","connectOptions":"useUnifiedTopology=true","port":"27017","db":"nodered","name":""}]

image

ps. change the mongo config settings to match your db

Looks like I'm missing something
Could you please explain why you have this ?
I don't have it

Am I supposed to manually install the mongodb driver ?
shouldn't it have been installed as a dependency when installing node-red-node-mongodb

.. you can press the x to remove the importing of the mongodb module ..
its not needed. i just added it when i was experimenting with the ISODate because that is an internal mongodb function (like ObjectId)

ok, so I removed it and same result :weary:

I don't understand what I could be missing
request with only "deleted:true" is ok, it's adding the date range that breaks everything

Oups sorry
it is working with your flow.
Great that give me some points to compare.
Many thanks
I'll let you know what was wrong

phew .. im glad because i was running out of ideas :wink:

check you if the double quotes are in the right place "deleted" : true

THe double quotes were not exact code, should have been back-quote to make it "code style"

So I don't understand because now the initial code in my initial message is working fine
It works fine with or without quotes (looks like Javascript doesn't requires keys to be in quotes)

I don't understand what was wrong and why it now works. Will remains a big mystery.
Thank you a lot for taking some time to help me and trying out on your own system.

image

1 Like