Skip to content

Join

Join

Overview

The Join Block merges two lists of dictionaries based on a specified key, allowing SQL-like join operations (e.g., INNER, LEFT, RIGHT, OUTER). This Block is ideal for combining data from multiple sources by matching records on a common key, with flexible options for different types of joins.

Description

The Join block performs advanced join operations between two lists of dictionaries based on a specified key. It merges the data according to the selected join type, similar to SQL join operations, allowing for flexible data integration and transformation.

Key Features:

  • Flexible Join Types: Supports multiple join types, including INNER, LEFT_INNER, LEFT_OUTER, RIGHT_INNER, RIGHT_OUTER, and OUTER.
  • Customizable Key: Allows specification of the join key.
  • Data Merging: Combines fields from both left and right records where applicable.
  • Error Handling: Ensures all records contain the specified key.

Supported Join Types:

  • INNER: Records where the key exists in both left and right lists.
  • LEFT_INNER: Left records with matching keys in the right list.
  • LEFT_OUTER: All left records, merging with right records where keys match.
  • RIGHT_INNER: Right records with matching keys in the left list.
  • RIGHT_OUTER: All right records, merging with left records where keys match.
  • OUTER: All records from both lists, merging where keys match.

Use Cases:

  • Merging datasets from different sources.
  • Performing SQL-like join operations in Python.

Metadata

  • Category: Function

Configuration Options

Name Data Type Description Default Value
key str
joinType JoinType JoinType.INNER

Inputs

Name Data Type Description
left list[dict[str, Any]]
right list[dict[str, Any]]

Outputs

Name Data Type Description
result list[dict[str, Any]]

State Variables

No state variables available.

Example(s)

Example 1: Perform an INNER join

  • Create a Join Block.
  • Set key to "id" and joinType to INNER.
  • Provide left and right lists of dictionaries where each dictionary contains an "id" key.
  • The Block will output records with matching "id" values from both lists.

Example 2: Perform a LEFT_OUTER join

  • Set up a Join Block with key as "user_id" and joinType as LEFT_OUTER.
  • Provide the left list as user data and the right list as order data, each containing "user_id".
  • The Block will output all user records, merging order data for users with matching "user_id".

Example 3: Perform an OUTER join

  • Configure a Join Block with key set to "product_id" and joinType as OUTER.
  • Supply left and right lists of product data with "product_id" as a shared key.
  • The Block will output all records from both lists, merging where "product_id" matches.

Error Handling

  • If a record in either list lacks the specified key, the Block will raise a KeyError.
  • If the joinType is invalid, a ValueError will be raised.

FAQ

What happens if a record doesn’t contain the specified key?

If a record in either the left or right list does not contain the specified join key, the Block will raise a KeyError, ensuring only records with the required key are processed.

Can I specify custom join types?

Yes, the Block supports various join types, similar to SQL joins. Select the appropriate join type based on your requirements.

How are fields merged from both lists?

Fields from both lists are merged into a single dictionary for each record in the output, based on the specified join type. If the same field exists in both records, the field from the right list will overwrite the one from the left list.

Does this Block support joins on nested fields?

Currently, this Block only supports joins on top-level keys. For joins on nested fields, you would need to extract those fields into top-level keys before using this Block.