Skip to main content

Join

A Join node matches the items from two paths by a key and passes on data items that pair each Left item with the Right items whose key matches it. Use it to combine the rows of an orders file with the rows of a shipments file that share an order number, to add a customer's details from a lookup file to each transaction, or to reconcile a partner's remittance against your invoices.

Join accepts file items and data items on both inputs and produces data items, because the pair it builds is a new object rather than either file.

Settings

The node has two inputs, Left and Right, and each has exactly one incoming connection, because the join matches one collection against one other collection. Connecting two nodes to the same input, or leaving an input unconnected, fails at save.

Left Key and Right Key are TransformScript expressions without braces, evaluated against each item of the corresponding input to produce the value to match on. A file item is one item whose payload is the whole parsed file, so to match the rows of an orders file with the rows of a shipments file, place before each input a Transform node whose script returns the file's parsed rows, which makes each row one data item, then set payload.order_id as both keys. Two items match when their key values are equal.

The join type is inner, left, or outer, and the default is left. An inner join passes on only the pairs in which a Left item matched a Right item. A left join also passes on every Left item that matched nothing on the Right. An outer join passes on the matched pairs and the unmatched items from both inputs.

What the Next Node Receives

The next node receives one data item for each pair, with the Left item as l and the Right item as r, so a Transform node after the join reads the order as payload.l and the shipment as payload.r. A Left item that matches several Right items produces one item per match. The original items are not passed on, so to use a Left or Right item after the join, connect the node that produces it to the next node as well.

Errors

When a key cannot be evaluated for an item, that item fails. Error Handling rules on the node determine what happens to a failed item. A key that is not a valid expression, or an input with no connection or more than one, fails at save, and the message gives the position of the failing field in the JSON definition.

Aggregate combines many items into one, and a node with two incoming connections receives items from two paths.

Join vs. Aggregate

Aggregate combines every data item on one path into one data item, without matching anything. Join matches the items of two paths by key and produces one item per matched pair. Use Aggregate to combine many items into one, for example to write one report. Use Join when an item on one path and an item on the other describe the same order, shipment, or customer.

Join vs. Two Connections Into One Node

Any node can receive connections from two nodes, and it processes the items from both together, without pairing them. Use two connections when both paths produce items that need the same processing, for example files from two folders that are all encrypted the same way. Use Join when an item on one path belongs with an item on the other.