Getting the latest (timestamp wise) value from cloudant query

Question

I have a cloudant DB where each document looks like:

{
  "_id": "2015-11-20_attr_00",
  "key": "attr",
  "value": "00",
  "employeeCount": 12,
  "timestamp": "2015-11-20T18:16:05.366Z",
  "epocTimestampMillis": 1448043365366,
  "docType": "attrCounts"
}

For a given attribute there is an employee count. As you can see I have a record for the same attribute every day. I am trying to create a view or index that will give me the latest record for this attribute. Meaning if I inserted a record on 2015-10-30 and another on 2015-11-10, then the one that is returned to me is just employee count for the record with timestamp 2015-11-10.

I have tried view, but I am getting all the entries for each attribute not just the latest. I did not look at indexes because I thought they do not get pre calculated. I will be querying this from client side, so having it pre calculated (like views are) is important.

Any guidance would be most appreciated. thank you

Answer 1

1. I created a test database you can see here. Just make sure your when you insert your JSON document into Cloudant (or CouchDB), your timestamps are not strings but JavaScript data objects:

   https://examples.cloudant.com/latestdocs/_all_docs?include_docs=true

2. I built a search index like this (name the design doc "summary" and the search index "latest"): function (doc) { if ( doc.docType == "totalEmployeeCounts" && doc.key == "div") { index("division", doc.value, {"store": true}); index("timestamp", doc.timestamp, {"store": true}); } }

3. Then here's a query that will return only the latest record for each division. Note that the limit value will apply to each group, so with limit=1, if there are 4 groups you will get 4 documents not 1.

   https://examples.cloudant.com/latestdocs/_design/summary/_search/latest?q=*:*&limit=1&group_field=division&include_docs=true&sort_field=-timestamp