How to Parse JSON having multiple values to CSV

[Manjunathan Ramasami]

Hi,

My JSON looks like below from my code. Is there a way to parse this JSON using CSV parser and write it into output file? How to parse keys with multiple values into CSV?

ent = system.newEntry();
<logic>
json = ent.toJSON();
task.logmsg("FORMATTED JSON OUTPUT::-->" + json);

"ITDEPT":{
"AAAAA":["99991231","99991231"],
"BBBBB":["0001","0010"],
"CCCCC":["20200704","20200707"],
"EMAIL":"A...@EXAMPLE.COM",
"SUBTY":["0001","0010"],
"DFGRR":["20200701","20200701"],
"USRID":"XXXXXXX"
}

Thanks

[Eddie Hartman]

Absolutely. You can use the innate JS Object handling or SDI's Javscript engine to step through the values, with multi-valued properties acting as Arrays. So you use the .length property and square brackets to address individual values:

ITDEPT.AAAAA[0] and [1]

In order to flatten the object to make it easier to write to CSV, you could turn multi-valued properties into multiple attributes with appended index. In order to do this you first need to wrap your JSON in curly braces so it becomes a single object:

{"ITDEPT":{
"AAAAA": ...

Then this code will flatten the multi-values into individual attributes:
----
jobj = fromJson(json);
flattenedEntry = system.newEntry();

// Cycle through the various properties in ITDEPT (e.g. AAAAA, BBBBB, ...)
for (propname in jobj.ITDEPT) {
// Get the value of the property using bracket notation (very handy)
prop = jobj.ITDEPT[propname];
// If the value is an array, cycle through and create an Att for each value
if (prop instanceof Array) {
for (i = 0; i < prop.length; i++) {
flattenedEntry[propname + "_" + i] = prop[i];
}
} else {
// Otherwise, just a single value
flattenedEntry[propname] = prop;
}
}

// Show the results
task.logmsg(flattenedEntry);
----
It also prints the result:
----
14:10:25,344 INFO - {
"BBBBB_1": "0010",
"BBBBB_0": "0001",
"EMAIL": "A...@EXAMPLE.COM",
"USRID": "XXXXXXX",
"AAAAA_1": "99991231",
"AAAAA_0": "99991231",
"DFGRR_1": "20200701",
"DFGRR_0": "20200701",
"SUBTY_1": "0010",
"SUBTY_0": "0001",
"CCCCC_1": "20200707",
"CCCCC_0": "20200704"
}
---
As you can see, the ordering of Attributes in indeterminate, but you get the picture, right? If not then let me know :)

/Eddie

[Manjunathan Ramasami]

Hi Eddie,

Thanks for the suggestions. Your sample code worked. Now I'm getting an another issue. CSV parser when dumping entries to output file it is missing some headers. Key in my input JSON is not constant as you see below.

INPUT JSON--->
{
"USERID":"AAAAAAA",
"DEPTNO":"5656565",
"ITDEPT":{
"SUBCATEGORY":["abc", "123"],
"ENDNO":"777777"
}
}

{
"USERID":"BBBBBBB",
"DEPTNO":"5656565",
"ITDEPT":{
"SUBCATEGORY":"2",
"ENDNO":"77777"
},
"DESC":{
"MESSAGE":"NA"
}
}
=====================================================
18:02:28,700 INFO - CTGDIS087I Iterating.
18:02:28,717 INFO - [FileConnector_1] CTGDIS351I Map Attribute OUTPUT [1].
18:02:28,717 INFO - [FileConnector_1] CTGDIS353I Script is: task.logmsg("FLATTENED ENTRY TO WRITE TO CSV:: " + flattenedEntry);
ret.value = flattenedEntry;
18:02:28,717 INFO - FLATTENED ENTRY TO WRITE TO CSV:: {
"DEPTNO": "5656565",
"USERID": "AAAAAAA",
"ITDEPT_SUBCATEGORY": "abc&123",
"ITDEPT_ENDNO": "777777"
}
18:02:28,717 INFO - [FileConnector_1] CTGDIS126I Return {
"DEPTNO": "5656565",
"USERID": "AAAAAAA",
"ITDEPT_SUBCATEGORY": "abc&123",
"ITDEPT_ENDNO": "777777"
}.
18:02:28,717 INFO - [FileConnector_1] CTGDIS123I Returned object class com.ibm.di.entry.Entry.
18:02:28,717 INFO - [FileConnector_1] CTGDIS504I *Result of attribute mapping*
18:02:28,717 INFO - [FileConnector_1] CTGDIS505I The 'conn' object
18:02:28,717 INFO - [FileConnector_1] CTGDIS003I *** Start dumping Entry
18:02:28,717 INFO - {
18:02:28,717 INFO - "DEPTNO": "5656565",
18:02:28,717 INFO - "USERID": "AAAAAAA",
18:02:28,717 INFO - "ITDEPT_SUBCATEGORY": "abc&123",
18:02:28,717 INFO - "ITDEPT_ENDNO": "777777"
18:02:28,717 INFO - }
18:02:28,717 INFO - [FileConnector_1] CTGDIS004I *** Finished dumping Entry
18:02:28,717 INFO - [FileConnector_1] CTGDIS506I The 'work' object
18:02:28,717 INFO - [FileConnector_1] CTGDIS003I *** Start dumping Entry
18:02:28,717 INFO - {
18:02:28,731 INFO - "DEPTNO": "5656565",
18:02:28,731 INFO - "USERID": "AAAAAAA",
18:02:28,731 INFO - "ITDEPT": {
18:02:28,731 INFO - "SUBCATEGORY": [
18:02:28,731 INFO - "abc",
18:02:28,731 INFO - "123"
18:02:28,731 INFO - ],
18:02:28,731 INFO - "ENDNO": "777777"
18:02:28,731 INFO - }
18:02:28,731 INFO - }
18:02:28,731 INFO - [FileConnector_1] CTGDIS004I *** Finished dumping Entry
18:02:28,731 INFO - [FileConnector_1] CTGDJW004I No column names provide. Will use first output entry for column names: '[ITDEPT_SUBCATEGORY, ITDEPT_ENDNO, USERID, DEPTNO]'.
18:02:28,731 INFO - [FileConnector_1] CTGDIS351I Map Attribute OUTPUT [2].
18:02:28,731 INFO - FLATTENED ENTRY TO WRITE TO CSV:: {
"DEPTNO": "5656565",
"USERID": "BBBBBBB",
"ITDEPT_SUBCATEGORY": "2",
"DESC_MESSAGE": "NA",
"ITDEPT_ENDNO": "77777"
}
18:02:28,731 INFO - [FileConnector_1] CTGDIS126I Return {
"DEPTNO": "5656565",
"USERID": "BBBBBBB",
"ITDEPT_SUBCATEGORY": "2",
"DESC_MESSAGE": "NA",
"ITDEPT_ENDNO": "77777"
}.
18:02:28,731 INFO - [FileConnector_1] CTGDIS123I Returned object class com.ibm.di.entry.Entry.
18:02:28,731 INFO - [FileConnector_1] CTGDIS504I *Result of attribute mapping*
18:02:28,731 INFO - [FileConnector_1] CTGDIS505I The 'conn' object
18:02:28,731 INFO - [FileConnector_1] CTGDIS003I *** Start dumping Entry
18:02:28,731 INFO - {
18:02:28,731 INFO - "DEPTNO": "5656565",
18:02:28,731 INFO - "USERID": "BBBBBBB",
18:02:28,731 INFO - "ITDEPT_SUBCATEGORY": "2",
18:02:28,731 INFO - "DESC_MESSAGE": "NA",
18:02:28,731 INFO - "ITDEPT_ENDNO": "77777"
18:02:28,731 INFO - }
18:02:28,731 INFO - [FileConnector_1] CTGDIS004I *** Finished dumping Entry
18:02:28,731 INFO - [FileConnector_1] CTGDIS506I The 'work' object
18:02:28,731 INFO - [FileConnector_1] CTGDIS003I *** Start dumping Entry
18:02:28,731 INFO - {
18:02:28,731 INFO - "DEPTNO": "5656565",
18:02:28,731 INFO - "USERID": "BBBBBBB",
18:02:28,731 INFO - "ITDEPT": {
18:02:28,731 INFO - "SUBCATEGORY": "2",
18:02:28,731 INFO - "ENDNO": "77777"
18:02:28,731 INFO - },
18:02:28,731 INFO - "DESC": {
18:02:28,731 INFO - "MESSAGE": "NA"
18:02:28,731 INFO - }
18:02:28,731 INFO - }
18:02:28,731 INFO - [FileConnector_1] CTGDIS004I *** Finished dumping Entry
18:02:28,731 INFO - CTGDIS088I Finished iterating.
18:02:28,731 INFO - [FileConnector_1] CTGDIS525I Closing Parser on output stream.
18:02:28,731 INFO - CTGDIS100I Printing the Connector statistics.
18:02:28,731 INFO - [FileConnector] Get:2
18:02:28,731 INFO - [EmptyScript] Calls: 2
18:02:28,731 INFO - [ParseJSONtoCSV_1] Calls: 2
18:02:28,731 INFO - [FileConnector_1] Add:2
18:02:28,731 INFO - [ClearValues] Calls: 2
18:02:28,731 INFO - CTGDIS104I Total: Get:2, Add:2.
18:02:28,731 INFO - CTGDIS101I Finished printing the Connector statistics.
18:02:28,731 INFO - CTGDIS080I Terminated successfully (0 errors).

And the final output in the CSV is missing header DESC_MESSAGE and its value as this key was not present for 1st entry. Is there a way to iterate through all JSON to get all the available keys which later can be used to define headers in CSV? This JSON is from some legacy system, hence standardizing keys is not possible.

ITDEPT_SUBCATEGORY|ITDEPT_ENDNO|USERID|DEPTNO
abc&123|777777|AAAAAAA|5656565
2|77777|BBBBBBB|5656565

[Eddie Hartman]

You can specify which fields to include in your CSV in the Parser Configuration > Advanced section. The 'Field Names' parameter is a text box where you can either put the names of all fields on a single line using the specified separator (e.g. ;) between them, OR one field on each line. Then you will get each field even if it is not in the Entry you are writing for that cycle.

/Eddie