Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Querying CouchDB documents between a start date and an end date

I've been trying to figure out how to create a CouchDB view that will let me query all the documents that have a start date greater than A and an end date less than B.

Is this possible in CouchDB or another noSQL document store? Should I scrap it and go back to SQL?

I'm simply trying to do the SQL equivalent of:

SELECT * WHERE [start timestamp] >= doc.start AND [end timestamp] < doc.end;

like image 871
Erk Avatar asked Jul 09 '10 22:07

Erk


4 Answers

Just create a map like this:

function (doc) {emit(doc.timestamp, 1)}

then query the view with:

?descending=true&limit=10&include_docs=true // Get the latest 10 documents

The view will be sorted oldest to latest so descending=true reverses that order.

If you want a specific range.

?startkey="1970-01-01T00:00:00Z"&endkey="1971-01-01T00:00:00Z"

would get you everything in 1970.

These should help:

  • http://wiki.apache.org/couchdb/Introduction_to_CouchDB_views
  • http://wiki.apache.org/couchdb/HttpViewApi
  • http://wiki.apache.org/couchdb/View_collation
like image 69
mikeal Avatar answered Oct 11 '22 08:10

mikeal


Use an array key in your map function

function (doc) {
  var key = [doc.start, doc.end]
  emit(key, doc)
}

Then to get documents with a start date greater then 1970-01-01T00:00:00Z and an end date before 1971-01-01T00:00:00Z use the query

?startkey=["1970-01-01T00:00:00Z", ""]&endkey=["\ufff0", "1971-01-01T00:00:00Z"]
like image 34
Noah Avatar answered Oct 11 '22 09:10

Noah


I was looking for the same thing and stumbled upon this question. With CouchDB 2.0 or higher you have the possibility of using Mango Queries, which includes greater-than and less-than.

A mango query could look like:

"selector": {
   "effectiveDate": {
      "$gte": "2000-04-29T00:00:00.000Z",
      "$lt": "2020-05-01T00:00:00.000Z"
   }
}
like image 24
Paul Avatar answered Oct 11 '22 09:10

Paul


Use startkey and endkey. This way you can decide your date range at runtime without slowing down your query.

like image 1
Umang Avatar answered Oct 11 '22 08:10

Umang