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, Workday 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
• Variables: Reusable named expressions defined once and referenced as $varName in mappings and validations
• 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
  • $search() - Deep recursive key-value search in nested data
  • $searchAll() - Returns all matches instead of first
  • $formatAddress() - Address string cleanup (encoded newlines → comma-separated)
• Validation rules: Define pre-transformation validation with configurable actions (SKIP, ERROR, WARN)
• Type safety: Fully typed with TypeScript
• Comprehensive testing: 130+ unit and integration tests covering all functionality

Project Structure

field-mapping-engine/
├── 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/                      # Tests
│   ├── fixtures/
│   │   ├── config/            # Mapping configs (*.mapping.json)
│   │   └── data/              # Input JSON + expected output (*.expected.json)
│   ├── integration.test.ts    # Auto-discovered fixture-based integration tests
│   ├── engine.test.ts         # Engine unit tests (variables, $search, etc.)
│   ├── functions.test.ts      # Custom function unit tests
│   └── validator.test.ts      # Validator unit tests
├── dist/                      # Compiled JavaScript (generated)
├── coverage/                  # Test coverage reports (generated)
├── CLAUDE.md                  # AI assistant onboarding guide
└── CHANGELOG.md               # Version history

Installation

yarn install

Building

Compile TypeScript to JavaScript:

yarn build

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

Testing

Run all tests (unit + integration):

yarn test

Run integration tests only:

yarn test:integration

Run tests in watch mode:

yarn test:watch

Generate coverage report:

yarn test:coverage

Coverage reports are generated in the coverage/ directory.

Usage

Defining a Mapping Configuration

Create a JSON file following the naming convention {source}_{object}_{dest}_{object}.mapping.json:

{
  "$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"
      }
    }
  ],

  "variables": [
    {
      "name": "contact",
      "expression": "Personal_Data.Contact_Data"
    }
  ],

  "validations": [
    {
      "label": "Require Email",
      "condition": "$not($exists(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')"
    },
    {
      "target": "WorkEmail",
      "expression": "$search($contact.Emails, 'type', 'WORK').address"
    }
  ]
}

Using the Engine Programmatically

import MappingEngine from 'field-mapping-engine';
import * as fs from 'fs';

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

// Create engine instance
const engine = new MappingEngine(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.status === 'SUCCESS') {
  console.log('Transformed data:', result.data);
} else if (result.status === 'ERROR') {
  console.log('Errors:', result.errors);
} else {
  console.log('Record skipped');
}

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
• variables - Reusable named expressions (evaluated top-to-bottom, later variables can reference earlier ones)
• 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).

$search(arrayOrObject, key, value)

Deep recursive search for the first item where a nested key exactly matches the given key name and value. Returns the top-level array element containing the match.

{
  "expression": "$search(Contact.Emails, 'type', 'WORK').address"
}

$searchAll(arrayOrObject, key, value)

Like $search, but returns all matching items instead of just the first.

{
  "expression": "$count($searchAll(items, 'type', 'A'))"
}

$formatAddress(raw)

Cleans address strings by replacing encoded newlines (
, 
, \n) with commas. Supports the pipe operator.

{
  "expression": "rawAddress ~> $formatAddress"
}

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+
• Yarn

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
yarn test

# Run specific test file
yarn test -- --testPathPatterns=engine

# Watch mode for TDD
yarn test:watch

# With coverage
yarn test:coverage

Type Checking

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

npx tsc --noEmit

License

ISC