How do I do the SQL equivalent of "DISTINCT" in CouchDB?

Question

I have a bunch of MP3 metadata in CouchDB. I want to return every album that is in the MP3 metadata, but no duplicates.

A typical document looks like this:

{
   "_id": "005e16a055ba78589695c583fbcdf7e26064df98",
   "_rev": "2-87aa12c52ee0a406084b09eca6116804",
   "name": "Fifty-Fifty Clown",
   "number": 15,
   "artist": "Cocteau Twins",
   "bitrate": 320,
   "album": "Stars and Topsoil: A Collection (1982-1990)",
   "path": "Cocteau Twins/Stars and Topsoil: A Collection (1982-1990)/15 - Fifty-Fifty Clown.mp3",
   "year": 0,
   "genre": "Shoegaze"
}

Nial

Answer 1

I believe your map/reduce would look something like:

function map(doc) {
    emit(doc.album, null);
}

function reduce(key, values) {
    return null;
}

Remember to query with the extra parameter group=true

Kisner

Could you please explain where the magic is in returning unique values? – Nial

Sure. Remember that the map function accepts one document and calls emit on (key, value) pairs. Because we only care about the album - nothing else - we only emit that. Then, for each distinct key, CouchDB will call reduce(key, values) and use that as the result of your query (again, we simply return null from reduce because we don't care about the value). – Kisner

I think you'll also want to run this query with group=true – Hulburt

Excellent. Worked beautifully. Thanks for the explanation, David. – Nial

How would you instead return one object that contains one property that is a distinct sorted array of albums? I want this because it is less verbose (not containing so many null values). – Parlormaid

Answer 2

Have a look at View Cookbook for SQL Jockeys' Get Unique Values section.

Misfeasor

link is broken... guide.couchdb.org/draft/cookbook.html – Chromate