Skip to main content

"getRecordsTransform" code package

Transform data as it is read from the database

Tutorial on creating a JavaScript transform

This tutorial creates a Code Package to use when retrieving records from JSON DB. The advantage of this method is that the data in the database is not affected. Use this method when you want to preserve the original data.

This tutorial assumes that you have already logged in using createSession and have a valid authToken.

Create a table to use this Code Package against

POST the following JSON to the FairCom endpoint:

{
  "api": "db",
  "action": "createTable",
  "params": {
    "databaseName": "faircom",
    "tableName": "testTable",
    "fields": [
      {
        "name": "numberField",
        "type": "double"
      },
      {
        "name": "numberFieldRounded",
        "type": "double"
      },
      {
        "name": "stringField",
        "type": "varchar",
        "length": 50
      },
      {
        "name": "stringFieldTrimmed",
        "type": "varchar",
        "length": 50
      }
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

Replace the authToken with the one from your current session

Insert records into the table

POST the following JSON to the FairCom endpoint:

{
  "api": "db",
  "action": "insertRecords",
  "params": {
    "databaseName": "faircom",
    "tableName": "testTable",
    "dataFormat": "objects",
    "sourceData": [
      {
        "numberField": 1234.5678,
        "numberFieldRounded": 1234.5678,
        "stringField": "   FairCom   ",
        "stringFieldTrimmed": "   FairCom   "
      },
      {
        "numberField": 8765.4321,
        "numberFieldRounded": 8765.4321,
        "stringField": "   Edge   ",
        "stringFieldTrimmed": "   Edge   "
      },
      {
        "numberField": 2112.2112,
        "numberFieldRounded": 2112.2112,
        "stringField": "   Rush   ",
        "stringFieldTrimmed": "   Rush   "
      }
    ]
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

Note that each varchar field has spaces around the record and that each double field has four digits after the decimal place.

Create JavaScript code

POST the following JSON to the FairCom endpoint:

{
  "api": "admin",
  "action": "createCodePackage",
  "params": {
    "ownerName": "admin",
    "databaseName": "faircom",
    "codeName": "roundAndTrim",
    "codeLanguage": "javascript",
    "serviceName": "javascript",
    "codeType": "getRecordsTransform",
    "codeStatus": "active",
    "description": "Test transform for rounding a numeric field and trimming a string field",
    "codeFormat": "utf8",
    "comment": "Original code",
    "code": "records.forEach( function( record ){ record.numberFieldRounded = parseFloat( record.numberFieldRounded.toFixed( 2 ) );record.stringFieldTrimmed = record.stringField.trim(); } );\n"
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

Note that the actual code when formatted looks like this:

records.forEach
(
  function( record )
  {
    record.numberFieldRounded = parseFloat( record.numberFieldRounded.toFixed( 2 ) );
    record.stringFieldTrimmed = record.stringField.trim();
  }
);

Here is a pseudocode explanation for this Code Package:

For each record processed:

  1. Modify the numberFieldRounded field to two fixed decimal places

  2. Modify the stringFieldTrimmed field by trimming leading and trailing whitespace

Retrieve records using the code package

POST the following JSON to the FairCom endpoint:

{
  "api": "db",
  "action": "getRecordsByTable",
  "params": {
    "databaseName": "faircom",
    "tableName": "testTable",
    "transformCodeName": "roundAndTrim"
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

Note that this is a normal getRecordsByTable request with one exception: the params block contains the "transformCodeName" property, which is set to the name of our Code Package "roundAndTrim".

The response from that getRecordsByTable will contain a data block that looks like this:

[
  {
    "changeId": 1525,
    "id": 1,
    "numberField": 1234.5678,
    "numberFieldRounded": 1234.57,
    "stringField": "   FairCom   ",
    "stringFieldTrimmed": "FairCom"  },
  {
    "changeId": 1525,
    "id": 2,
    "numberField": 8765.4321,
    "numberFieldRounded": 8765.43,
    "stringField": "   Edge   ",
    "stringFieldTrimmed": "Edge"  },
  {
    "changeId": 1525,
    "id": 3,
    "numberField": 2112.2112,
    "numberFieldRounded": 2112.21,
    "stringField": "   Rush   ",
    "stringFieldTrimmed": "Rush"
  }
]

Note that the "numberFieldRounded" fields have only two decimal places and all of the "stringFieldTrimmed" fields have no leading or trailing whitespace.

You can confirm that the Code Package worked by calling getRecordsByTable again, but without the "transformCodeName" property:

{
  "api": "db",
  "action": "getRecordsByTable",
  "params": {
    "databaseName": "faircom",
    "tableName": "testTable"
  },
  "authToken": "replaceWithAuthTokenFromCreateSession"
}

The response will contain a data block that looks like this:

[
  {
    "changeId": 3362,
    "id": 1,
    "numberField": 1234.5678,
    "numberFieldRounded": 1234.5678,
    "stringField": "   FairCom   ",
    "stringFieldTrimmed": "   FairCom   "  },
  {
    "changeId": 3362,
    "id": 2,
    "numberField": 8765.4321,
    "numberFieldRounded": 8765.4321,
    "stringField": "   Edge   ",
    "stringFieldTrimmed": "   Edge   "  },
  {
    "changeId": 3362,
    "id": 3,
    "numberField": 2112.2112,
    "numberFieldRounded": 2112.2112,
    "stringField": "   Rush   ",
    "stringFieldTrimmed": "   Rush   "
  }
]

The "numberFieldRounded" fields and the "stringFieldTrimmed" fields show that the data in the database has not been affected by the Code Package.

Return modified records

Embed this JavaScript code in a "createCodePackage" action to save the code to the server. To transform the results of the query, put the code package name in the "transformCodeName" property in any "getRecords..." query.

records.forEach(record => {

  try {
    record.temperatureRounded = Number(record.temperature.toFixed(2))
    record.deviceName = record.deviceName.trim();
  }
  catch(err) {
    record.transformError = err.message
  }
});

A "codeType": "getRecordsTransform" example returning analytics

Embed this JavaScript code in a "createCodePackage" action to save the code to the server. To return the analytics of the query, put the code package name in the "transformCodeName" property in any "getRecords..." query.

try {

  count = 0;
  temperatureTotal = 0.0;
  averageTemperature = 0.0;

  //Calculate analytics
  records.forEach(record => {
    count++;
    temperatureTotal = temperatureTotal + record.temperature;   
  });

  //Return analytics
  averageTemperature = temperatureTotal / count;
  records = [
    {
      "recordCount": count,
      "averageTemperature": averageTemperature
    }
  ];
  
}
catch(err) {
  //Return an error
  records = [
    {
      "transformError": err.message,
      "recordCount": null,
      "averageTemperature": null
    }
  ];
}

getRecordsTransform javascript transform tutorial for code packages

code package
codepackge
tutorial
getRecordsTransform
javascript transform