Sidebar

How can I convert a JSON array into a CSV?

0 votes
344 views
asked Apr 28, 2023 by michael-h-5027 (14,350 points)
[
  {
    "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"
  }
]

1 Answer

0 votes

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);
   }
}
 

answered Apr 28, 2023 by michael-h-5027 (14,350 points)
...