Skip to main content

Join@1

Node Join@1 performs inner join operations between source data and lookup arrays based on matching key values. It combines records from two datasets similar to SQL joins, adding all matching records from the lookup array to each source record.

Adapter Prerequisites​

  • General availability: All adapters support this node type.

Node Configuration​

For fields path, targetPath, targetValueWriteMode, and targetValueKind, see Overview.

transformations:
- type: Join@1
  path: $.orders[*]                # Path to the source objects to join
  keyPath: $.orderId              # Path to the key field in source objects
  joinPath: $.orderItems[*]       # Path to the array of objects to join with
  joinKeyPath: $.orderId          # Path to the key field in join objects
  itemPath: $.items               # Path where joined records will be stored

Parameters​

Required Parameters​

ParameterTypeDescription
pathstringJSON path to the source objects that will receive joined data
keyPathstringRelative path to the key field in source objects used for matching
joinPathstringJSON path to the array of objects to join with the source data
joinKeyPathstringRelative path to the key field in join objects used for matching
itemPathstringRelative path in source objects where the array of matched records will be stored

Join Operation​

The join operation follows these steps:

  1. Select Source Records: Uses path to select source objects that will receive joined data
  2. Select Join Records: Uses joinPath to select the array of objects available for joining
  3. Extract Source Keys: For each source object, extracts the key value using keyPath
  4. Find Matches: Searches join objects for records where joinKeyPath matches the source key
  5. Store Results: Creates an array of all matching join records and stores it at itemPath

Key Matching​

  • String comparison is case-sensitive and uses exact matching
  • If no matching records are found for a source object, an empty array is stored at itemPath
  • Multiple join records can match a single source record (many-to-many relationship)

Error Handling​

The node will throw an error if:

  • The input data context is null
  • No source data is found at the specified path
  • No join data is found at the specified joinPath
  • A source record has no key value at the specified keyPath

Examples​

Example 1: Orders with Order Items​

Join orders with their corresponding order items:

transformations:
- type: Join@1
  path: $.orders[*]
  keyPath: $.orderId
  joinPath: $.orderItems[*]
  joinKeyPath: $.orderId
  itemPath: $.items

Input:

{
  "orders": [
    { "orderId": "123", "customerName": "John" },
    { "orderId": "456", "customerName": "Jane" }
  ],
  "orderItems": [
    { "orderId": "123", "productName": "Widget", "quantity": 2 },
    { "orderId": "123", "productName": "Gadget", "quantity": 1 },
    { "orderId": "456", "productName": "Tool", "quantity": 3 }
  ]
}

Output:

{
  "orders": [
    {
      "orderId": "123",
      "customerName": "John",
      "items": [
        { "orderId": "123", "productName": "Widget", "quantity": 2 },
        { "orderId": "123", "productName": "Gadget", "quantity": 1 }
      ]
    },
    {
      "orderId": "456",
      "customerName": "Jane",
      "items": [
        { "orderId": "456", "productName": "Tool", "quantity": 3 }
      ]
    }
  ],
  "orderItems": [
    { "orderId": "123", "productName": "Widget", "quantity": 2 },
    { "orderId": "123", "productName": "Gadget", "quantity": 1 },
    { "orderId": "456", "productName": "Tool", "quantity": 3 }
  ]
}

Example 2: Customers with Addresses​

Join customers with their multiple addresses:

transformations:
- type: Join@1
  path: $.customers[*]
  keyPath: $.customerId
  joinPath: $.addresses[*]
  joinKeyPath: $.customerId
  itemPath: $.customerAddresses

Input:

{
  "customers": [
    { "customerId": "C001", "name": "Alice" },
    { "customerId": "C002", "name": "Bob" }
  ],
  "addresses": [
    { "customerId": "C001", "type": "home", "street": "123 Main St" },
    { "customerId": "C001", "type": "work", "street": "456 Oak Ave" },
    { "customerId": "C002", "type": "home", "street": "789 Pine Rd" }
  ]
}

Output:

