GUBUS JOIN Transform

Overview

JOIN enriches objects in localObjs by merging data from external sources (side objects (literally previous schemas result) & rules. Think of it as SQL JOIN operations. But GUBUS JOIN for Google Spreadsheets.

Key characteristics:

• 3 projection types: full_join, some_fields_join, blind_one_obj_join
• Explicit one-to-many cardinality when one base object must become one result object per source match
• Optional matched-only retention for one-to-many results
• Joins based on union field matching (like SQL ON clause)
• IS ABLE to rename fields during join with colon syntax
• Optional error handling with break_on_false_join

---

False-match behavior

The break_on_false_join field controls how the JOIN transform reports cases where a base object does not find a matching source object. It supports three options:

• ignore: Ignores and does not expose falsy joins. Use this when the join is optional and missing data is acceptable.
• inform_error: Continues execution, but accumulates mistakes (failed joins) to expose them after execution. Use this when false joins are allowable, but should be reviewed and repaired in the future.
• brake: On a false join, execution is immediately stopped, the main result is falsified, and no posting or further processing happens. Use this when the join is mandatory and missing data is a critical error.

This allows fine-grained control over error handling in JOIN operations, supporting both strict and flexible data enrichment scenarios.

For one-to-many JOINs, keep_only_matched_base_objects: true separately controls result retention. With the flag enabled, unmatched base objects are omitted from the JOIN result. Without it, the JOIN remains left-preserving and emits one unchanged/projected base object for a miss. Use break_on_false_join: "ignore" together with the flag for a silent inner-join-style filter, or inform_error to omit misses while still reporting them.

---

How It Works

[Load join source: side_obj/rule/local]
	→ [Index on the ordered union_field_in_join key]
	→ [For each local object]
			→ [Lookup by every paired base/source match field]
			→ [Merge fields based on join_type]
			→ [Apply field renaming if [:] is present]

Indexing on the join field ensures O(1) lookup for large datasets. By default the first match is used. With join_multiplicity: "one_to_many", every match is used and the base object is duplicated once per match.

Multiple match fields

Use ordered comma-separated lists when one field is not enough to identify a match. Fields at the same position form a pair, and every pair must match:

{
	"union_field_in_base_obj": "product_name,site",
	"union_field_in_join": "item_name,site_name"
}

This is equivalent to base.product_name = source.item_name AND base.site = source.site_name. Both lists must contain the same number of fields. The same composite key works for first-match and one-to-many JOINs.

{
	"join_multiplicity": "one_to_many",
	"keep_only_matched_base_objects": true,
	"break_on_false_join": "ignore"
}

In this mode, a base object with five matches produces five objects, while a base object with no matches produces none.

---

Join Types

FULL_JOIN

Copies all fields from joined object to base object.

{
	"join_type": "full_join",
	"join_source": "side_obj",
	"join_source_ref": "customerData",
	"union_field_in_base_obj": "customer_id",
	"union_field_in_join": "id"
}

Base: {order_id: "A1", customer_id: "C123", total: 100}
Join: {id: "C123", name: "John Doe", email: "john@example.com", city: "NYC"}
Result: Base object + all join fields (name, email, city)

---

SOME_FIELDS_JOIN

Copies only specified fields from joined object.

{
	"join_type": "some_fields_join",
	"union_field_in_base_obj": "customer_id",
	"union_field_in_join": "id",
	"join_fields_if_part_join": "name,email"
}

Result: Base object + only name and email (not city)

---

BLIND_ONE_OBJ_JOIN

Joins first object from join source to all base objects, ignoring union field matching.

Useful for adding configuration/global settings to all objects.

{
	"join_type": "blind_one_obj_join",
	"join_source": "rule",
	"join_source_ref": "globalConfig"
}

Base: [{order_id: "A1", total: 100}, {order_id: "A2", total: 200}]
Join: {tax_rate: 0.08, currency: "USD"}
Result: Both orders get tax_rate and currency fields

---

Field Renaming

Use colon syntax to rename fields during join: source_field:target_field

In the schema navigator, click the pencil on a field chip and enter its output name inline. Press Enter or click away to apply it; press Escape to cancel. Clear the output name to restore the original field name.

{
	"join_type": "some_fields_join",
	"join_fields_if_part_join": "name:customer_name,email:customer_email"
}

Join: {id: "C123", name: "John Doe", email: "john@example.com"}
Result: Fields added as customer_name and customer_email (not name/email)

---

Rule Properties

PROPERTY	DESCRIPTION
reference	Required. Example: “enrichWithCustomers”
join_type	Required projection behavior: full_join, some_fields_join, or blind_one_obj_join
join_multiplicity	Optional. one_to_many duplicates each base object once per matching source row. Omit for first-match behavior.
keep_only_matched_base_objects	One-to-many only. When true, unmatched base objects are omitted from the JOIN result. Omit/false preserves them.
join_source	Required. “side_obj”, “rule”, or “local”
join_source_ref	Required. Reference name of join source
union_field_in_base_obj	Required (except BLIND). Ordered, comma-separated base match fields
union_field_in_join	Required (except BLIND). Ordered source match fields; count must equal the base list
join_fields_if_part_join	Required for SOME_FIELDS_JOIN. Comma-separated fields. Supports “field:newName”
base_fields_to_inherit	One-to-many only. Base fields copied to each expanded result; supports field:newName. Blank copies all.
new_target_to_persist	Optional side-object destination. When set, local objects remain unchanged.
break_on_false_join	brake, inform_error, or ignore
false_join_op_message	Custom error message on failed join

---

Common Use Cases

Enrich Orders with Customer Data

{
	"join_type": "some_fields_join",
	"join_source": "side_obj",
	"join_source_ref": "loadCustomers",
	"union_field_in_base_obj": "customer_id",
	"union_field_in_join": "id",
	"join_fields_if_part_join": "name:customer_name,email:customer_email"
}

Base: [{order_id: "A1", customer_id: "C123", total: 100}]
Join: [{id: "C123", name: "John Doe", email: "john@example.com"}]
Result: [{order_id: "A1", customer_id: "C123", total: 100, customer_name: "John Doe", customer_email: "john@example.com"}]

---

Add Pricing from Database Rule

{
	"join_type": "some_fields_join",
	"join_source": "rule",
	"join_source_ref": "productPricing",
	"union_field_in_base_obj": "product_id",
	"union_field_in_join": "sku",
	"join_fields_if_part_join": "price,discount"
}

Merges pricing data from database rule into product list.

---

Mandatory Join with Error Handling

{
	"join_type": "full_join",
	"union_field_in_base_obj": "customer_id",
	"union_field_in_join": "id",
	"break_on_false_join": "brake",
	"false_join_op_message": "Customer data missing for order"
}

If any customer_id not found in join source, SchemaContext.mainResult = false and execution stops.

Summary

JOIN enriches objects by merging data from multiple sources:

• Projection, cardinality, and unmatched-row retention are independent
• Union field matching like SQL ON clause
• Field renaming with colon syntax
• Optional error handling