Export Cloudant JSON as CSV, RSS, or iCal

You can access IBM Cloudant’s NoSQL database from any programming language because of its RESTful HTTP API and JSON data format. There are some circumstances, however, where you need to present your data in a format other than JSON. Perhaps you need to crunch some data in a spreadsheet, import data into your calendar, or aggregate data in an RSS reader.

You could write your own converter code on the the client side, but why not configure Cloudant to output data directly to other formats using a List Function. A list function works alongside Cloudant MapReduce to let you output a Cloudant database in the format of your choice.

These are the basic steps, which I’ll expand upon in this article:

Devise a Javascript map function that defines an index into your Cloudant database. MapReduce is an efficient and highly scalable method of working with large datasets in a distributed database. This step is equivalent to indexing a field in an relational database.

Devise a list function that describes how the data is to be transformed. The list function runs through each document in the result set and calls functions to generate HTTP headers and to output text.

Upload the map and list functions as a Cloudant Design Document. Design documents are special records in a Cloudant database that contain code that defines MapReduce indexes, Search indexes, and list functions.

Query the newly-created view with a list function. Also, use this function to transform your data format. (Once an index has built, querying the view is very efficient and has excellent performance on large data sets.)

The data set

Before we can index our data, we need to take a look at the data itself. Let’s say we have a database of appointments for a hair styling business. A typical document looks like this:

Ordering the data by date

The appointment data is ordered by the _id field, but we would like to order our data by date, so we can extract a single day’s appointments from a larger data set. To do so, we’ll create a bydate view of this data, using a JavaScript map function. When a new view is created (by adding a design document to the database), the view’s map function is called with each document in the database in turn. The map function chooses to “emit” a key and a value which forms an index into the data set. In this case, we need to index the date field, so doc.date is emitted as the key. We don’t need to map the value of the date field so our function will emit null in the value slot:

function(doc) {
emit(doc.date, null);
}

You can paste this map function in one of two places:

into the Cloudant Dashboard’s New View page (Open your database, click the + button beside All Design Docs and choose New View)

This database covers only one day, but in real life, there could be millions of records in your database. To include only specific dates, we can add a startkey and endkey to the URL to define the date range.

For example, this query shows the afternoon appointments on September 29th:

List functions

Now that we have a date-ordered view into our data set and can filter data between two dates, we can start adding list functions to transform the output data into something other than JSON. A list function contains a row iterator to run through the data set and you can optionally call a start function once to output HTTP headers. You can call a send function multiple times to output a block of data:

iCalendar

In order to represent our data on a calendar app such as iCal, Outlook, or Google Calendar, we first create a second view which will sort the data by stylist and date. This lets us extract a single stylist’s agenda for the day.

Our new map function looks like this:

function (doc) {
emit([doc.stylist, doc.date], null);
}

We combine the stylist and the date into an array which forms the key of the index. View the index itself here.

We can parameterise this query to extract each stylist’s data separately:

Outputing a line of text from a list function is simple; the biggest challenge we have here is dates. Our dates are stored in this format: 2015-09-29 10:00:00 and the iCal format expects 20150929T100000Z, where T delimits the date from the time and Z indicates the GMT timezone. Converting this string is easy, but we also have to do some date artithmetic to add the appointment’s duration to the start time to get an end time stamp in the correct format. This is the code to do it:

You can easily import the iCal text that Cloudant produces into a Calendar application:

Conclusion

Now you know that you can take Cloudant’s NoSQL database JSON documents and use JavaScript map functions to order your data in a materialized view. With that view, you can extract the data in that order, or a subset of it. Then use list functions to transform the output into a range of formats other than the JSON that’s standard to Cloudant. The same design document can support multiple views of the data (bydate, bystylistanddate) and multiple list functions (CSV, RSS, and iCal), which you can combine together using Cloudant’s RESTful API.

Before joining IBM Cloud Data Services, Glynn served as the Head of IT and Development for Central Index, creating a white-label frontend for a NoSQL business directory (using PHP, Node.js, MySQL, Redis, Cloudant, and Redshift). His experience includes writing CRM systems, "find my nearest" indexes, e-commerce platforms, and a phone tracking app. He also built a transport route-planning system in Java. Glynn got his start in Research and Development for the steel industry, creating control and instrumentation systems. Outside work, Glynn enjoys guitars, football, crosswords, and Victorian fiction.