how to extract data from response body.

niveditakumari · ‎09-30-2019

Hello,

How to extract data from response body and set that value in table.

ex - I am calling one API and with that I am getting some records and that are stored in response body. Now I want to extract all the values from response body and set that value in one table.

I am trying to achieve this by schedule job.

Can anyone please help me that how I can extract that values from response body and set that value in our tables.

Regards,

Nivedita

Ankur Bawiskar · ‎10-07-2019

Hi Nivedita,

the script shared earlier should work; ensure you give correct field names for the 6 fields

try {
var r = new sn_ws.RESTMessageV2('Get Data', 'Default GET');

//override authentication profile
authentication_type ='basic';
r.setAuthenticationProfile(authentication_type, 'efea940d1b408010acf5fc88cc4bcbf7');

var response = r.execute();
var responseBody = response.getBody();
var httpStatus = response.getStatusCode();

gs.print(responseBody);
gs.log("response body is" + responseBody);
var obj = JSON.parse(responseBody);

for(var i = 0; i<obj.plannedEvents.length; i++) {

var gr = new GlideRecord('u_outage');
gr.initialize();
gr.u_rollbackplan = obj.plannedEvents[i].rollbackPlan;
gr.u_plannedeventstartdate = obj.plannedEvents[i].plannedEventStartDate;
gr.u_closurecode = obj.plannedEvents[i].closureCode;
gr.u_description = obj.plannedEvents[i].description;
gr.u_plannedeventenddate = obj.plannedEvents[i].plannedEventEndDate;
gr.u_ticketid = obj.plannedEvents[i].ticketId;
gr.u_correlationid = obj.plannedEvents[i].correlationId;
gr.u_impact = obj.plannedEvents[i].impact;
gr.u_executionowner = obj.plannedEvents[i].executionOwner;
gr.u_maintenancetype = obj.plannedEvents[i].maintenanceType;
gr.u_sitelocation= obj.plannedEvents[i].maintenanceType;
gr.u_expecteddowntime= obj.plannedEvents[i].expectedDowntime;
gr.u_activitystatus= obj.plannedEvents[i].activityStatus;

var servicesArray = [];

for(var j = 0; j<obj.plannedEvents[i].services.length; j++) {
var gr = new GlideRecord('services_table');
gr.initialize();
gr.u_serviceidentifier= obj.plannedEvents[i].services[j].serviceIdentifier;
gr.u_protectionstatus= obj.plannedEvents[i].services[j].protectionStatus;
gr.u_servicetype= obj.plannedEvents[i].services[j].serviceType;
gr.u_servicealias= obj.plannedEvents[i].services[j].serviceAlias;
gr.u_customerlename= obj.plannedEvents[i].services[j].customerLeName;
gr.u_cuid= obj.plannedEvents[i].services[j].cuid;
var sysId = gr.insert();
servicesArray.push(sysId.toString());
}

gr.u_services = servicesArray.toString();
gr.insert();

}
gs.log("table data is" + responseBody);
}

catch(ex) {
gs.info('Exception is: ' + ex);
}
Mark ✅ Correct if this solves your issue and also mark 👍 Helpful if you find my response worthy based on the impact.
Thanks
Ankur

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

View solution in original post

niveditakumari · ‎10-02-2019

Can you please help me with this.

Regards,

Nivedita

niveditakumari · ‎10-02-2019

Hello Ankur,

Can you please help me with this to getting data one by one and set into custom table.

Regards,

Nivedita

niveditakumari · ‎10-02-2019

Hello Ankur,

I have tried below code but i am getting undefined value :

Regards,

Nivedita

Ankur Bawiskar · ‎10-02-2019

Hi Nivedita,

what information from the json you want to extract?

Are you referring to the services which is an array of json objects? the array length is 4

If yes then sample code below

var str = '{"status":"200","message":"Success","totalCount":1,"startIndex":0,"endIndex":0,"plannedEvents":[{"closureCode":null,"rollbackPlan":"Test","plannedEventStartDate":"2018-05-24 07:06:30","siteLocation":"Test","ticketId":"CHGP0054939","description":"Test Ticket . Please dont close it.","maintenanceType":"Normal","impact":"Test","correlationId":null,"executionOwner":"Tata Backbone Network","plannedEventEndDate":"2018-05-25 07:06:56","services":[{"serviceIdentifier":"TEST1-CN-862128916674-G-MPLS_duplicate","protectionStatus":"Protected","serviceType":"GVPN - MPLS","serviceAlias":"","customerLeName":"TATA Communications Test1","cuid":"1"},{"serviceIdentifier":"TEST1-GB-441926640000-IP-DIA-40","protectionStatus":"Protecting","serviceType":"IP - DIA","serviceAlias":"","customerLeName":"TATA Communications Test1","cuid":"1"},{"serviceIdentifier":"TEST1-GB-040237-I-TPAAS-02","protectionStatus":"Unprotected","serviceType":"Infrastructure - TPaaS","serviceAlias":"","customerLeName":"TATA Communications Test1","cuid":"1"},{"serviceIdentifier":"TEST1-GB-040237-G-MPLS-01","protectionStatus":"Protection failure","serviceType":"GVPN - MPLS","serviceAlias":"","customerLeName":"TATA Communications Test1","cuid":"1"}],"expectedDowntime":"00:02:45","activityStatus":"Scheduled"}]}';

var parser = new JSONParser();

var parsedData = parser.parse(str);

for(var i=0;i<parsedData.plannedEvents[0].services.length;i++){

gs.info(parsedData.plannedEvents[0].services[i].serviceIdentifier);

gs.info(parsedData.plannedEvents[0].services[i].protectionStatus);

gs.info(parsedData.plannedEvents[0].services[i].serviceType);

gs.info(parsedData.plannedEvents[0].services[i].serviceAlias);

gs.info(parsedData.plannedEvents[0].services[i].customerLeName);

gs.info(parsedData.plannedEvents[0].services[i].cuid);
}

screenshot below

Mark ✅ Correct if this solves your issue and also mark 👍 Helpful if you find my response worthy based on the impact.
Thanks
Ankur

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

niveditakumari · ‎10-02-2019

Hello Ankur,

I need to extract all the values from JSON and then import that values in my custom table.

I have created one custom table for all these fields.

Regards,

Nivedita