How store the JSON data fields in table from inbound integration?

[sri83]

Hi Team,

As shown below, we receive JSON data from a third party system, and we need to store this data in a custom table.

Could you please explain how I should process the following JSON data and update the fields in the table?

JSON Fomat:

{
"u_sys_id": "5f82c76f1b50e1106c1ab9118b4bcb55",
"u_state": "Ready",
"u_comments": "Sample comments",
"u_priority": "High",
"u_correlation_id": "TICKET123456789",
"applications": [
{
"name 1": "value 1",
"name 2": "value 2",
"name 3": "value 3",
"name 4": "value 4",
"name 5": "value 5"
}
]
}

[Ankit Rawat]

Hi @sri83 ,

You can directly dot walk.

For example;

I am storing a value in a variable

var a=<name of variable which stores the JSON>.u_sys_id;

var b = <name of variable which stores the JSON>.applications.name 1;

Regards 

Ankit

[Joshua_Quinn]

In my experience, if this data is coming in via an integration, you will need to parse the string into a usable JSON object and then use that object to fill in a GlideRecord object and then insert that object. If it's coming in via a field in a data source, that same script would be used in the transform of that field.

The script you would use could look something like 

var obj_string = '<your json string>';
var json = JSON.parse(obj_string);
var gr = new GlideRecord('your_target_table');
gr.setValue('u_sys_id', json['u_sys_id']);
//Continue setting values for each field
//If you know that the JSON will always have an array of applciations, you can skip this if statement
if (json.hasOwnProperty('applications')) {
	var application_array = json['applications'];
	//JSON.parse does not parse JSON objects in the array, so you will have to loop through the array and create JSON objects for each array index
	for (var i = 0; i < application_array.length; i++){
		var application_obj = JSON.parse(application_array[i]);
		//Once you have an object for the applciations, do what you need with that object
		gr.setValue('application_name_one', application_obj['name 1']);
	}
}
gr.insert();

[sri83]

Hi  Joshua_Quinn,

Thanks for the quick reply,

Actually we are not processing the data directly to target table. First we are storing data in the source table then after will process the JSON data into the target fields through transform map by mapping with the fields.
Where do i write the above code in the transform map? Please suggest

[Joshua_Quinn]

You can use the "Transform Script" under the related list of the transform map. Using the source parameter, access the JSON string and then perform the parsing as needed. You won't need to worry about mapping the field from the source table to your target table. Here is the article on Maps with Transformation Event Scripts