JSON Data File Datamapping Issue

[iBaldie]

I know there are loads of posts about this and I have managed to create an XML file from a simple json file to then setup my designer template and then just send through workflow the json file to designer to create the content. However I know have a complex json data object that has variable key names and nested arrays, all of which makes mapping complex and as such impossible to datamap using my previous method.

Any ideas of a way round this? My thoughts are to just use the index for each array item that contains the data fields, below is an example (Fields are CommKitID and DatePorcessed, others are in the actuall data set but left these out for data protection and simplicity:

{
“kbatch_id”: “B-11102018”,
“batch_total”: 258,
“rightnow_data”: {
“ConID_221614”: {
“CommKitID_C0032785449”: {
“CommKitID”: “C0032785449”,
“DateProcessed”: “‘2018-09-11 04:51:06’”
}
},
“ConID_191515”: {
“CommKitID_C0033571033”: {
“CommKitID”: “C0033571033”,
“DateProcessed”: “‘2018-09-21 20:02:21’”
}
},
“ConID_697615”: {
“CommKitID_C0032808696”: {
“CommKitID”: “C0032808696”,
“DateProcessed”: “‘2018-09-18 20:16:38’”
}
},
“ConID_118230”: {
“CommKitID_C0031572871”: {
“CommKitID”: “C0031572871”,
“DateProcessed”: “‘2018-09-18 20:40:18’”
}
},
“ConID_718094”: {
“CommKitID_C0030517748”: {
“CommKitID”: “C0030517748”,
“DateProcessed”: “‘2018-09-19 03:17:17’”
}
},
“ConID_903268”: {
“CommKitID_C0033351455”: {
“CommKitID”: “C0033351455”,
“DateProcessed”: “‘2018-09-19 19:42:32’”
}
},
“ConID_153156”: {
“CommKitID_C0034313975”: {
“CommKitID”: “C0034313975”,
“DateProcessed”: “‘2018-09-19 20:44:29’”
}
},
“ConID_142881”: {
“CommKitID_C0030420422”: {
“CommKitID”: “C0030420422”,
“DateProcessed”: “‘2018-09-20 00:55:02’”
}
},
“ConID_104772”: {
“CommKitID_C0030531909”: {
“CommKitID”: “C0030531909”,
“DateProcessed”: “‘2018-09-20 00:55:46’”
}
},
“ConID_699321”: {
“CommKitID_C0022819286”: {
“CommKitID”: “C0022819286”,
“DateProcessed”: “‘2018-09-21 00:54:29’”
},
“CommKitID_C0031628679”: {
“CommKitID”: “C0031628679”,
“DateProcessed”: “‘2018-09-21 19:55:20’”
}
},
“ConID_114453”: {
“CommKitID_C0030470563”: {
“CommKitID”: “C0030470563”,
“DateProcessed”: “‘2018-09-21 01:33:24’”
}
}
}
}

[Phil]

If your JSON file contains a single record, I would simply have Workflow store it in a JobInfo, which gets passed automatically to the DataMapper. Then, in a Datamapper Script Step, you do a JSON.parse() of that value to convert it to a JS object that you can then process fairly easily.

However, if you need the field names in your data model to reflect the structure of the JSON file, then you’re out of luck because the very principle behind the data model is to offer a stable, predictable structure.

You could, however, design a completely generic process that would store all the fields in a detail table that would contain 2 fields: “name” and “value”.
So for instance, the detail table’s first record would contain something like:

name: CommKitID
value: C0034313975

and the second record in the detail table would contain

name: DateProcessed
value: 2018-09-19 20:44:29

So regardless of the JSON structure, your data model would remain the same, there would only be a varying number of records in the detail table. But this kind of method may not be applicable to your project, you’ll have to determine that by yourself.

If all else fails, converting the JSON to XML and then potentially running that XML through an XSLT transform in order to “genericize” (is that even a word?!?) the data might be your best bet.

[iBaldie]

Hi Phil
Thanks for your help, hoever this data is stuctured so that we have multiple records per set (pack) and I need this info for production perposes so this wont work.

So in this instance I would want to access the records detail like this:
record.fields.rightnow_data.ConID.CommKitID.DateProcessed and record.fields.rightnow_data.ConID.CommKitID.CommKitID

So I will need to rename the json object with generic keys I guess…