Search code examples
couchdbcloudant

how to return the latest record by timestamp?

I have a data set that looks similar to this:

{"user":333,"product":943, "rating":2.025743791177902, "timestamp":1481675659}
{"user":333,"product":3074,"rating":2.1070657532324493,"timestamp":1481675178}
{"user":333,"product":3074,"rating":2.108323259636257, "timestamp":1481673546}
{"user":333,"product":943, "rating":2.0211849667268353,"timestamp":1481675178}
{"user":333,"product":943, "rating":2.041045323231024, "timestamp":1481673546}
{"user":333,"product":119, "rating":2.1832303461543163,"timestamp":1481675659}
{"user":333,"product":119, "rating":2.1937538029700203,"timestamp":1481673546}
{"user":111,"product":123, ...

I would like to query all records for a user (e.g. 333), but only return the latest timestamp:

{"user":333,"product":119, "rating":2.1832303461543163,"timestamp":1481675659}

Is this possible with a map/reduce index? If so, how?

Ideally I would also like to sort by the rating value.

Solution

  • If you create a Map function like this

    function(doc) {
  emit([doc.user, doc.timestamp], null);
}

    an index would be created in user & time order.

    To return the latest (most recent) timestamp for a given user you can query the index with the following parameters:

    startkey=[333,9999999999]
endkey=[333,0]
descending=true
limit=1

    We are querying the index in reverse order, starting with the largest timestamp for that user but limiting the result set size to 1 - the newest entry.