How can I convert a JSON array into a CSV?

Question

[
  {
    "patientMrn": 12345,
    "patientFullName": "zzTest, Bill",
    "patientDob": "1929-02-02",
    "patientType": "LTC"
  },
  {
    "patientMrn": 2222,
    "patientFullName": "zzTest, Jill",
    "patientDob": "1955-01-01",
    "patientType": "LTC"
  },
  {
    "patientMrn": 3333,
    "patientFullName": "zzTest, Jack",
    "patientDob": "2003-03-03",
    "patientType": "ZTC"
  }
]

Answer 1

You can dynamically build the header row and then add each row with a custom mapping.

// Create the CSV message model. Pipe deliminated and with a header row that we will add next.
message = qie.createCSVMessage("", false, '"', "|", 'UTF-8', true);

qie.debug('How many JSON rows: ' + source.getCount('/[]'));

// Make header row
var csvHeader = '';
var keys = source.getJSONKeys('/[1]');
for (var i = 0 ; i < keys.length; i++) {
   csvHeader += keys[i] + '|';
}
message.setNode('/', csvHeader);

// Add each object as csv data row
for (var j=1 ; j <= source.getCount('/[]') ; j++) {
   // for each row loop through each column
   for (var k = 0 ; k < keys.length; k++) {
      var keyValue = source.getNode("/["+j+"]/"+keys[k]);
      
      // null safe the value from each key
      if (StringUtils.isBlank(keyValue)) {
         keyValue = '';
      }
      
      qie.debug('key['+k+']: (' + keys[k] + ') keyValue['+k+']): (' + keyValue + ')');
      message.setNode(keys[k], keyValue, j);
   }
}