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;
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:
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"]
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"
}
}
Use startkey
and endkey
. This way you can decide your date range at runtime without slowing down your query.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With