CouchDB version of SELECT ALL where field1=value1 and field2=value2

Question

I have a small CouchDB table with the following document structure:

{
    "_id": "22e5cfffa3d363bb1dd3f2293e002953",
    "idRubric": "9bf94452c27908f241ab559d2a0d46c5",
    "category": "open",
    "yada yada" : "for the rest of the fields"
 }

There are 4 possible values of category and (currently) 41 possible values of idRubric. I'm attempting to create a view which allows me to select only those documents where idRubric and category simultaneously match a given value. My current map function looks like this:

{
   "_id": "_design/views",
   "_rev": "40-97b9c83b8016e4f71738bb64cbe6c754",
   "views": {
       "byRubricCategory": {
           "map": "function (doc) { if (doc.idRubric && doc.category) {emit(doc.idRubric, doc.category); } }"
       }
   }
}

The result of this is an array which shows all documents which match ieRubric. If I swap out idRubric for category, then I get all documents matching the category. I've attempted specifying the result as [doc.idRubric, doc.category], however that just returns a null set. Per recommendation, I've updated the view document to output an array:

{
   "_id": "_design/views",
   "views": {
       "byRubricCategory": {
           "map": "function (doc) { if (doc.idRubric && doc.category) {emit([doc.idRubric, doc.category], null); } }"
       }
   }
}

The result of changing the emit to an array is that I now get an empty set as the result:

{
  "total_rows": 16,
  "offset": 0,
  "rows": [

  ]
}

I am not using the reduce function because (with my limited understanding) the objective of map appears to be to reduce the set to a single value, which I don't want, I need the result set which will, at a minimum, have 4 documents and may have 20-30.

I've already been through 20 or topics on CouchDB and this topic, but all appear to refer to multiple key queries where the focus is key1 OR key2. I'm trying to get to a logical AND rather than a logical OR. Thanks.

Representative values when no key is specified:

{
  "total_rows": 16,
  "offset": 0,
  "rows": [
    {
      "id": "22e5cfffa3d363bb1dd3f2293e00380a",
      "key": [
        "9bf94452c27908f241ab559d2a0d46c5",
        "close"
      ],
      "value": null
    },
    {
      "id": "22e5cfffa3d363bb1dd3f2293e0045a7",
      "key": [
        "9bf94452c27908f241ab559d2a0d46c5",
        "content"
      ],
      "value": null
    },
    {
      "id": "22e5cfffa3d363bb1dd3f2293e002953",
      "key": [
        "9bf94452c27908f241ab559d2a0d46c5",
        "open"
      ],
      "value": null
    },
    {
      "id": "22e5cfffa3d363bb1dd3f2293e0088a4",
      "key": [
        "9bf94452c27908f241ab559d2a0d50d6",
        "rising"
      ],
      "value": null
    }
  ]
}

Answer 1

The easiest way to achieve this is with a map function like this:

function (doc) {
  if (doc.idRubric && doc.category) {
    emit([doc.idRubric, doc.category], null);
  }
}

Then you'll have to find the document using startkey and endkey.

startkey = ["a", "b"]
endkey = ["a", "b", {}]

Keep in mind that the keys should be json strings. The "{}" in the endkey means any other object. It will match any object with idRubric equals to "a" and category equals to "b".

Also note, that I don't emit the document along with the keys. There's no real reason to emit the document if you actually want to fetch the document unless you're passing the emitted values to a map reduce. The thing is that you can fetch a view with docs assigned to emitted values. So yo don't really have to emit a value. This will also make the view less big. Imagine that you have a few document that have 10mb of text content. If you emit the whole doc 100 times, your document will actually waste 10mb x 100 times = 1000mb of memory.

Also, instead of the idRubric and category test, you might want to add a "type" to your document to make it more explicit what your object are really.

You could also use this:

http://localhost:5984/rubric_content/_design/views/_view/byRubricCategory?startkey=[%229bf94452c27908f241ab559d2a0d46c5%22,%22content%22]&endkey=[%229bf94452c27908f241ab559d2a0d46c5%22,%22content%22,{}]

Instead of using "key". It is good when you have a set emitting multiple things. Also for some reason, the order in which you emit the values also matter. It will change the order of the result set.

The advantage of my method over the "key" is that you can fetch something like that:

key: ["apple","orange"]
key: ["apple","orange"]
key: ["apple","orange","cherry"]
key: ["apple","lemon"]

A query with "key" = ["apple","cherry"] will return the first 2 keys. A query with startkey=["apple","orange"] and endkey with ["apple","orange",{}] will return the first 3 document as all of those document start with apple and orange. With that in mind, if your startkey and endkey are identical, it will be like "key". It's just that specifying both keys allow you to write more complicated key ranges.

Also one last thing, add "include_docs=true" to your url if you want to fetch the document too.

Answer 2

Oh bother! the answer is key= not keys= I've been testing this in my browser using: http://localhost:5984/rubric_content/_design/views/_view/byRubricCategory?keys=[%229bf94452c27908f241ab559d2a0d46c5%22,%22content%22]

the error is the key specification. Correcting this to: http://localhost:5984/rubric_content/_design/views/_view/byRubricCategory?key=[%229bf94452c27908f241ab559d2a0d46c5%22,%22content%22] resolves the issue. Thank you all for your help and your patience.

Answer 3

emitting [doc.idRubric, doc.category] as your key is the right way to do this. With that composite key and your view doc you should be able to do something like: curl host:5984/dbname/_design/views/_view/byRubricCategory?key=%5B%229bf94452c27908f241ab559d2a0d46c5%22,%22open%22%5D - note that [, " and ] are all CGI escaped.