Cloudant Sorting on a nullable field

Question

I want to sort on a field lets say name which is indexed in Cloudant DB. I am getting all the documents both which has this name field and which doesn't by using the index without sort . But when i try to sort with the name field I am not getting the documents which doesn't have this name field in the doc.

Is there any way to do this by using the query indexes. I want all the documents in sorted order which doesn't have the name field too.

For Example : Below are some documents:

{
 "_id": 1234,
 "classId": "abc",
 "name": "Happa"
}
{
  "_id": 12345,
  "classId": "abc",
  "name": "Prasanth"
}
{
  "_id": 123456,
  "classId": "abc",
}

Below is the Query what i am trying to execute:

{
  "selector": {
    "classId": "abc",
    "name" :{
      "or" : [
        {"$exists": true},{"$exists": false}
      ]
    }
  },
  "sort": [{ "classId": "asc" }, { "name": "asc" }],
  "use_index": "idx-classId_name"
},

I am expecting all the documents to be returned in a sorted order including the document which doesn't have that name field.

Answer 1

Your query makes no sense to me as it stands. You're requesting a listing of documents which either have, or don't have a specific field (meaning every document), and expecting to sort those on this field that may or may not exist. Such an order isn't defined out of the box.

I'd remove the name clause from the selector, sorting only on the classId field which appear in every document, and then do the secondary partial ordering on the client side, so you can decide how you intend to mix in the documents without the name field with those that have it.

Another solution is to use a view instead of a Cloudant Query index. I've not tested this, but hopefully the intent is clear:

function(doc) {
    if (doc && doc.classId) {
        var name = doc.name || "[notfound]";
        emit(doc.classId+"-"+name, 1);
    }
}

which will key the docs on "classId-name" and for docs with no name, a specified sentinel value.

Querying the view should return the documents lexicographically ordered on this compound key (which you can reverse with a query parameter if you wish).