IBM Cloudant DB - get historical data - best way?

1
votes

I'm pretty confused concerning this hip thing called NoSQL, especially CloudantDB by Bluemix. As you know, this DB doesn't store the values chronologically. It's the programmer's task to sort the entries in case he wants the data to.. well.. be sorted.

What I try to achive is to simply get the last let's say 100 values a sensor has sent to Watson IoT (which saves everything in the connected CloudantDB) in an ORDERED way. In the end it would be nice to show them in a D3.css style kind of graph but that's another task. I first need the values in an ordered array.

What I tried so far: I used curl to get the data via PHP from https://averylongID-bluemix.cloudant.com/iotp_orgID_iotdb_2018-01-25/_all_docs?limit=20&include_docs=true';

What I get is an unsorted array of 20 row entries with random timestamps. The last 20 entries in the DB. But not in terms of timestamps.

My question is now: Do you know of a way to get the "last" 20 entries? Sorted by timestamp? I did a POST request with a JSON string where I wanted the data to be sorted by the timestamp, but that doesn't work, maybe because of the ISO timestamp string.

Do I really have to write a javascript or PHP script to get ALL the database entries and then look for the 20 or 100 last entries by parsing the timestamp, sorting the array again and then get the (now really) last entries? I can't believe that.

Many thanks in advance!

2
nosqlibm-cloudcloudantwatson
Rather than including a link to your live query, can you please add to your question some example output from the link? – Chris Snow

2 Answers

2
votes

I finally found out how to get the data in a nice ordered way. The key is to use the _design api together with the _view api.

So a curl request with the following URL / attributes and a query string did the job:

https://alphanumerical_something-bluemix.cloudant.com/iotp_orgID_iotdb_2018-01-25/_design/iotp/_view/by-date?limit=120&q=name:%27timestamp%27

The curl result gets me the first (in terms of time) 120 entries. I just have to find out how to get the last entries, but that's already a pretty good result. I can now pass the data on to a nice JS chart and display it.

1
votes

One option may be to include the timestamp as part of the ID. The _all_docs query returns documents in order by id.

If that approach does not work for you, you could look at creating a secondary index based on the timestamp field. One type of index is Cloudant Query:

Cloudant query allows you to specify a sort argument:

Another approach that may be useful for you is the _changes api:

The changes API allows you to receive a continuous feed of changes in your database. You could feed these changes into a D3 chart for example.