Field Mapping Engine

A TypeScript-based data transformation engine for mapping and transforming data between different systems using JSONata expressions.

Overview

The Field Mapping Engine provides a flexible, configuration-driven approach to transforming data between systems (e.g., Salesforce to Skedulo). It uses JSON configuration files to define field mappings, validations, and value translations, with JSONata as the expression language for transformations.

This is primarily an exploration of how we can use JSONata to build powerful field mapping into our integrations layer.

Features

• Configuration-driven mapping: Define mappings in JSON files with JSONata expressions
• JSON Schema validation: Automatic validation of mapping configuration structure
• JSONata expression validation: Validates that all expressions are syntactically correct
• Custom functions: Built-in functions for common transformation tasks:
  • $translate() - Value translation using mapping tables
  • $lookup() - Foreign key resolution
  • $formatPhone() - Phone number formatting to E.164 standard
• Validation rules: Define pre-transformation validation with configurable actions (SKIP, ERROR, WARN)
• Type safety: Fully typed with TypeScript
• Comprehensive testing: 100+ unit tests covering all functionality

Project Structure

field-mapping-engine/
├── config/                    # Mapping configuration files
│   ├── salesforce_contact_skedulo_contact.mapping.json
│   └── salesforce_account_skedulo_account.mapping.json
├── data/                      # Sample source data for testing
│   ├── salesforce_contacts.json
│   └── salesforce_accounts.json
├── schema/                    # JSON Schema definitions
│   └── mapping.schema.json
├── src/                       # TypeScript source code
│   ├── engine.ts              # Core transformation engine
│   ├── functions.ts           # Custom JSONata functions
│   ├── validator.ts           # Configuration validator
│   └── types.ts               # TypeScript type definitions
├── test/                      # Unit tests
│   ├── engine.test.ts
│   ├── functions.test.ts
│   └── validator.test.ts
├── dist/                      # Compiled JavaScript (generated)
├── coverage/                  # Test coverage reports (generated)
└── validate-mappings.js       # CLI tool to validate mapping files

Installation

npm install

Building

Compile TypeScript to JavaScript:

npm run build

This compiles the TypeScript source in src/ to JavaScript in dist/.

Testing

Run all tests:

npm test

Run tests in watch mode:

npm run test:watch

Generate coverage report:

npm run test:coverage

Coverage reports are generated in the coverage/ directory.

Validation

Validate all mapping configuration files:

npm run validate

This command:

• Checks JSON syntax
• Validates against the JSON Schema
• Validates all JSONata expressions
• Reports any errors found

Usage

Defining a Mapping Configuration

Create a JSON file in the config/ directory:

{
  "$schema": "../schema/mapping.schema.json",
  "integration_id": "example_mapping",
  "sourceSystem": "SourceSystem",
  "sourceObject": "Contact",
  "destinationSystem": "DestinationSystem",
  "destinationObject": "Person",
  
  "translations": [
    {
      "name": "StatusMap",
      "mappings": {
        "Active": "Enabled",
        "Inactive": "Disabled"
      }
    }
  ],
  
  "validations": [
    {
      "label": "Require Email",
      "condition": "$exists(Email) and Email != ''",
      "action": "ERROR",
      "message": "Email is required"
    }
  ],
  
  "mappings": [
    {
      "target": "FullName",
      "expression": "FirstName & ' ' & LastName",
      "label": "Concatenate first and last name"
    },
    {
      "target": "Status",
      "expression": "$translate(Status, 'StatusMap', 'Unknown')"
    },
    {
      "target": "Phone",
      "expression": "$formatPhone(Phone, 'US')"
    }
  ]
}

Using the Engine Programmatically

import IntegrationEngine from './src/engine';
import * as fs from 'fs';

// Load mapping configuration
const mappingConfig = JSON.parse(
  fs.readFileSync('./config/my_mapping.mapping.json', 'utf8')
);

// Create engine instance
const engine = new IntegrationEngine(mappingConfig);

// Process a source record
const sourceRecord = {
  FirstName: 'John',
  LastName: 'Doe',
  Email: '[email protected]',
  Status: 'Active',
  Phone: '555-123-4567'
};

const result = await engine.processRecord(sourceRecord);

if (result.success) {
  console.log('Transformed data:', result.data);
} else {
  console.log('Validation errors:', result.validationErrors);
}

Mapping Configuration Reference

Required Fields

• integration_id - Unique identifier for the mapping
• sourceSystem - Name of the source system
• sourceObject - Source object/entity type
• destinationSystem - Name of the destination system
• destinationObject - Destination object/entity type
• mappings - Array of field mappings (at least one required)

Optional Fields

• $schema - Reference to JSON Schema (enables IDE validation)
• version - Version of the mapping configuration
• translations - Named translation maps for value conversions
• validations - Validation rules to apply before transformation

Validation Actions

• SKIP - Skip processing this record (silent)
• ERROR - Reject the record with an error
• WARN - Log a warning but continue processing

Custom Functions

$translate(value, mapName, defaultValue)

Translates a value using a named translation map.

{
  "expression": "$translate(Status, 'StatusMap', 'Unknown')"
}

$lookup(foreignKey, objectType, fieldName)

Performs a database lookup to resolve a foreign key (async).

{
  "expression": "$lookup(Account_Id__c, 'Account', 'Salesforce_Id__c')"
}

$formatPhone(phoneNumber, countryCode)

Formats a phone number to E.164 standard (+[country code][number]).

{
  "expression": "$formatPhone(Phone, 'US')"
}

Supports 2-letter country codes (AU, US, GB, etc.) or numeric codes (61, 1, 44).

JSONata Expressions

The engine uses JSONata for expression evaluation. JSONata provides:

• Field access: FirstName, Account.Name
• String concatenation: FirstName & ' ' & LastName
• Conditional logic: Age >= 18 ? 'Adult' : 'Minor'
• Built-in functions: $exists(), $length(), $lowercase(), $uppercase()
• Array operations: $count(), $filter(), $map()
• And much more...

See the JSONata documentation for full expression syntax.

Development

Prerequisites

• Node.js 18+
• npm

Dependencies

Runtime:

• jsonata - JSONata expression evaluation
• ajv - JSON Schema validation

Development:

• typescript - TypeScript compiler
• jest - Testing framework
• ts-jest - Jest TypeScript integration
• @types/* - Type definitions

Running Tests During Development

# Run all tests
npm test

# Run specific test file
npm test -- test/functions.test.ts

# Watch mode for TDD
npm run test:watch

# With coverage
npm run test:coverage

Type Checking

The project uses strict TypeScript configuration. Type check without compiling:

npx tsc --noEmit

License

ISC