Parsing JSON Data in Staging table

[Sathiskumar_D]

Hello,

I am very new to REST API integration. I am getting data from third party application in JSON format. I moved that data to  staging table as string (JSON.stringify(data)). For transformation, I am moving that data to name-value pair field in target table. Field value in target table is empty. I am not sure how to bring that data into the desired field. Also, the reason for using name value field in target is to get the extraneous data for later use. Things I want achieve:

1) parse the stringified data from staging table and set value in name-value pair field.

2) if possible, other data in the parsing may be mapped to other fields.

[Allen Andreas]

Hello,

Ideally, you'd parse the payload and then input the data to the relevant fields that are on your staging table. You could still save the initial payload in it's own field, but then your staging table has all the fields involved and then the values from the parsed payload.

Since you didn't do that initially, you'll have to do that in the transform map. Then you'll need to map the keys to the appropriate fields on the target table and then set those values accordingly.

You'd also want to have done this before you start saving on the staging table as the transform map will automatically run then, otherwise, you'd have to push it through manually.

Please mark reply as Helpful/Correct, if applicable. Thanks!

[Sathiskumar_D]

Hi Allen,

Thanks for your reply.

"Ideally, you'd parse the payload and then input the data to the relevant fields that are on your staging table. You could still save the initial payload in it's own field, but then your staging table has all the fields involved and then the values from the parsed payload."

1. Yes.I did parse the payload. After parsing,I am saving the entire payload in string field (JSON.stringify (parsedData).
2. Yes,staging table has other fields as well as payload field (entire JSON data in string).

"Since you didn't do that initially, you'll have to do that in the transform map. Then you'll need to map the keys to the appropriate fields on the target table and then set those values accordingly."

1. I did it initially. I also tried to parse the stringified payload field from that staging table to target table in transform map.
2. I want to store some of the data as name-value in the field. There are some extra data that will be used later.   

"You'd also want to have done this before you start saving on the staging table as the transform map will automatically run then, otherwise, you'd have to push it through manually".

1. This prompted me to ask another question.Should i use onBefore,onStart or what type transform map script be used?

[Allen Andreas]

Hi,

My apologies, your post above and now your response is a bit confusing. This is all drawing speculation or assumptions because you aren't providing any examples of anything.

Please, take a moment, perhaps give an example of a payload, give an example of a staging table record and what fields are filled in with what values, what your transform map looks like, and where you're stuck.

If you're saying you've parsed the payload and set your staging table record(s) with values already, then you don't need to do anything, but use a transform map and point the staging table field to a target table field, and that's it.

I'm not understanding why we're talking about parsing the payload (and where to do it) if you're saying you've already done that.

Please mark reply as Helpful/Correct, if applicable. Thanks!

[Sathiskumar_D]

var ImportUtils = Class.create();
ImportUtils.prototype = {
    initialize: function() {},


    
    importMYAPI: function() {


        try {
            var r = new sn_ws.RESTMessageV2('myAPI', 'complaintsList');
            r.setStringParameter('Accept', 'application/json');
            r.setStringParameter('key', 'Private');
            //override authentication profile 
            //authentication type ='basic'/ 'oauth2'
            //r.setAuthenticationProfile(authentication type, profile name);

            //set a MID server name if one wants to run the message on MID
            //r.setMIDServer('MY_MID_SERVER');

            //if the message is configured to communicate through ECC queue, either
            //by setting a MID server or calling executeAsync, one needs to set skip_sensor
            //to true. Otherwise, one may get an intermittent error that the response body is null
            //r.setEccParameter('skip_sensor', true);

            var response = r.execute();
            var responseBody = response.getBody();
            var httpStatus = response.getStatusCode();
            var responseObj = JSON.parse(responseBody);
            gs.log(responseObj);
           
        }
        //The code stub assumes the error messages have been internationalized,which has not been done here. Modifying the code to catch() logic to report errors.
        catch (ex) {
            var message = ex.message;
        }
        
        
        var results = responseBody.split(',');
        
        var parser = new JSONParser();
        var parseddata = parser.parse(responseBody);
                for(var i=0;i < responseObj.complaints.length; i++){
			var results = responseBody.split(',');
            var restGR = new GlideRecord('u_raw_complaints');
            restGR.initialize();
            restGR.setValue('u_complaintid', responseObj.complaints[i].complaintId);//string field
            restGR.setValue('u_company_status', responseObj.complaints[i].companyStatus);//string field
            restGR.setValue('u_complaint_payload', JSON.stringify(responseObj.complaints[i]));//name-value pair field
            restGR.setValue('u_complaint_payload_string', JSON.stringify(responseObj.complaints[i]));// string field
                   restGR.insert();
		}



    },

    type: 'ImportUtils'
};

This is my SI that is called by scheduled job. It pushes data to staging table. I have attached the staging table as well.