AnsweredAssumed Answered

Unable to parse JSON DB Response to a JSON profile.

Question asked by sahil.minocha875598 on Jul 6, 2018
Latest reply on Jul 6, 2018 by sahil.minocha875598

Unable to parse JSON DB Response to a JSON profile.

 

I have a JSON column incoming from MySQL and I want to map to a JSON profile to iterate over it.

 

Incoming DB record:

DBSTART|4e7b04ae-a586-4097-8be2-cacf4e5b23c9|2|@|BEGIN|2|@|OUT_START|3|@|{"contactIds":[132456],"companyIds":[132456,1324565],"dealIds":[13246],"ownerIds":[],"workflowIds":[],"ticketIds":[],"contentIds":[]}

 

I transformed this to a flat file to get the following result:

{"contactIds":[132456],"companyIds":[132456,1324565],"dealIds":[13246],"ownerIds":[],"workflowIds":[],"ticketIds":[],"contentIds":[]}

 

Now, when I try to map the above response to a JSON Profile I get an error with "Unable to extract data from profile, invalid element found." or "Malformed JSON".

 

Can anybody suggest a better way to map this JSON string to a JSON profile?

 

I tried using custom scripting to fetch each column to map it to corresponding node, however I do not get any result from the map.

Script:

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;

ObjectMapper mapper = new ObjectMapper();
JsonNode rootNode = mapper.readTree(associations);
def contacts=rootNode.get("contactIds");
contactIds = contacts;

 

I also tried to map the JSON Object directly to the Root with the below script and get the following error:

[Input ProfileLocation: Root; DocumentLocation: /Root]: Unable to create JSON files from data, the document may not be well-formed json

Script output for Root is:

Function: 5-  Output Element[Root], Value[{"contactIds":[12345],"companyIds":[132456,1234],"dealIds":[123456],"ownerIds":[],"workflowIds":[],"ticketIds":[],"contentIds":[]}]

And the JSON profile has the same structure as above, as I created the JSON profile with the above response.

Root(Object){contactIds(Array)[],companyIds[],etc.}

 

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;

ObjectMapper mapper = new ObjectMapper();
Object rootNode = mapper.readTree(associations);
root=rootNode;

 

Function: 5-  Input[18:associations, {"contactIds":[1234],"companyIds":[1234,12345],"dealIds":[1234],"ownerIds":[],"workflowIds":[],"ticketIds":[],"contentIds":[]}]

Function Execution: Scripting-  Output[19:root, {"contactIds":[1234],"companyIds":[1234,1234],"dealIds":[1234],"ownerIds":[],"workflowIds":[],"ticketIds":[],"contentIds":[]}]

 

Any pointers would be much appreciated! 

Attachments

Outcomes