CouchDB index with $or and $and not working but just $and does

Question

For some reason, I have the following .find() commands and I am getting conflicting indexing errors. Below are examples of one working when I only try to get one type of document. But then if I try to get 2 types of documents it doesn't work for some reason.

Does anyone know why this would be the case?

My index file:

{
  "_id": "_design/index",
  "_rev": "3-ce41abcc481f0a180eb722980d68f103",
  "language": "query",
  "views": {
    "index": {
      "map": {
        "fields": {
          "type": "asc",
          "timestamp": "asc"
        },
        "partial_filter_selector": {}
      },
      "reduce": "_count",
      "options": {
        "def": {
          "fields": [
            "type",
            "timestamp"
          ]
        }
      }
    }
  }
}

Works:

var result = await POUCHDB_DB.find({
  selector:{
    $and: [{type:"document"},{uid:"123"}]
  },
  limit:50, 
  bookmark: bookmark, 
  sort: [{timestamp: "desc"}]
});

Doesn't work:

var result = await POUCHDB_DB.find({
  selector:{
    $or: [
      {$and: [{type:"document"},{uid:"123"}]},
      {$and: [{type:"page"},{uid:"123"}]}
      ]
  },
  limit:50, 
  bookmark: bookmark, 
  sort: [{timestamp: "desc"}]
});

score 2 · Accepted Answer · answered Dec 19 '18 at 17:38

Missing timestamp in selector

In order yo use the timestamp to sort, it must be in your selector. You can simply add it with a "$gte":null.

Redundant condition

The uid seems redundant for your query. For this reason, I would add it into a separate condition.

Finally, in order to use your index, you should create an index with the following fields: uid, timestamp, type (I think this one is optional).

{
  "selector": {
    "$and": [{
        "uid": "123",
        "timestamp": {
          "$gte": null
        }
      },
      {
        "$or": [{
            "type": "document"
          },
          {
            "type": "page"
          }
        ]
      }
    ]
  },
  "sort": [{
    "timestamp": "desc"
  }]
}

Recommandation

If you want your queries to use your index, I would recommend to specify the "use_index" field. If you can version your indexes and queries, it will make the queries faster.

When I did a `createIndex` it ended up working but not when I edited my index for some reason. For some reason when I added another type key in the index `['timestamp', 'type', 'uid', 'document_id']` it didnt work until I did `['timestamp', 'type', 'uid'']` and `['timestamp', 'type', 'document_id']` Does that make sense? Is that how it is supposed to work? — bryan, Dec 21 '18 at 17:10
When you say "It was not working", you mean the index is not getting picked? Normally, the index would query the index with the uid and timestamp since they can easily narrow down the results. Then, they would make in-memory filter for the $or queries (I think). — Alexis Côté, Dec 21 '18 at 19:58