0

I am using "cloudant" database with node-red application. And stores all data via using node-red application. I am using random() string for cloudant document "_id" field, some of document contains system generated "_id" field whereas some document are random() generated string as "_id". All the records having the "createdAt" field which stores "Unix Timestamp" which I am getting through the javscript date obejct i.e "new Date().getTime()".

I created the CloudantDB sort index via using the following design document i.e 

{
    "ddoc": "_design/629170abb04bb25e13d65322e59141dcc5d16317",
    "name": "customIndex",
    "type": "json",
    "def": {
        "fields": [{
            "createdAt": "asc"
        }, {
            "page_id": "asc"
        }, {
            "task_id": "asc"
        }, {
            "_id": "asc"
        }]
    }

and using the following query to get the result i.e 

{
"selector": {
    "createdAt": {
        "$gt": 0
    },
    "$or": [{
        "task_id": {
            "$in": ["1_0"]
        }
    }, {
        "page_id": 1
    }],
    "table": "details"
},
"sort": [{
    "createdAt": "desc"
}],
"limit": 20 , "bookmark": null}

with following cloudantDb endpoint

https://30175cba-a69e-4ff0-9a79-788abcf0f585-bluemix.cloudant.com/master_table/_find

I don't what I am missing but cloudantDB doesn't sort the records as it should be and sometime return random records without any sorting

I am following the following link to implement the cloudantDb find query with sort index i.e

https://developer.ibm.com/clouddataservices/cloudant-query-new/

any kind of help or suggestion is appreciated.

Thanks in advance.

Ram Thakur
  • 11
  • 2

1 Answers1

1

Cloudant records are JSON documents, and the JSON spec does not support JavaScript date types. You're not the only person to hit this problem. See this answer from JasonSmith https://stackoverflow.com/a/4815878/1459475

brobes
  • 706
  • 3
  • 7
  • Ah right. `new Date().getTime()` actually returns a number. I can't access your database, so this is hard for me to test. After looking at this again, I think your problem might be that you manually created that design document, and are trying to run a Cloudant Query selector against it. Unfortunately, that won't work. To run a Cloudant Query selector, you need to create the index through Cloudant Query as well. See https://console.bluemix.net/docs/services/Cloudant/api/cloudant_query.html#creating-an-index . Hopefully that's the problem? – brobes Nov 08 '17 at 15:56
  • Thanks @brobes for you suggestion. I already created CloudantDb Index for selector query and CloudantDb didn't return me any warning or error while running the above query. If I was missing with Index then I think CloudantDb will definitely return some warning or error. – Ram Thakur Nov 13 '17 at 08:03
  • OK, well if that's the case, perhaps you need to familiarize yourself with CouchDB view collation rules? They govern the order in which indexes are sorted, so perhaps it's as simple as that? http://docs.couchdb.org/en/2.1.1/ddocs/views/collation.html#collation-specification – brobes Nov 15 '17 at 18:54