CODEWITHSUNDEEP
node.jsdatabasesortingcouchdbcouchdb-nano
mapmath
mapmath

Reputation: 1532

Index & sort with newly introduced field

I have a document structure as below:

{
  "_id": "6ed3d427045228dce9e3b38d111cb046",
  "_rev": "1-952bddae5572fbf2b0ce4a165c19c98b",
  "type": "USER",
  "name": "ABC",
  "email": "[email protected]",
  "phone": "024578963"
}

This is NodeJS app and I am using the couchdb-nano. For the sorting purpose (like getting data for Pagination), I have created an index for the above fields (name, email, phone).

A new field called created_date has been introduced to the documents and all the latest documents contains the created_date as below:

{
  "_id": "6ed3d427045228dce9e3b38d111cb046",
  "_rev": "1-952bddae5572fbf2b0ce4a165c19c98b",
  "type": "USER",
  "name": "ABC",
  "email": "[email protected]",
  "phone": "024578963",
  "created_date": 1570528394252
}

So that all the latest documents since a certain time consist the created_date and old documents has no created_date field in the document.

When I try to sort like below:

const q = {
      selector : {
        type: { "$eq": "USER" },
      },
      fields : ['name', 'email', 'phone', 'created_date'],
      limit: 100,
      skip: 10,
      sort : [{"created_date":"asc"}]
    };
await prodresdb.find(q);

I will not be able to get old documents that are not consist the created_date. This is because of created_date index will not index the old documents since those has no created_date field.

I am sure someone else have this problem too and need to figure out... How to overcome this? Is there any CouchDB way to fix this? What is the best practice for such kind of problem.

Upvotes: 0

Views: 256

Answers (1)

uminder
uminder

Reputation: 26190

I did a few tests and found that CouchDB simply doesn't include documents in the _find result if they don't contain the field (attribute) specified in sort. In my opinion, this is a bug since sort unexpectedly reduces the filtered result. This is especially annoying when using CouchDB together with PouchDB because the latter works as expected (also returns documents with missing attributes).

Adding the missing attribute to existing documents as suggested in a comment above may solve your problem. In many cases however (i.e. for number and boolean attributes), it's not a viable solution because the document content may be falsified.

The short answer to your question: In many cases, there's no straight forward CouchDB way to fix this.

UPDATE:

It's interesting that similar product Couchbase Server addresses this issue - for full text search results - by allowing for fine-grained control over the sort-procedure through the missing field.

missing: Specifies the sort-procedure for documents with a missing value in a field specified for sorting. The value of missing can be first, in which case results with missing values appear before other results; or last (the default), in which case they appear after.

Upvotes: 1

Related Questions