Cloudant: How to create an index for "Sort" function?

Question

The problem I am facing is on creating the correct index to query through my Cloudant database. The JSON data structure I am using looks similar to below.

{
  "customer" : "123",
  "time" : "2014-11-20"
}

I want to sort the documents based on the time. The index query that I used is:

curl -X POST 'https://<user>:<pass>@<user>.cloudant.com/<DB-name>/_index' -d '
{
  "index": { 
    "fields": [
      "customer", 
      "time"
     ] 
  } 
}'

And the Query that I am using is:

curl -X POST 'https://<user>:<pass>@<user>.cloudant.com/<DB-name>/_find' -d '
{
  "selector": { 
    "customer" : "123"
  }, 
  "sort": [
    "time"
  ]
}'

The error code I am getting is "no_usable_index". Can anyone provide some insight into this problem?

Also, what would be different if the time were in the format: "2014-11-20 11:50:00"? Essentially, I am trying to sort based on date and time. Is this possible?

score 3 · Accepted Answer · answered Dec 05 '14 at 11:05

The error message is telling you that there is no index to perform the sorting, or at least it can't find one. To help it find one, sort on customer and then on time, like so:

curl -X POST 'https://<user>:<pass>@<user>.cloudant.com/<DB-name>/_find' -d '
{
  "selector": { 
    "customer" : "123"
  }, 
  "sort": [
    "customer",
    "time"
  ]
}'

This query is functionally identical, but now Cloudant Query will find the index.

Regarding your question about the other time format, the time field would still be treated as a string field for the purposes of sorting. In your case, that means you'll get the expected result.

Cloudant: How to create an index for "Sort" function?

1 Answers1

Linked