Converting JSON data to CSV in Cloudant using List and View

Question

I tried to convert the JSON data in my Cloudant db to csv format, using the List function. It works perfectly for all values except JSON array values, i.e. the nested values. For these, I am getting [object object] as the output in my csv document.

Please find the sample JSON document which I am using, below:

  {
    "NAME": "Aparna",
    "EMAIL": "[email protected]",

    "PUBLIC_OFFICIALS_CONTACTED": [
    { "NAME_PUBLIC_OFFICIAL": [ "ab"],
    "TITLE_PUBLIC_OFFICIAL": ["cd"]}
    ],
    "COMMUNICATION_TYPE": [
    "Meeting",
    "Phone",
    "Handout",
    "Conference"
    ],
    "NAMES_OF_OTHERS_FROM_IBM": [
    { "NAME_OF_OTHERS": ["ef"],
    "TITLE_OF_OTHERS": [ "gh"]}
    ],
    "COMMUNICATION_BENEFIT": "Yes",
    "LAST_UPDATE_BY" : "ap"
    }

Please find the map and list functions used below :


     "map" : "function(doc){
                                 if((\"SAVE_TYPE_SUBMIT\" in doc) && (doc.SAVE_TYPE_SUBMIT== \"Submit\")) {
                                    emit (doc. LAST_UPDATE_BY,[doc.NAME,doc.EMAIL,doc.PUBLIC_OFFICIALS_CONTACTED[0].NAME_PUBLIC_OFFICIAL,\n   doc.PUBLIC_OFFICIALS_CONTACTED[0].TITLE_PUBLIC_OFFICIAL,doc.COMMUNICATION_TYPE,doc.NAMES_OF_OTHERS_FROM_IBM[0].NAME_OF_OTHERS,  doc.NAMES_OF_OTHERS_FROM_IBM[0].TITLE_OF_OTHERS, doc.COMMUNICATION_BENEFIT,doc. LAST_UPDATE_BY,doc.LAST_UPDATE_DATE]) ;
                                  }
                              }



"list" : "function (head, req) {
                     var row;
                     start({\n headers: {'Content-Type': 'text/csv' }, 
                        });
                     var first = true;
                     while(row = getRow()) {
                         var doc = row.doc;
                         if (first) {
                            send(Object.keys(doc).join(',') + '\\n');
                            first = false;\n }
                            var line = '';
                            for(var i in doc) {
                           // comma separator
                               if (line.length > 0) {
                                  line += ',';\n }
                                  // output the value, ensuring values that themselves
                                 // contain commas are enclosed in double quotes
                                    var val = doc[i];
                                    if (typeof val == 'string' && val.indexOf(',') > -1)                            {
                                         line += '\"' + val.replace(/\"/g,'\"\"') + '\"';
                                     } 
                                   else {
                                       line += val;
                                        }
                                 }
                                 line += '\\n';
                                 send(line);
                             }}"

Note : In the map, only the first values have been fetched from the JSON arrays for now, on purpose, to simplify the function.

Please help understand how to fetched the nested JSON values or arrays and download the same in csv format. Any guidance would be much appreciated!

Answer 1

You can try to stringify the object you are trying to export and you will get some clue

if (typeof val == 'string' && val.indexOf(',') > -1)                            {
    line += '\"' + val.replace(/\"/g,'\"\"') + '\"';
} 
else {
    line += JSON.stringify(val);
}

Or even better

if (typeof val == 'string' && val.indexOf(',') > -1)                            {
    line += '\"' + val.replace(/\"/g,'\"\"') + '\"';
} 
else if(val instanceof Array){
    line += val.join(',');
}
else {
    line += JSON.stringify(val);
}