Parse and insert REST message JSON response to incident table

[Community Alums]

Hi,

I'm receiving a JSON response with incident information from a third party api looking like this from background script:

*** Script: {"items":[{"id":"000000000053434","caseClassification":"To be classified","status":"Working","subStatus":null,"priority":"Medium","accessId":null,"subscriptionId":null,"service":null,"serviceProvider":null,"numberOfExternalLogs":3,"internalSystemReferenceId":null,"contactName":"ContactName","contactEmail":null,"statusId":30,"affectedAreaZipCodes":null,"externalReferenceId":null,"description":"The description goes here","services":[],"createdAt":"2019-03-29T09:57:09+02:00","updatedAt":"2019-03-29T09:57:09+02:00"}

How could I parse this response and insert it into the SNow incident table from a scheduled job? Existing incidents also need to be updated at the same time to avoid duplicates.

Thanks

[Phuong Nguyen]

Hi qwe7799,

You can parse is it using the JSON.parse(payload), then you can access the object's properties by the .(dot) notation, like obj.items, obj.items[0].id, etc...

Then you need to query the incident table to find if an incident exists with that sys_id, if yes update it, if no insert a new record. If you can post your code here with what you got so far, I can help point you in the right direction.

Thanks,

Phuong

[Community Alums]

Hello,

Thank you and sorry for the late response.

I've been trying to execute this script in a scheduled job to retrieve and insert records:

But it only inserts the first record in the list to the table, and only "id" and "status" gets inserted.

What could possibly be wrong?

try {
	var r = new sn_ws.RESTMessageV2('Vendor Get Incidents', 'GET');
	
	
		r.setLogLevel('all');
		
		var response = r.execute();
		
		gs.log(response.getStatusCode());
		gs.log(response.getBody());
		
		var responseBody = response.getBody();
		var httpStatus = response.getStatusCode();
	
	
		var responseBody = response.getBody();

                var resp= JSON.parse(responseBody);

		
		var gr = new GlideRecord('u_vendor_inbound_tickets');
		
		gr.initialize();
		
		gr.u_id = resp.items[0].id;
		gr.u_caseclassification = resp.items[1].caseClassification;
		gr.u_status = resp.items[2].status;
		gr.u_substatus = resp.items[3].subStatus;
		 gr.u_priority = resp.items[4].priority;
		 gr.u_accessid = resp.items[5].accessId;
		 gr.u_subscriptionid = resp.items[6].subscriptionId;
		 gr.u_service = resp.items[7].service;
		 gr.u_serviceprovider = resp.items[8].serviceProvider;
		 gr.u_numberofexternallogs = resp.items[9].numberofExternalLogs;
		 gr.u_internalsystemreferenceid = resp.items[10].internalSystemReferenceId;
		 gr.u_contactname = resp.items[11].contactName;
		 gr.u_contactemail = resp.items[12].contactEmail;
		 gr.u_statusid = resp.items[13].statusId;
		 gr.u_affectedareazipcodes = resp.items[14].affectedAreaZipCodes;
		 gr.u_externalreferenceid = resp.items[15].externalReferenceId;
		 gr.u_description = resp.items[16].description;
		 gr.u_services = resp.items[17].services;		 		
				
		
		gr.insert();
						
	
}
	catch(ex) {
		var message = ex.getMessage();
	}

[A-N · Accepted solution]

When I format the JSON from your post I get this error

• Error:Expecting closing ] at end[Code 22, Structure 86]
• Error:Expecting closing } at end[Code 22, Structure 86]

(1) Ensure that your JSON format is correct

(2) Use a for loop to insert multiple records - See below

try {
	var r = new sn_ws.RESTMessageV2('Vendor Get Incidents', 'GET');
	
	
		r.setLogLevel('all');
		
		var response = r.execute();
		
		gs.log(response.getStatusCode());
		gs.log(response.getBody());
		
		var responseBody = response.getBody();
		var httpStatus = response.getStatusCode();
	
	
		var responseBody = response.getBody();

                var resp= JSON.parse(responseBody);

// Use a for loop to insert multiple records
           for(i=0;i<resp.length;i++)
             {
		
		var gr = new GlideRecord('u_vendor_inbound_tickets');
		
		gr.initialize();
		
		gr.u_id = resp.items[0].id;
		gr.u_caseclassification = resp.items[1].caseClassification;
		gr.u_status = resp.items[2].status;
		gr.u_substatus = resp.items[3].subStatus;
		 gr.u_priority = resp.items[4].priority;
		 gr.u_accessid = resp.items[5].accessId;
		 gr.u_subscriptionid = resp.items[6].subscriptionId;
		 gr.u_service = resp.items[7].service;
		 gr.u_serviceprovider = resp.items[8].serviceProvider;
		 gr.u_numberofexternallogs = resp.items[9].numberofExternalLogs;
		 gr.u_internalsystemreferenceid = resp.items[10].internalSystemReferenceId;
		 gr.u_contactname = resp.items[11].contactName;
		 gr.u_contactemail = resp.items[12].contactEmail;
		 gr.u_statusid = resp.items[13].statusId;
		 gr.u_affectedareazipcodes = resp.items[14].affectedAreaZipCodes;
		 gr.u_externalreferenceid = resp.items[15].externalReferenceId;
		 gr.u_description = resp.items[16].description;
		 gr.u_services = resp.items[17].services;		 		
				
		
		gr.insert();
		
             }	// End for-loop			
	
}
	catch(ex) {
		var message = ex.getMessage();
	}

Also, Use a for loop to iterate

[Community Alums]

Sorry, should have explained it better.

The REST response contains multiple items and looks like this:

{"items":[{"id":"000000000056255","caseClassification":"To be classified","status":"Working","subStatus":null,"priority":"Medel","accessId":null,"subscriptionId":null,"service":null,"serviceProvider":null,"numberOfExternalLogs":3,"internalSystemReferenceId":null,"contactName":"Alessandro Piccolo","contactEmail":null,"statusId":30,"affectedAreaZipCodes":null,"externalReferenceId":null,"description":"The description goes here","services":[],"createdAt":"2019-03-29T09:57:09+01:00","updatedAt":"2019-03-29T09:57:09+01:00"},{"id":"000000000056254","caseClassification":"To be classified","status":"Viewed","subStatus":null,"priority":"Medel","accessId":null,"subscriptionId":null,"service":null,"serviceProvider":null,"numberOfExternalLogs":0,"internalSystemReferenceId":null,"contactName":"Alessandro Piccolo","contactEmail":null,"statusId":25,"affectedAreaZipCodes":null,"externalReferenceId":null,"description":"The description goes here","services":[],"createdAt":"2019-03-28T10:59:47+01:00","updatedAt":"2019-03-28T10:59:47+01:00"},{"id":"000000000056253","caseClassification":"To be classified","status":"Working","subStatus":null,"priority":"Medel","accessId":null,"subscriptionId":null,"service":null,"serviceProvider":null,"numberOfExternalLogs":3,"internalSystemReferenceId":null,"contactName":"Alessandro Piccolo","contactEmail":null,"statusId":30,"affectedAreaZipCodes":null,"externalReferenceId":null,"description":"The description goes here","services":[],"createdAt":"2019-03-27T15:39:26+01:00","updatedAt":"2019-03-27T15:39:26+01:00"}], "currentCount": 3}    (Real count is 100, just cut it off here to show)

So the items are inside an array and it ends with "currentCount".

I tried the for loop and it inserted multiple records, but only the first item in the array. Still only "id", and "status" gets inserted.