{
  "customers": [
    {
      "customerId": "C001",
      "name": "Alice",
      "customerAddresses": [
        { "customerId": "C001", "type": "home", "street": "123 Main St" },
        { "customerId": "C001", "type": "work", "street": "456 Oak Ave" }
      ]
    },
    {
      "customerId": "C002",
      "name": "Bob",
      "customerAddresses": [
        { "customerId": "C002", "type": "home", "street": "789 Pine Rd" }
      ]
    }
  ],
  "addresses": [
    { "customerId": "C001", "type": "home", "street": "123 Main St" },
    { "customerId": "C001", "type": "work", "street": "456 Oak Ave" },
    { "customerId": "C002", "type": "home", "street": "789 Pine Rd" }
  ]
}

Example 3: Products with Categories​

Join products with category information:

transformations:
- type: Join@1
  path: $.products[*]
  keyPath: $.categoryId
  joinPath: $.categories[*]
  joinKeyPath: $.id
  itemPath: $.categoryDetails

Input:

{
  "products": [
    { "id": "P001", "name": "Laptop", "categoryId": "CAT1" },
    { "id": "P002", "name": "Mouse", "categoryId": "CAT1" },
    { "id": "P003", "name": "Book", "categoryId": "CAT2" }
  ],
  "categories": [
    { "id": "CAT1", "name": "Electronics", "department": "Tech" },
    { "id": "CAT2", "name": "Books", "department": "Media" }
  ]
}

Output:

{
  "products": [
    {
      "id": "P001",
      "name": "Laptop",
      "categoryId": "CAT1",
      "categoryDetails": [
        { "id": "CAT1", "name": "Electronics", "department": "Tech" }
      ]
    },
    {
      "id": "P002",
      "name": "Mouse",
      "categoryId": "CAT1",
      "categoryDetails": [
        { "id": "CAT1", "name": "Electronics", "department": "Tech" }
      ]
    },
    {
      "id": "P003",
      "name": "Book",
      "categoryId": "CAT2",
      "categoryDetails": [
        { "id": "CAT2", "name": "Books", "department": "Media" }
      ]
    }
  ],
  "categories": [
    { "id": "CAT1", "name": "Electronics", "department": "Tech" },
    { "id": "CAT2", "name": "Books", "department": "Media" }
  ]
}

Example 4: No Matching Records​

When no matching records are found, an empty array is created:

transformations:
- type: Join@1
  path: $.employees[*]
  keyPath: $.departmentId
  joinPath: $.departments[*]
  joinKeyPath: $.id
  itemPath: $.departmentInfo

Input:

{
  "employees": [
    { "id": "E001", "name": "John", "departmentId": "DEPT99" }
  ],
  "departments": [
    { "id": "DEPT1", "name": "Engineering" },
    { "id": "DEPT2", "name": "Marketing" }
  ]
}

Output:

{
  "employees": [
    {
      "id": "E001",
      "name": "John",
      "departmentId": "DEPT99",
      "departmentInfo": []
    }
  ],
  "departments": [
    { "id": "DEPT1", "name": "Engineering" },
    { "id": "DEPT2", "name": "Marketing" }
  ]
}

Use Cases​

  • Order Processing: Join orders with their line items, shipping details, or payment information
  • Customer Data: Combine customer records with addresses, preferences, or transaction history
  • Product Catalogs: Join products with categories, specifications, or pricing information
  • Data Enrichment: Add reference data to primary records from lookup tables
  • Hierarchical Data: Combine parent records with their child records
  • Report Generation: Aggregate related data from multiple sources for reporting

Notes​

  • Many-to-Many Joins: The node supports many-to-many relationships where one source record can match multiple join records
  • Array Results: Matched records are always stored as an array, even if only one match is found
  • Original Data Preserved: Both source and join arrays remain unchanged in the output
  • Path Requirements: All paths (keyPath, joinKeyPath, itemPath) are relative to their respective object contexts
  • Performance: Large datasets may impact performance; consider filtering data before joining when possible
  • Data Types: Key values are compared as strings, so ensure consistent data types