JOIN

This operator joins two streams together in a single one.

This page discusses:

Configuration
About Join Types

Configuration

If the left input stream is ordered, the order is kept on the output. If the right stream is ordered, this order is kept within a same left input document.


Parameter	Comment
`expression`	Specifies the join criteria.
`type`	Specifies the join strategy: `LEFT`, `INNER`, `FULL_OUTER`, `LEFT_EXCLUDING`. Default: `LEFT`.
`aggregate`	If `true`, the right elements are grouped inside a list for one matching left element. Default: `false`.
`outputExpression`	Operates as a `MAP` operator placed at the end of the `BINARY JOIN`.

About Join Types

When having more than 2 data sources, only Inner join is available.

The following subsections use the following dataset as example. It lists the employees belonging to each company office.

The left input is a stream of company offices:

{id: "MyCompanyUK"}, {id: "MyCompanyFR"}, {id: "MyCompanyGE"}

The right input is a stream of employees:

{name: "John", company: "MyCompanyUK"}
{name: "Lucy", company: "MyCompanyUK"}
{name: "Michel", company: "MyCompanyFR"}
{name: "Mary", company: "MyCompanyUS"}

The join criteria expression is: "left.id == right.company"

Left Join

The left join iterates on left elements and associates them with right elements for which the criteria is true.

In our example, if the company office is not associated to an employee, these entries will nevertheless appear in the left join output.

The left join with aggregate: true provides the following result:

{left: {id: "MyCompanyUK"}, right: [{name: "John", company: "MyCompanyUK"}, {name: "Lucy", company: "MyCompanyUK"}]}
{left: {id: "MyCompanyFR"}, right: [name: "Michel", company: "MyCompanyFR"]}
{left: {id: "MyCompanyGE"}, right: []}

The left join with aggregate: false provides the following result:

{left: {id: "MyCompanyUK"}, right: {name: "John", company: "MyCompanyUK"}}
{left: {id: "MyCompanyUK"}, right: {name: "Lucy", company: "MyCompanyUK"}}
{left: {id: "MyCompanyFR"}, right: {name: "Michel", company: "MyCompanyFR"}}
{left: {id: "MyCompanyGE"}, right: NULL}

Inner Join

The inner join takes the intersection of the left and right input.

In our example, if the employee is not associated to a company office, or if a company office is not associated to an employee, these entries will not appear in the inner join output.

The inner join provides the following result:

{left: {id: "MyCompanyUK"}, right: {name: "John", company: "MyCompanyUK"}}
{left: {id: "MyCompanyUK"}, right: {name: "Lucy", company: "MyCompanyUK"}}
{left: {id: "MyCompanyFR"}, right: {name: "Michel", company: "MyCompanyFR"}}

Full Outer Join

The full outer join outputs all elements.

In our example, if the employee is not associated to a company office, or if a company office is not associated to an employee, these entries will nevertheless appear in the full outer join output.

The full outer join provides the following result:

{left: {id: "MyCompanyUK"}, right: {name: "John", company: "MyCompanyUK"}}
{left: {id: "MyCompanyUK"}, right: {name: "Lucy", company: "MyCompanyUK"}}
{left: {id: "MyCompanyFR"}, right: {name: "Michel", company: "MyCompanyFR"}}
{left: {id: "MyCompanyGE"}, right: NULL}
{left: NULL, right: {name: "Mary", company: "MyCompanyUS"}}

Left Excluding Join

The left excluding join retrieves the left elements that do not match any right elements.

In our example, the left excluding join will display only the company offices that do not have any employees.

The left excluding join provides the following result:

{left: {id: "MyCompanyGE"}, right: NULL}