[SOLVED] CouchDB Where conjunction

CouchDB Where conjunction

I'm stuck with the following problem in couchdb. Imagine the following doc-structure

{name:"test",timestamp:12334567,value:335}

My task is to query all documents on a specific timeframe that have a value bigger than X. In SQL this would be something like

SELECT * FROM doc where timestamp>=X and timestamp < Y and value>Z

The value Z should be dynamic, so I can't just filter before emit.

I was already working with startkey & endkey, but this post How exactly is startkey and endkey working in CouchDB? helped me to understand that this was the wrong approach.

Can someone please give me aa hint how one could solve a problem like this in couchdb?

Thanks in advance.

Solution

You need to create an index for each of the columns you want to query by and perform the logic operation on the client side.

In your case I'd suggest creating the following view:

    function(doc) {
        emit(['by_timestamp', doc.timestamp], null);
        emit(['by_value', doc.value], null);
    }

Now query your view with the following parameters:

?startkey=['by_timestamp', X]&endkey=['by_timestamp', Y]
?startkey=['by_value', Z]&endkey=['by_value', {}]

Having the results, on the client side perform the set intersection of the document ids returned by both queries.

Once you have it you get your results with the POST query to /dbname/_all_docs?include_docs=true, inside the post body put the jsoned dict:

{keys: [...]}

And voila.

Note: as the alternative you can consider having a separate map() function for each index. This way you increase the the query speed for the price of slower indexing of the new/updated documents.