CouchDB query to get the doc with MAX timestamp

Question

My CouchDB document format as below and based on the price changes there can be multiple documents with same product_id & store_id

{ "_id": "6b645d3b173b4776db38eb9fe6014a4c", "_rev": "1-86a1d9f0af09beaa38b6fbc3095f06a8", "product_id": "6b645d3b173b4776db38eb9fe60148ab", "store_id": "0364e82c13b66325ee86f99f53049d39", "price": "12000", "currency": "AUD_$", "time": 1579000390326 }

and I need to get the latest document (by time - the timestamp) for given product_id & store_id

For this, with my current solution I have to do two queries as below;

1. To get the latest timestamp. This returns the latest timestamp for given product_id & store_id

   "max_time_by_product_store_id": { "reduce": "function(keys, values) {var ids = [] values.forEach(function(time) { if (!isNaN(time)){ ids.push(time); } }); return Math.max.apply(Math, ids) }", "map": "function (doc) {emit([doc.store_id, doc.product_id], doc.time);}" }

2. Based on the latest timestamp, again I query to get the document with three parameters that are store_id, product_id & time as below,

   "store_product_time": { "map": "function (doc) { emit([doc.store_id, doc.product_id, doc.time]); }" }

This works perfectly for me but my problem is I need to do two DB queries to get the document and looking for a solution to fetch the document within one DB query.

In CouchDB selector also has no way to get the document by MAX value.

Answer 1

With CouchDB's /db/_find, you can descending sort the result and limit the result to one document as follows:

{
   "selector": {
      "_id": {
         "$gt": null
      }
   },
   "sort": [
      {
         "time": "desc"
      }
   ],
   "limit": 1
}

CURL

curl -H 'Content-Type: application/json' -X POST http://localhost:5984/<db>/_find -d '{"selector":{"_id":{"$gt":null}},"sort":[{"time": "desc"}],"limit": 1}'

Please note that an index must previously be created for the sort field time (see /db/_index).