Below is JSON data representing a sample payroll. For this example, we will be calculating the total number of employees per department as well as the average salary in each department.

{
  "employees": [{
    "department": "Tools",
    "firstName": "Blanca",
    "lastName": "Braun",
    "jobTitle": "Senior Applications Designer",
    "salary": "90649.05"
  }, {
    "department": "Kids",
    "firstName": "Evie",
    "lastName": "Hand",
    "jobTitle": "Dynamic Data Developer",
    "salary": "112421.56"
  }, {
    "department": "Garden",
    "firstName": "Laurine",
    "lastName": "Grimes",
    "jobTitle": "Dynamic Mobility Producer",
    "salary": "79557.53"
  }, {
    "department": "Kids",
    "firstName": "Electa",
    "lastName": "Grant",
    "jobTitle": "Dynamic Web Executive",
    "salary": "70659.13"
  }, {
    "department": "Toys",
    "firstName": "Antonette",
    "lastName": "Brakus",
    "jobTitle": "Global Intranet Liaison",
    "salary": "77529.69"
  }, {
    "department": "Games",
    "firstName": "Murray",
    "lastName": "Predovic",
    "jobTitle": "Forward Accounts Producer",
    "salary": "94251.07"
  }, {
    "department": "Tools",
    "firstName": "Hipolito",
    "lastName": "Schuppe",
    "jobTitle": "Customer Accounts Representative",
    "salary": "65794.58"
  }, {
    "department": "Tools",
    "firstName": "Guillermo",
    "lastName": "Fadel",
    "jobTitle": "Product Program Facilitator",
    "salary": "73314.76"
  }, {
    "department": "Movies",
    "firstName": "Isabelle",
    "lastName": "Collins",
    "jobTitle": "National Quality Technician",
    "salary": "101208.32"
  }, {
    "department": "Toys",
    "firstName": "Judge",
    "lastName": "Wehner",
    "jobTitle": "Chief Response Specialist",
    "salary": "62509.47"
  }, {
    "department": "Home",
    "firstName": "Emilie",
    "lastName": "Kuvalis",
    "jobTitle": "Central Research Engineer",
    "salary": "88068.85"
  }, {
    "department": "Home",
    "firstName": "Raleigh",
    "lastName": "Stamm",
    "jobTitle": "Forward Identity Agent",
    "salary": "112875.56"
  }, {
    "department": "Home",
    "firstName": "Avis",
    "lastName": "Herman",
    "jobTitle": "District Accountability Consultant",
    "salary": "101049.62"
  }, {
    "department": "Games",
    "firstName": "Don",
    "lastName": "Jenkins",
    "jobTitle": "Forward Usability Orchestrator",
    "salary": "119551.50"
  }, {
    "department": "Movies",
    "firstName": "Bethany",
    "lastName": "Barton",
    "jobTitle": "Direct Tactics Administrator",
    "salary": "50132.78"
  }, {
    "department": "Garden",
    "firstName": "Lois",
    "lastName": "Cormier",
    "jobTitle": "Chief Functionality Strategist",
    "salary": "58224.66"
  }, {
    "department": "Toys",
    "firstName": "Shany",
    "lastName": "Schaefer",
    "jobTitle": "District Infrastructure Officer",
    "salary": "32563.79"
  }, {
    "department": "Games",
    "firstName": "Edna",
    "lastName": "Kub",
    "jobTitle": "Internal Mobility Director",
    "salary": "39433.80"
  }, {
    "department": "Kids",
    "firstName": "Ramona",
    "lastName": "Friesen",
    "jobTitle": "Forward Communications Director",
    "salary": "87064.58"
  }, {
    "department": "Computers",
    "firstName": "Deron",
    "lastName": "Beier",
    "jobTitle": "Global Accountability Executive",
    "salary": "66089.03"
  }]
}

JavaScript Transform

Instructions:

1. Download this payroll JSON file.
2. Create a Server Filesystem Connection.
   
3. Create a JSON Feed off of the previously created connection, configured as follows:
   a) In File Name, upload the payroll.json file and select it from the dropdown.
   b) In JSON Parser > JSON Path, enter $.employees[*]
   c) In JSON Path Results, click Select First Level Only.
   d) Go to Data Preview to view the preliminary JSON dataset.
4. Create a JavaScript Transform off of the JSON feed, configured as follows:
   a) Provide a Transform Name (for example, PayrollAnalysis).
   b) In Script References > Referenced Datasets, make sure Payroll is selected.
   c) In Script, paste the following:
   
   

// using $.employees[*] would result in the following attributes: department, firstName, lastName, jobTitle, salary
function getAttributes(sourceAttrs, nodeVars, secVars, sourceRecords) {
    var attributes = [];
    // we only have one producer in this case, so we know sourceAttrs[0] is the source we need
    sourceAttrs[0].forEach(function(attr) {
        if (attr.name === 'department') {
            // department is the only original attribute that we need
            attributes.push(attr);
        }
    });
    // we also need to add two new attributes: numEmployees and avgSalary
    attributes.push({ name : 'numEmployees', type : 'int' });
    attributes.push({ name : 'avgSalary', type : 'number' });
    return jsAttributesSuccess(attributes);
}
 
function getRecords(sourceRecords, nodeVars, secVars, attributes) {
    var records = [];
    var deptInfo = {};
    // again, with one producer, we know sourceRecords[0] is the source we need
    sourceRecords[0].forEach(function(record){
      var dept = record.department;
      if (deptInfo[dept]) {
        // if we have already begun tracking info for a department, add to the existing data
        deptInfo[dept].count++;
        deptInfo[dept].salarySum += record.salary;
        deptInfo[dept].salarySum = parseFloat(deptInfo[dept].salarySum);
      } else {
        // we've not yet encountered this department, so create a new index for it
        deptInfo[dept] = { count : 1, salarySum : record.salary };
      }
    });
    // having aggregated the department data, we can now create the records we need
    for (var dept in deptInfo) {
        records.push({
            department : dept,
            numEmployees : deptInfo[dept].count,
            // calculation of the avgSalary value can optionally be wrapped in parseFloat
            // toFixed() returns a string, but the server can look at the avgSalary attribute definition and cast the value to a number
            avgSalary : (deptInfo[dept].salarySum / deptInfo[dept].count).toFixed(2)
        });
    }
    return jsRecordsSuccess(records);
}

      d) Go to Data Preview to view the end result where the total number of employees per department as well as the average salary in each department are calculated.