couchdbpouchdbcouchdb-2.0couchdb-mango

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


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"}]
});

Solution

  • 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.