Parsing nested JSON objects to CSV

[rrootdawg]

I'm trying to parse nested JSON objects to a .csv file using the drag and drop feature in Mirth. I'm close but can't seem to get out all the nested objects data to a record. what I end up with is a record for each parent object and only the last child object data for that record. What I'm expecting is a record for the parent repeated with each child object's data values. Maybe I need to do this by writing the javascript? Any help is appreciated. Attached is the channel I've been playing with. Simple channel writer. Had to save it as .txt to post. Also attached is some sample JSON. I'm expecting to get two output rows for each nested provider_groups and negotiated_price objects but am only able to get the last nested objects. I.E. provider_groups[1] and negotiated_price[1].

Here's what I get:
"BYPASS GRAFT, WITH OTHER THAN VEIN; CAROTID-",CPT,2021,35606,1112223330,111222333,3193.25

Here's what I'm looking for:
"BYPASS GRAFT, WITH OTHER THAN VEIN; CAROTID-",CPT,2021,35606,1234567890,123456789,1965.49
"BYPASS GRAFT, WITH OTHER THAN VEIN; CAROTID-",CPT,2021,35606,1112223330,111222333,3193.25

JSON TEST2.txt
Testdata.txt
.

[pacmano1]

I have been using mirth a long time and still don't undestand the iterator UI. I would build that in JS.

[tonygermano]

I didn't actually test this in mirth, but it should work. The code makes some assumptions about the data.

The function converts the object directly to an XMLList, using an e4x template, with the number of rows the same length as the provider_groups array.

The reduce combines the function results for each object into one long list of rows, which it then uses to replace any pre-existing rows in your outbound template.

Performance may start to lag if the final output will contain a very large number of rows. If you find that is the case for your data, then it would be best to either batch process your input to only deal with one object at a time or write your output directly to a string (using the Raw type) to avoid using xml (Delimited type.)

Xml (e4x objects as implemented in mirth/rhino) works great for small data, but gets exponentially slower the longer an xml list gets.

function denormalizeToXML(obj) {
    var result = new XMLList();
    // assumes the negotiated_rates array always contains 1 element
    obj.negotiated_rates[0].provider_groups.forEach((group, i) => {
        result += <row>
            <CodeDesc>{obj.name}</CodeDesc>
            <BillCodeType>{obj.billing_code_type}</BillCodeType>
            <CodeVer>{obj.billing_code_type_version}</CodeVer>
            <Code>{obj.billing_code}</Code>
            <NPI>{group.npi}</NPI>
            <EIN>{group.tin.value /* assumes this is always right */}</EIN>
            <Rate>{obj.negotiated_rates[0].negotiated_prices[i].negotiated_rate}</Rate>
        </row>;
    });
    return result;
}

tmp.setChildren(msg.in_network.reduce((xmlList, billingCode) =>
        xmlList + denormalizeToXML(billingCode), new XMLList()));

[rrootdawg]

Thanks, this actually worked as you expected. You are right about performance and the large number of rows. There will be a lot of rows. I'll play with batching and also using Raw data type.