16

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;

Erk
  • 161
  • 1
  • 1
  • 3
  • Duplicate, see http://stackoverflow.com/questions/3079040/range-keys-in-couchdb-views – b_erb Jul 09 '10 at 22:16
  • @PartlyCloudy I don't see how this question is a dupe of your question. Your question generates a bunch of events at regular intervals over some range, whereas this is looking to find all the values between some range. I could just be using your solution incorrectly but I don't think so. – jfenwick Jun 07 '11 at 03:22

4 Answers4

8

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:

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
mikeal
  • 4,037
  • 2
  • 27
  • 22
  • 7
    This wouldn't work because there are two different variables, doc.start and doc.end. – jfenwick Jun 07 '11 at 03:17
  • 1
    You presented an anon function as the answer, but its completely out of context because you didnt say where or how to place it, or what to assign it to, or how or what calls it. I mean do I assign it to a var in the doc? Do I place it in the doc bare and unassigned? Maybe it doent go in the doc at all? Who knows. – ekerner Feb 13 '17 at 15:30
  • 1
    All the apache wiki links have rotted. – Paul Apr 20 '18 at 08:43
4

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"]
Noah
  • 33,851
  • 5
  • 37
  • 32
  • 2
    What magic does the first part of the end key do here? "\ufff0" Is it equal to a date in the infinite future? – mtnpaul Sep 21 '15 at 19:32
  • @noah this does not seem to filter out the end date portion, if the start date matches, so with the end key – wprater Sep 13 '17 at 00:37
2

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"
   }
}
Paul
  • 770
  • 1
  • 7
  • 12
1

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

Umang
  • 5,196
  • 2
  • 25
  • 24