Passing date values as variables in aggregation

[vsreekar]

I'm unable to pass a date variable to an aggregation created in RestHeart.

Here is sample aggregation created

{
    "aggrs": [
        {
            "stages": [
                    {"_$match": {"_$and": [{"ts": {"_$gte": {"_$date": {"_$var": "start"}}}}, {"ts": {"_$lte": {"_$date": {"_$var": "end"}}}}]} }
            ],
            "type": "pipeline",
            "uri": "VISITS_GLOBAL"
        }   
    ]
}

Here is the get request - http://localhost:8080/test/daily_TENANT_1/_aggrs/VISITS_GLOBAL?avars={"start":"2016-08-20T00:00:00Z","end":"2016-08-21T00:00:00Z"}

There is no error/exception being thrown by the server, but no documents are being returned. There are some valid documents present in the collection.

[ujibang]

I'm not sure where the issue comes from, can you try using epoch time, i.e.

  {"start": 1471651200000}

Anyway the string representation should be supported by mongodb, see https://jira.mongodb.org/browse/SERVER-11813

are you sure that the documents in the collection have ts of date type, ie they are returned by restheart as {"ts": {"$date": ....}}`?

[vsreekar]

Thank you for your response @ujibang

First let me confirm you that documents have ts of type date. Here are few sample records for reference.

http://localhost:8080/test/daily_TENANT_1?filter={ts:{$gte:{$date:1471651200000}}}&filter={ts:{$lte:{$date:1471651300000}}}

{
    _id: "daily_TENANT_1",
    _etag: {
        $oid: "57ba8cb5e84b970580758280"
    },
    _returned: 2,
    _embedded: {
        rh: doc: [{
            _id: "123456:20160820",
            sin: "123456",
            ts: {
                $date: 1471651200000
            }
        },
        {
            _id: "1234567:20160820",
            sin: "1234567",
            ts: {
                $date: 1471651200000
            }
        }]
    }
}

I tried using epoch time, but didn't get back any result. I believe the values passed as part of avars are being processed as string literals.

Please do let me know if you need any further information

[ujibang]

I will check it. I open a bug https://softinstigate.atlassian.net/browse/RH-206 to track the issue

Thanks for reporting,...

[ujibang]

Hi,

I'm looking at this. Currently I found a way to make it working.

The good news is the avars query parameter is parsed correctly (not interpreted as strings). The bad news is that the mongodb java driver parses long numbers as {"$numberLong":"1471651200000"} and then not recognizing them in this form as $date values; if $date value is like{"$date": 1471651200000} then it works.

So the trick is passing the avar directly as a $date object:

Define the aggregation as follows (note the conditions are in the form {"$gte": {"$var": "start"} and are not using $date explicitly). (Note also that prepending the operators with _ is not required anymore).

{
    "aggrs": [
        {
            "stages": [
                    {"$match": {"$and": [{"ts": {"$gte": {"$var": "start"}}}, {"ts": {"$lte": {"$var": "end"}}}]} }
            ],
            "type": "pipeline",
            "uri": "VISITS_GLOBAL"
        }
    ]
}

You can then GET the documents matching the condition with:

GET 127.0.0.1:8080/db/issue151/_aggrs/VISITS_GLOBAL?avars='{"start":{"$date":"2016-08-20T00:00:00Z"},"end":{"$date":"2016-08-21T00:00:00Z"}}'

The response is:

{
    "_embedded": {
        "rh:result": [
            {
                "_etag": {
                    "$oid": "57c45975051e0e10bf35f9bd"
                }, 
                "_id": "123456:20160820", 
                "sin": "123456", 
                "ts": {
                    "$date": 1471651200000
                }
            }, 
            {
                "_etag": {
                    "$oid": "57c45975051e0e10bf35f9bd"
                }, 
                "_id": "1234567:20160820", 
                "sin": "1234567", 
                "ts": {
                    "$date": 1471651200000
                }
            }
        ]
    }, 
    "_returned": 2, 
    "_size": 2, 
    "_total_pages": 1
}

[vsreekar]

Apologies about delayed response @ujibang. We are using the alternate approach you suggested.