Return unique values by key in CouchDB

couchdbmapreduce

Is there a way to do the following in CouchDB? A way to return unique, distinct values by a given key?

SELECT DISTINCT field FROM table WHERE key="key1"

'key1' => 'somevalue'
'key1' => 'somevalue'
'key2' => 'anotherval'
'key2' => 'andanother'
'key2' => 'andanother'

For example:

http://localhost:5984/database/_design/designdoc/_view/distinctview?key="key1" would return ['somevalue']

http://localhost:5984/database/_design/designdoc/_view/distinctview?key="key2" would return ['anotherval', 'andanother']

Best Answer

As suggested in the CouchDB definitive guide, you should put the values you want to be unique in the key, then query the reduce function with group=true.

For example, given that keyfield is the field with "key1" and "key2" and valuefield is the field with the values, your map function could be:

function(doc) {
  // filter to get only the interesting documents: change as needed
  if (doc.keyfield && doc.valuefield) {
    /*
     * This is the important stuff:
     * 
     *  - by putting both, the key and the value, in the emitted key,
     *    you can filter out duplicates
     *    (simply group the results on the full key);
     * 
     *  - as a bonus, by emitting 1 as the value, you get the number
     *    of duplicates by using the `_sum` reduce function.
     */
    emit([doc.keyfield, doc.valuefield], 1);
  }
}

and your reduce function could be:

_sum

Then querying with group=true&startkey=["key2"]&endkey=["key2",{}] gives:

{"rows":[
{"key":["key2","anotherval"],"value":1},
{"key":["key2","andanother"],"value":2}
]}
Related Topic