Convert JSON Payload into a Table in Flow designer

[PavanBV]

Hi Team,

I have created a custom action which connects to an oracle data base and run a SELECT query to get the records from a specific table. 

Now the problem is, the response is in JSON format and i need to convert it into a table format to email it as an report. Please share a lead if anyone knows a fix to this. 

JSON output from the SQL query:

{
    "result": [
        {
            "chain": "XXX",
            "module": "YYY",
            "so_queue": "ZZZ",
            "so_status_name": "AAA",
            "to_char_so_job_finished_mm_dd_yyyyhh24_mi_ss_": null,
            "to_char_so_job_started_mm_dd_yyyyhh24_mi_ss_": "05/06/2024 13:06:03"
        },
        {
            "chain": "BBB",
            "module": "CCC",
            "so_queue": "DDD",
            "so_status_name": "ZXXXX",
            "to_char_so_job_finished_mm_dd_yyyyhh24_mi_ss_": null,
            "to_char_so_job_started_mm_dd_yyyyhh24_mi_ss_": "05/06/2024 13:06:03"
        }
    ]
}

Expected/formatted output:

[Sandeep Rajput · Accepted solution]

@PavanBV Why don't you prepare custom HTML using javascript and feed it to your email notification. Here the keys of your JSON will become your table header and data will become your rows.

Here is an example function I wrote for you which exactly does the same thing.

var stringJSON = '{ "result": [ { "chain": "XXX", "module": "YYY", "so_queue": "ZZZ", "so_status_name": "AAA", "to_char_so_job_finished_mm_dd_yyyyhh24_mi_ss_": null, "to_char_so_job_started_mm_dd_yyyyhh24_mi_ss_": "05/06/2024 13:06:03" }, { "chain": "BBB", "module": "CCC", "so_queue": "DDD", "so_status_name": "ZXXXX", "to_char_so_job_finished_mm_dd_yyyyhh24_mi_ss_": null, "to_char_so_job_started_mm_dd_yyyyhh24_mi_ss_": "05/06/2024 13:06:03" } ] }';

gs.info(convertToHTML(stringJSON));



function convertToHTML(stringJSON){
    
    var style = ' style="border: 1px solid black;border-collapse: collapse;">';
    var styleHeader = ' style="border: 1px solid black;border-collapse: collapse;background-color:orange">';
    var htmlString = '<table'+style;
    var jsonObj = JSON.parse(stringJSON);
    if(jsonObj&&jsonObj.result.length>0){
        htmlString = htmlString+'<tr'+style;
        var keyArray = Object.keys(jsonObj.result[0]);
        for(var i=0;i<keyArray.length;i++){            
            htmlString = htmlString+'<th'+styleHeader;
            htmlString = htmlString+ keyArray[i].toString().toUpperCase();
            htmlString = htmlString+'</th>';
        }
        htmlString = htmlString+'</tr>';

        for (var j=0;j<jsonObj.result.length;j++){
            htmlString = htmlString+'<tr'+style;
            for (var k=0;k<keyArray.length;k++){
                htmlString=htmlString+'<td'+style;
                htmlString=htmlString+jsonObj.result[j][keyArray[k]];
                htmlString=htmlString+'</td>';
            }
            htmlString = htmlString+'</tr>';
        }
        htmlString = htmlString+'</table>';
    }
    //var keyArray = Object.keys(jsonObj.result[0]);
    //gs.print(keyArray);
    return htmlString;
}

The above function returns an HTML which can be added to any notification. Here is the output HTML.

<table style="border: 1px solid black;border-collapse: collapse;"><tr style="border: 1px solid black;border-collapse: collapse;"><th style="border: 1px solid black;border-collapse: collapse;background-color:orange">CHAIN</th><th style="border: 1px solid black;border-collapse: collapse;background-color:orange">MODULE</th><th style="border: 1px solid black;border-collapse: collapse;background-color:orange">SO_QUEUE</th><th style="border: 1px solid black;border-collapse: collapse;background-color:orange">SO_STATUS_NAME</th><th style="border: 1px solid black;border-collapse: collapse;background-color:orange">TO_CHAR_SO_JOB_FINISHED_MM_DD_YYYYHH24_MI_SS_</th><th style="border: 1px solid black;border-collapse: collapse;background-color:orange">TO_CHAR_SO_JOB_STARTED_MM_DD_YYYYHH24_MI_SS_</th></tr><tr style="border: 1px solid black;border-collapse: collapse;"><td style="border: 1px solid black;border-collapse: collapse;">XXX</td><td style="border: 1px solid black;border-collapse: collapse;">YYY</td><td style="border: 1px solid black;border-collapse: collapse;">ZZZ</td><td style="border: 1px solid black;border-collapse: collapse;">AAA</td><td style="border: 1px solid black;border-collapse: collapse;">null</td><td style="border: 1px solid black;border-collapse: collapse;">05/06/2024 13:06:03</td></tr><tr style="border: 1px solid black;border-collapse: collapse;"><td style="border: 1px solid black;border-collapse: collapse;">BBB</td><td style="border: 1px solid black;border-collapse: collapse;">CCC</td><td style="border: 1px solid black;border-collapse: collapse;">DDD</td><td style="border: 1px solid black;border-collapse: collapse;">ZXXXX</td><td style="border: 1px solid black;border-collapse: collapse;">null</td><td style="border: 1px solid black;border-collapse: collapse;">05/06/2024 13:06:03</td></tr></table>

This is how it looks when added to an HTML editor.

Please mark the answer helpful and correct if it manages to address your question.

[PavanBV]

Hi @Sandeep Rajput  - Thanks for the response. I did try with your solution. However, the htmlString is being returned as "Empty" in the flow. I have set the "htmlstring" output variable to "string" datatype. Am i doing something wrong?

[Sandeep Rajput]

@PavanBV Sent you a message, please check.

[PavanBV]

Thank you so much @Sandeep Rajput , i was able to pass the right set of data as input and have modified the code to parse from the correct hierarchy. I was able to get the result.