CouchDB query no index exists for this sort error-CodePudding

I have seen already this question a lot of times, yet here I am asking same question.

Why do I get this error:

Error running query. Reason: (no_usable_index) No index exists for this sort try indexing by the sort fields.

I am running a blockchain service with hyperledger fabric and I am using CouchDB for the World State. In these data I am having a field for date and I want to sort data by date. This is my Mango query:

{
   "selector": {
      "date": {
         "$gte": null
      },
      "_id": {
         "$gte": null
      }
   },
   "fields": [
      "_id",
      "date"
   ],
   "sort": [
      {
         "date": "asc"
      }
   ]
}

To be mentioned, date is a string. I don't have any idea what is going on here since I tried everything. I tried sorting with any field of the table but the only one to get sorted and working is with the field : _id

I even tried with the _rev field which is again under the _id and still error.

{
   "selector": {
      "_rev": {
         "$gte": null
      },
      "_id": {
         "$gte": null
      }
   },
   "fields": [
      "_id",
      "_rev"
   ],
   "sort": [
      {
         "_id": "asc"
      },
      {
         "_rev": "asc"
      }
   ]
}

This is an example of my data:

{
 "id": "438c5ad0868db83201bdc36edb7705e3081c73821b20d14d4c4251af3e49c04e",
 "key": "438c5ad0868db83201bdc36edb7705e3081c73821b20d14d4c4251af3e49c04e",
 "value": {
  "rev": "1-4f2cc5b932d393a88b3497a3942fff33"
 },
 "doc": {
  "_id": "438c5ad0868db83201bdc36edb7705e3081c73821b20d14d4c4251af3e49c04e",
  "_rev": "1-4f2cc5b932d393a88b3497a3942fff33",
  "company": "greenTea",
  "date": "2022-02-04 15:40:23.337  0000 UTC",
  "fromacc": "Gary",
  "operation": "",
  "txid": "438c5ad0868db83201bdc36edb7705e3081c73821b20d14d4c4251af3e49c04e",
  "type": "transaction",
  "~version": "CgMBBwA="
 }
}

I have tried anything I have found online but I cannot get it to work. Any help or suggestions or even a good documentation would be appreciated.

Thank you

CodePudding user response：

Aside from the full table scan required to gather results, imagine what would happen if there where 10 billion documents to sort in memory.

It is required to create an index on date if you want to sort on the field.

Simply add an index like this.

{
   "index": {
      "fields": [
         "date"
      ]
   },
   "name": "date-index"
}

And you will find this query succeeds:

{
   "selector": {
      "date": {
         "$gte": null
      },
      "_id": {
         "$gte": null
      }
   },
   "fields": [
      "_id",
      "date"
   ]
}

There may be some efficiency with this query

{
   "selector": {
      "date": {
         "$gte": 9
      },
      "_id": {
         "$gte": 9
      }
   },
   "fields": [
      "_id",
      "date"
   ]
}

Since both fields are string types, see 3.2.2.5. Collation Specification