CouchDB mango ignoring index sort order

Question

I am using Hyperledger Fabric with CouchDB (version 2.2.0, the one that comes with the hyperledger/fabric-couchdb docker image). There is a limitation on Fabric which does not allow to specify sort array for mango queries, so it has to be done with indexes.

The problem I am facing is that no matter if I specify sort "asc" or "desc" fot the index fields, the query results always come in the same order.

The index I am trying to create (also I would rather use assetType as partial index selector, but had no success on that too):

{
    "index": {
      "fields": [
        {"assetType": "desc"},
        {"originalCustomer.document":"desc"}, 
        {"transactionDate":"desc"}
      ]
    },
    "ddoc": "date-index",
    "name": "date-index",
    "type": "json"
 }

Query I am running

{
    "selector": {
        "assetType": "receivable",
        "originalCustomer.document": "1",
        "transactionDate": {
            "$gt":"1900-01-01"
        }
    },
    "use_index": ["date-index"]
 }

_explain result

{
    "dbname": "testdb",
    "index": {
        "ddoc": "_design/date-index",
        "name": "date-index",
        "type": "json",
        "def": {
            "fields": [{"assetType": "asc"},{"originalCustomer.document":"asc"},{"transactionDate": "asc"}]
        }
    },
    "selector": {
        "$and": [
            {"assetType": {"$eq": "receivable"}},
            {"originalCustomer.document": {"$eq": "1"}},
            {"transactionDate": {"$gt": "1900-01-01"}}
        ]
    },
    "opts": {
        ...
    },
    "limit": 25,
    "skip": 0,
    "fields": "all_fields",
    "mrargs": {
        "include_docs": true,
        "view_type": "map",
        "reduce": false,
        "start_key": [
            "receivable",
            "1",
            "1900-01-01"
        ],
        "end_key": [
            "receivable",
            "1",
            "<MAX>"
        ],
        "direction": "fwd",
        "stable": false,
        "update": true,
        "conflicts": "undefined"
    }
}

the same _find result is produced no matter if I use "asc" or "desc" on the index. I am expecting transactionDate "2019-01-02" to be the first on the list, given the descending ordering (I removed non related fields for brevity)

{
    "docs": [
        {
            "assetType": "receivable",
            "originalCustomer": {"document": "1"},
            "transactionDate": "2019-01-01"
        },
        {
            "assetType": "receivable",
            "originalCustomer": {"document": "1"},
            "transactionDate": "2019-01-01"
        },
        {
            "assetType": "receivable",
            "originalCustomer": {"document": "1"},
            "transactionDate": "2019-01-01"
        },
        {
            "assetType": "receivable",
            "originalCustomer": {"document": "1"},
            "transactionDate": "2019-01-02"
        },
        {
            "assetType": "receivable",
            "originalCustomer": {"document": "1"},
            "transactionDate": "2019-01-02"
        }
    ],
    "bookmark": "..."
}

score 0 · Answer 1 · answered Jun 13 '19 at 08:52

Having a closer look at the CouchDB API Reference for /db/_index I see that the index object is supposed to be a JSON array of field names following the sort syntax. The index object in the example request on that same reference page however doesn't include the sort direction. In such cases, the default 'asc' is applied.

Since you're using your index for sorting in both direction, no need to specify the direction. It would look as follows.

{
    "index": {
      "fields": ["assetType", "originalCustomer.document", "transactionDate" ]
    },
    ...
}

The desired sorting of individual results needs to be specified when requesting the data through /db/_find. Your query must contain a sort object that follows the sort syntax. In order to obtain a descending sorted result for example, the query could look as follows.

{
    "selector": {
        "assetType": "receivable",
        "originalCustomer.document": "1",
        "transactionDate": {
            "$gt":"1900-01-01"
        }
    },
    "fields": ["assetType", "originalCustomer.document", "transactionDate" ],
    "sort": [
        {"assetType": "desc"},
        {"originalCustomer.document":"desc"}, 
        {"transactionDate":"desc"}
    ]
 }

Thank you for the answer. But as I said, I can't specify the sort order in the _find command. It is a limitation of Hyperledger Fabric. And according to the [CouchDB docs](https://docs.couchdb.org/en/2.2.0/api/database/find.html#db-index), I can do it in the index creation: "The index object is a JSON array of field names following the [sort syntax](https://docs.couchdb.org/en/2.2.0/api/database/find.html#find-sort) ...". In the sort syntax link it is saying I can use {"fieldName1": "desc"} — Victor Machado, Jun 14 '19 at 00:59

CouchDB mango ignoring index sort order

1 Answers1