CouchDB Mango query - Match any key with array item

Question

I have the following documents:

{
  "_id": "doc1"
  "binds": {
    "subject": {
      "Test1": ["something"]
    },
    "object": {
      "Test2": ["something"]
    }
  },
},
{
  "_id": "doc2"
  "binds": {
    "subject": {
      "Test1": ["something"]
    },
    "object": {
      "Test3": ["something"]
    }
  },
}

I need a Mango selector that retrieves documents where any field inside binds (subject, object etc) has an object with key equals to any values from an array passed as parameter. That is, if keys of binds contains any values of some array it should returns that document.

For instance, consider the array ["Test2"] my selector should retrieve doc1 since binds["subject"]["Test1"] exists; the array ["Test1"] should retrieve doc1 and doc2 and the array ["Test2", "Test3"] should also retrieve doc1 and doc2.

F.Y.I. I am using Node.js with nano lib to access CouchDB API.

Answer 1

I am providing this answer because the luxury of altering document "schema" is not always an option.

With the given document structure this cannot be done with Mango in any reasonable manner. Yes, it can be done, but only when employing very brittle and inefficient practices.

Mango does not provide an efficient means of querying documents for dynamic properties; it does support searching within property values e.g. arrays¹.

Using worst practices, this selector will find docs with binds properties subject and object having properties named Test2 and Test3

{
   "selector": {
      "$or": [
         {
            "binds.subject.Test2": {
               "$exists": true
            }
         },
         {
            "binds.object.Test2": {
               "$exists": true
            }
         },
         {
            "binds.subject.Test3": {
               "$exists": true
            }
         },
         {
            "binds.object.Test3": {
               "$exists": true
            }
         }
      ]
   }
}

Yuk.

The problems

1. The queried property names vary so a Mango index cannot be leveraged (Test37 anyone?)
2. Because of (1) a full index scan (_all_docs) occurs every query
3. Requires programmatic generation of the $or clause
4. Requires a knowledge of the set of property names to query (Test37 anyone?)

The given document structure is a show stopper for a Mango index and query.

This is where map/reduce shines

Consider a view with the map function

function (doc) {
  for(var prop in doc.binds) {
    if(doc.binds.hasOwnProperty(prop)) {
      // prop = subject, object, foo, bar, etc
      var obj = doc.binds[prop];
      for(var objProp in obj) {
        if(obj.hasOwnProperty(objProp)) {
        // objProp = Test1, Test2, Test37, Fubar, etc
          emit(objProp,prop)
        }
      }
    }
  }
}

So the map function creates a view for any docs with a binds property with two nested properties, e.g. binds.subject.Test1, binds.foo.bar.

Given the two documents in the question, this would be the basic view index

id	key	value
doc1	Test1	subject
doc2	Test1	subject
doc1	Test2	object
doc2	Test3	object

And since view queries provide the keys parameter, this query would provide your specific solution using JSON

{
 include_docs: true,
 reduce: false,
 keys: ["Test2","Test3"]
}

Querying that index with cUrl

$ curl -G http://{view endpoint} -d 'include_docs=false' -d 'reduce=false' -d 'keys=["Test2","Test3"]'

would return

{
  "total_rows": 4,
  "offset": 2,
  "rows": [
    {
      "id": "doc1",
      "key": "Test2",
      "value": "object"
    },
    {
      "id": "doc2",
      "key": "Test3",
      "value": "object"
    }
  ]
}

Of course there are options to expand the form and function of such a view by leveraging collation and complex keys, and there's the handy reduce feature.

I've seen commentary that Mango is great for those new to CouchDB due to it's "ease" in creating indexes and the query options, and that map/reduce if for the more seasoned. I believe such comments are well intentioned but misguided; Mango is alluring but has its pitfalls¹. Views do require considerable thought, but hey, that's we're supposed to be doing anyway.

¹⁾ $elemMatch for example require in memory scanning which can be very costly.