Monday, 4 June 2018

Query for Matching Dates within Array

I have created a model schema with some nested fields in it, one of it is the Timestamp field:

{_id: Object_id,
  name: string,
someArray: [{Timestamp: Date, otherFields: ...},{Timestamp: Date, otherFields...},...],
...,
}

the Timestamp has of type: Timestamp: Date e.g (Timestamp: 2018-06-01T14:57:45.757647Z)

Now, I want to query only those documents from the array, which are between a start and end date that are received as parameters from a API url...

/link/Collection/:start.:end.:id

My router url (with the parameter strings as query) looks like this:

http://localhost:6000/link/Collection/2018-06-01T14:50:45.2018-06-01T15:17:45.29

My query function in mongoose / node (express) to retrieve the data looks like this:

exports.list_by_start_end_date_ID = function(req, res) {
    console.log(req.body)
    d_id=req.params.id;
    start = req.params.start;
    end = req.params.end;
    console.log(d_id);
    console.log(start)
    console.log(end)
    console.log(new Date(start));
    console.log(new Date(end));
    //SomeColl.findById(d_id, "myCollection").where("myCollection.Timestamp").gte(new Date(start)).lte(new Date(end))
    SomeColl.findById(d_id, "myCollection",{"myCollection.Timestamp": {"$gte": new Date(start), "$lte": new Date(end)}})
    .exec( function(err, fps) {
        if (err)
            res.send(err);
        res.json(fps);
    });
};

I get returned:

[{"Timestamp":"2018-06-01T14:57:45.757647Z"...},{"Timestamp":"2018-06-01T15:27:45.757647Z"...},{"Timestamp":"2018-06-01T15:12:45.757647Z"...}]

I don't get any error, I also can create new Date(start) from start and end parameters and it's correct, but as you can see, the document with 15:27 time shouldn't be returned...

I tried out both versions (also commented out version) of the query strings, and I also tried with the blank ISO Date format string that I passed as parameter (start / end) to the url.. but neither worked. How can I compare the dates in mongoose and get the correct documents passed back?

EDIT: I tried to find a workaround by ignoring db api operations, and just parsing the correct documents (subdocuments) of the array with javascript..:

myColl.findById(d_id)
    .exec( function(err, fps) {
        if (err) {
        console.log(err);
            res.send(err);
        }
        else {          
            //console.log(fps["someArray"])
            laenge = fps["someArray"].length;
            console.log(laenge);
            startts = +new Date(start)
            endts = +new Date(end)
            TSarray = []
            console.log(startts,endts)
            for (let doc of fps["someArray"]) {
                ts = +new Date(doc["Timestamp"])
                //console.log(doc)
                if ((ts >= startts) && (ts <= endts)){
                    TSarray.push(doc)   
                    //console.log(TSarray)                              
                }
            }       
            res.json(TSarray)   
        //res.json(fps);
    }
    })//.then((res) => res.json())
};

However, when I want to get the results from the array, I get HTTP 304 error.. I did not find out yet, how to retrieve the corresponding subdocuments (based on a filter criteria) of an array field of one single document.. Do I have to use projection to get only the array field, and then use some filter criteria on that array to get the right subdocuments, or how does it generally work?

//EDIT2: I tried with the mongoDB aggregation framework, but get returned []:

myColl.aggregate([{$match: {"id":d_id},
            someArray: {
                $filter: {
                    input: "$someArray",
                    as: "fp",
                    cond: {$and: [
                        {$gte: [ "$$fp.Timestamp", new Date(start)]},
                        {$lte: [ "$$fp.Timestamp", new Date(end)]}
                    ]}

                }
            }
        }
    }]).exec( function(err, fps) {
        if (err) {
        console.log(err);
            res.send(err);
        }
        else {  
            console.log(fps)
            res.json(fps);
    }
    })}
;

This also does not work, is there anything wrong with that query? How can I specify a date range in mongoose with the filter criteria condition?

//EDIT3: After 5 days of work, I finally managed to get the right documents returned, based on a timestamp. However, to get documents from 14:00:00 o'clock, I have to enter 16:00:00 in the url.. why is that? Here is my function:

myColl.findById(d_id, "someArray")
    .exec( function(err, fps) {
        if (err) {
        console.log(err);
            res.send(err);
        }
        else {  
            startts = +new Date(start)
            endts = +new Date(end)
            TSarray = []
            for (let doc of fps["Fahrplanabschnitte"]) {
                ts = + new Date(doc["Timestamp"]                    
                if ((ts >= startts) && (ts <= endts)){
                    TSarray.push(doc)   

                }               
            }   
            //for (let a of TSarray) {console.log(a)};
            res.json(TSarray);
        }
    })
};



from Query for Matching Dates within Array

No comments:

Post a Comment