Tuesday, July 5, 2011

Search by multiple keys in CouchDB

While developing a simple Twitter clone on V8CGi + CouchDB stack, we faced an interesting problem.
We have a CouchDB database with users and messages. Each user has many followers and he'd like to see messages from all these
followers. So we need to query tweets database for all documents that have author belonging to the list.
In SQL it's something as simple as:

SELECT * FROM tweets WHERE author IN ("szymon", "wiktor", "marek") SORT BY timestamp;

In CouchDB it turned out to be an open issue https://issues.apache.org/jira/browse/COUCHDB-523

As far as I remember we ended up with running two separate queries. I'd be happy to hear how others solved this!

1 comment:

  1. I don't think the couchdb issue you flagged is the same as what you described.

    This issue https://issues.apache.org/jira/browse/COUCHDB-523 relates to multiple independent ranges. What you're looking for is to select all items with a given key equal to one or more values. The former is a generalization of the latter, but they are not the same. In fact the simpler case is documented to be supported in Couchdb, using the keys query param, in which you specify an array. http://wiki.apache.org/couchdb/HTTP_view_API?action=show&redirect=HttpViewApi#Querying_Options