Return unique key values ​​in CouchDB

Question

Is there a way to do the following in CouchDB? How to return unique values ​​for a specific 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" will return ['somevalue']

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

Answer 1

As stated in the final CouchDB manual , you should put the values ​​that you want to be unique in the key, then request the reduction function with group=true .

For example, assuming keyfield is a field with "key1" and "key2" and valuefield is a field with values, your display function might 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 reduction function could be:

 _sum 

Then a query using group=true&startkey=["key2"]&endkey=["key2",{}] gives:

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

Answer 2

Depending on what I see here, (if necessary I change my answer) key1 and key2 look like independent fields, so you will need two separate views.

In my test database, I created 5 simple documents:

 // I've left out fields like _id and _rev for the sake of simplicity { "key1": "somevalue" } { "key1": "somevalue" } { "key2": "anotherval" } { "key2": "andanother" } { "key2": "andanother" } 

Here are two view requests you will need:

 // view for key1 function(doc) { if (doc.key1) { emit("key1", doc.key1); } } // view for key2 function(doc) { if (doc.key2) { emit("key2", doc.key2); } } 

From there, your reduce function can return all the values ​​in the array, simply by doing this:

 function (key, values) { return values; } 

However, you specifically indicated the values ​​are excellent . Since JavaScript does not have a built-in unique() method for arrays, and we cannot use CommonJS modules in view functions, we will have to add our own logic for this. I just copied the first array.unique() function that I found on Google, you can write your own, which is better optimized.

 function (key, values, rereduce) { var o = {}, i, l = values.length, r = []; for (i = 0; i < l; i += 1) { o[values[i]] = values[i]; } for (i in o) { r.push(o[i]); } return r; } 

You will use this reduction function in both views. When you request any of these views, it also abbreviates by default. (You need to explicitly pass reduce=false to get only the results of your map function.

Here are the result sets that you will receive using the map/reduce queries above: (remember that they are two separate queries)

 {"rows":[ {"key":"key1", "value": ["somevalue"]} ]} {"rows":[ {"key": "key2", "value": ["anotherval", "andanother"]} ]}