Nested JSON string returns Object Object

[john_duchock]

I am having issues parsing a nest json string and keep getting a return of Object Object and was wondering if i could get some help from more skilled api developers.

The Goal:

Import data from a REST API that identifies a user associated with a store (user : store) along with other attributes and store that data in a custom table (u_import_store_user_data) where a transform map may perform further actions.

The String:

{"employees":[{"employeeid":"99999","lastname":"DOE","firstname":"JOHN","stores":{"1461":{"storename":"PITTSBURGH PA","storedist":"36"},"1340":{"storename":"COLUMBUS OH","storedist":"77"}},"regionNo":"3","rposition":"HC","empstatus":"AF"}]}

The Prettified Picture:

The Scheduled Job:

//Define a sleep function
//This will be used to pause the system between deleting old rows and creating new ones.  
//This prevents unneeded table growth in the import (pre-transform) table.

function sleep(milliseconds) {
	var start = new Date().getTime();
	for (var i = 0; i < 1e7; i++) {
		if ((new Date().getTime() - start) > milliseconds){
			break;
		}
	}
}

//Delete all rows in the import table
var gr = new GlideRecord('u_import_store_user_data');
gr.deleteMultiple();

//Pause for (10 seconds) while table is emptied
sleep(6000);

//System is now ready for imported records.
//Call the Store Import API and build records

var r = new sn_ws.RESTMessageV2('Employee Store [PROD]', 'GET');

var response = r.execute();
var jsonString = response.getBody();
//var httpStatus = response.getStatusCode();
var parser = new JSONParser();
var parsed = parser.parse(jsonString);

for (i = 0; i < parsed.employees.length; i++) {

	var employeeid = parsed.employees[i].employeeid;
	var lastname = parsed.employees[i].lastname;
	var firstname = parsed.employees[i].firstname;
	var regionNo = parsed.employees[i].regionNo;
	var rposition = parsed.employees[i].rposition;
	var empstatus = parsed.employees[i].empstatus;
	var store = parsed.employees[i].stores;

	gs.log("JCD LOG2: " + employeeid + ' : '+ firstname + " " + lastname + " : " + store);

	// Insert records into the import table where a transform map will run against them and update records in the production table 
	//        var rec = new GlideRecord('u_import_store_user_data');
	//          rec.initialize();
	//                     rec.u_employee_id = employeeid;
	//                     rec.u_last_name = lastname;
	// 	                   rec.u_first_name = firstname;
	// 	                   rec.u_region_number = regionNo;
	// 	                   rec.u_position = rposition;
	// 	                   rec.u_employee_status = empstatus;
	// 					   rec.u_store = store;

	//          rec.insert();


}

The Result:

This should be returning:

JCD LOG2: 99999 : JOHN DOE : 1340

JCD LOG2: 99999 : JOHN DOE : 1461

JCD LOG2: 148500 : AALIYAH WHITE : 1383

etc...

But it seems i am doing something wrong in the scheduled job....

Any help would be appreciated...

[Omkar Mone]

Hi,

Please use below updated script:

=========================================

for (i = 0; i < jsonString.employees.length; i++) {

var employeeid = jsonString.employees[i].employeeid;
var lastname = jsonString.employees[i].lastname;
var firstname = jsonString.employees[i].firstname;
var regionNo = jsonString.employees[i].regionNo;
var rposition = jsonString.employees[i].rposition;
var empstatus = jsonString.employees[i].empstatus;
var store = jsonString.employees[i].stores;

var len = Object.keys(store).length;
for(var j =0; j< len; j++)
{
gs.print("JCD LOG2: " + employeeid + ' : '+ firstname + " " + lastname + " : " + Object.keys(store)[j]);
}

}

o/p:

*** Script: JCD LOG2: 99999 : JOHN DOE : 1461
*** Script: JCD LOG2: 99999 : JOHN DOE : 1340

Hope this helps.

Regards,

Omkar Mone.

www.dxsherpa.com

[vkachineni · Accepted solution]

var msg = '{"employees":[{"employeeid":"99999","lastname":"DOE","firstname":"JOHN","stores":{"1461":{"storename":"PITTSBURGH PA","storedist":"36"},"1340":{"storename":"COLUMBUS OH","storedist":"77"}},"regionNo":"3","rposition":"HC","empstatus":"AF"}]}';
var parsed = JSON.parse(msg);
for (i = 0; i <= parsed.employees.length-1; i++) {
  
  for (j = 0; j <= Object.keys(parsed.employees[i].stores).length-1; j++) {
  console.log(parsed.employees[i].employeeid+ " " + parsed.employees[i].lastname + " " +  parsed.employees[i].firstname + " " + Object.keys(parsed.employees[i].stores)[j]);
  
  }
}


'99999 DOE JOHN 1340'
'99999 DOE JOHN 1461'

[john_duchock]

This looks like it is working.  Going to add the API call to it, and map it to the target table and see what i get...

[john_duchock]

I added field mappings to the pre-transform table and the value for stores is coming up NULL...  not sure why...

var r = new sn_ws.RESTMessageV2('Employee Store [PROD]', 'GET');

var response = r.execute();
var msg = response.getBody();


var parsed = JSON.parse(msg);
for (i = 0; i <= parsed.employees.length-1; i++) {

	for (j = 0; j <= Object.keys(parsed.employees[i].stores).length-1; j++) {
		gs.log("JCD LOG50: " +parsed.employees[i].employeeid+ " " + parsed.employees[i].lastname + " " +  parsed.employees[i].firstname + " " + Object.keys(parsed.employees[i].stores)[j]);
	}
	
	
	
	var rec = new GlideRecord('u_import_store_user_data');
		rec.initialize();
		rec.u_employee_id = parsed.employees[i].employeeid;
		rec.u_last_name = parsed.employees[i].lastname;
		rec.u_first_name = parsed.employees[i].firstname;
		rec.u_store = Object.keys(parsed.employees[i].stores)[j].toString();
	rec.insert();

}

[vkachineni]

//Move the insert record code inside the for loop

//Also put a log for the variable msg after getBody to see the value. 

var r = new sn_ws.RESTMessageV2('Employee Store [PROD]', 'GET');

var response = r.execute();
var msg = response.getBody();


var parsed = JSON.parse(msg);
for (i = 0; i <= parsed.employees.length-1; i++) {

	for (j = 0; j <= Object.keys(parsed.employees[i].stores).length-1; j++) {
		gs.log("JCD LOG50: " +parsed.employees[i].employeeid+ " " + parsed.employees[i].lastname + " " +  parsed.employees[i].firstname + " " + Object.keys(parsed.employees[i].stores)[j]);
		
		var rec = new GlideRecord('u_import_store_user_data');
		rec.initialize();
		rec.u_employee_id = parsed.employees[i].employeeid;
		rec.u_last_name = parsed.employees[i].lastname;
		rec.u_first_name = parsed.employees[i].firstname;
		rec.u_store = Object.keys(parsed.employees[i].stores)[j].toString();
		rec.insert();
	}
}