Retrieve data by date and time inside of array element

MongoDB Atlas Atlas Search
1

I want to retrieve data by date and time inside of array from EffectiveDate element.
I saving EffectiveDate by Date.now on Mongodb not with ISO date format.
I want to get result EffectiveDate is not greater than current date and then get the lasted date from list result. My collection data is at the following sample.

    "_id": "63a2b0f87a810608e6ca6d95",
    "Templates": [
        {
            "HardwareVer": "minthein@Joseph",
            "SoftwareVer": "11111.0",
            "RevisionNum": "mtw",
            "EffectiveDate": "2022-12-26T08:29:58.470Z",
            "WorkTasks": [
                "63a70c631dbb68ffa7473be1",
                "63aa9a084c60349138c4d5c3"
            ],
            "HasTraveller": true,
            "_id": "63a70a691dbb68ffa7473ba8"
        },
        {
            "HardwareVer": "josephwin",
            "SoftwareVer": "11111.0",
            "RevisionNum": "win",
            "EffectiveDate": "2022-12-26T07:29:58.470Z",
            "WorkTasks": [],
            "HasTraveller": false,
            "_id": "63a70b741dbb68ffa7473bbc"
        },
        {
            "HardwareVer": "A333",
            "SoftwareVer": "333.0",
            "RevisionNum": "221227135521",
            "EffectiveDate": "2023-01-27T05:55:09.148Z",
            "WorkTasks": [],
            "HasTraveller": false,
            "_id": "63aa88c96e0a2601d545e52c"
        }
    ],
}

I wish to get result at the follow

{
    "_id": "63a2b0f87a810608e6ca6d95",
    "Templates": [
        {
            "HardwareVer": "minthein@Joseph",
            "SoftwareVer": "11111.0",
            "RevisionNum": "mtw",
            "EffectiveDate": "2022-12-26T08:29:58.470Z",
            "WorkTasks": [
                "63a70c631dbb68ffa7473be1",
                "63aa9a084c60349138c4d5c3"
            ],
            "HasTraveller": true,
            "_id": "63a70a691dbb68ffa7473ba8"
        },
    ],
} ```

Please help me sir.I had start to learning MongoDB and Nodjs.
2

In your thread Join data collection MongoDB inside an array and two element result add array element, I wrote

Please read Formatting code and log snippets in posts and update your sample documents so that we can cut-n-paste into our system.

In another thread of yours, Add value to new AddFields Array, I wrote

Please read Formatting code and log snippets in posts and update your sample documents so that we can cut-n-paste into our system.

And finally, in Aggregate - return the whole array if query match one element in the array, I replied

Please read Formatting code and log snippets in posts and update your sample documents so that we can cut-n-paste into our system.

This is the third time I write you the above. Help us help you by providing your document in a usable form. Editing documents that are badly formatted is time consuming. It is easier to help others that have well formatted documents so we answer them faster. It is really really easy for you to supply documents that are easy to cut-n-paste into our systems.

Good Luck!

2 Likes
3

I had edit code line format please help me your idea sir.

4

My coding is not working. What is wrong the query ? Please help and thanks

   MainAssyTemplate.findOne(
   {
     _id: req.body.ProductId,
     //   Templates: {
     //     $elemMatch: { EffectiveDate: { $lt: isodate("2023-01-27") } },
     //   },
     "Templates.EffectiveDate": { $lt: new Date("2023-01-27") },
   },
   (err, mainAssyTemplate) => {
     if (err) {
       return res.status(500).json({
         message:
           "Some error occured while retrieving Main Assbly Template [" +
           err.reason +
           "]",
       });
     }
     res.send(mainAssyTemplate);
   }
 );
5

The code looks okay but does not match your sample data. It looks like the field EffectiveDate has a string type rather than a date type.

6

I agree. It seems an ISO date.

In JS once you do const d = new Date(myDate) you can call the method d.toISOString().

And then the query may do what is expected.

7

I had create schema for EffectiveDate using type:Date();

  _id: { type: String, require: true, unique: true },
  Templates: [
    {
      HardwareVer: { type: String, require: true },
      SoftwareVer: { type: String, require: true },
      RevisionNum: { type: String, require: true },
      EffectiveDate: { type: Date, default: Date.now() },
      WorkTasks: { type: Array, default: [], require: true },
      HasTraveller: { type: Boolean, require: true, default: false },
    },
  ],
});
8

Sorry to jump in but what is the error you talk about?

If it is just not finding a doc, are you sure there is a doc with that Id ? Otherwise it will indeed be empty.

Maybe you can link an example in https://mongoplayground.net that reproduces the error…

1 Like
9

I missed that part of the query:

_id : req.body.ProductId

it is good that you did.

Please print the value of req.body.ProductId and share the document that you expect to see.

Do not store your _id as String, a real ObjectId takes less space and is faster.

It looks like you are using mongoose. You might have a schema with type:Date, but nothing stop anyone (outside your mongoose code), to create document with string dates. If your dates were not string there would be not double quotes around the value. Like your true and false of HasTraveller.

Please provide followups in your other threads. That is how we keep the forum useful.

Open Compass and share a screenshot of the result of Schema Analysis.

1 Like
11

Dear @sarah_white,

Please read Formatting code and log snippets in posts and make sure you format your next code accordingly.

Yes, I have mentioned your next code because I have flagged your reply as spam. I am following you so I see all your posts. But good work in this case for putting the spam only after editing the post once. But I still got it.

@+

1 Like