sql - Performing a WHERE - IN query in CouchDB

Question

I would like to query for a list of particular documents with one call to CouchDB.

With SQL I would do something like

SELECT *
FROM database.table
WHERE database.table.id
IN (2,4,56);

What is a recipe for doing this in CouchDB by either _id or another field?

Answer 1

You need to use views keys query parameter to get records with keys in specified set.

function(doc){
    emit(doc.table.id, null);
}

And then

GET /db/_design/ddoc_name/_view/by_table_id?keys=[2,4,56]

To retrieve document content in same time just add include_docs=True query parameter to your request.

UPD: Probably, you might be interested to retrieve documents by this reference ids (2,4,56). By default CouchDB views "maps" emitted keys with documents they belongs to. To tweak this behaviour you could use linked documents trick:

function(doc){
    emit(doc.table.id, {'_id': doc.table.id});
}

And now request

GET /db/_design/ddoc_name/_view/by_table_id?keys=[2,4,56]&include_docs=True

will return rows with id field that points to document that holds 2,4 and 56 keys and doc one that contains referenced document content.