Join data

Use the Flux join package to join two data sets based on common values. Learn how join two data sets using the following join methods:

• How join functions work
  • Input streams
  • Join predicate function (on)
  • Join output function (as)
• Perform join operations
  • Perform an inner join
  • Perform a left outer join
  • Perform a right outer join
  • Perform a full outer join
  • Join on time
• Troubleshoot join operations

How join functions work

join functions join two streams of tables together based on common values in each input stream.

• Input streams
• Join predicate function (on)
• Join output function (as)

Input streams

Each input stream is assigned to the left or right parameter. Input streams can be defined from any valid data source. For more information, see:

• Query data sources
• Define ad hoc tables with array.from()

Data requirements

To join data, each input stream must have the following:

• One or more columns with common values to join on.
  Columns do not need identical labels, but they do need to have comparable values.

• Identical group keys.
  Functions in the join package use group keys to quickly determine what tables from each input stream should be paired and evaluated for the join operation.
  Both input streams should have the same group key. If they don’t, your join operation may not find any matching tables and will return unexpected output. If the group keys of your input streams are not identical, use group() to regroup each input stream before joining them together.

  Only tables with the same group key instance are joined.

Join predicate function (on)

join package functions require the on parameter to compare values from each input stream (represented by l (left) and r (right)) and returns true or false. Rows that return true are joined. This parameter is a predicate function.

(l, r) => l.column == r.column

Join output function (as)

join package functions (except join.time()) require the as parameter to define the output schema of the join. The as parameter returns a new record using values from joined rows–left (l) and right (r).

(l, r) => ({l with name: r.name, location: r.location})

Perform join operations

The join package supports the following join types and special use cases:

• Perform an inner join
• Perform a left outer join
• Perform a right outer join
• Perform a full outer join
• Join on time

import "join"

left = from(bucket: "example-bucket-1") |> //...
right = from(bucket: "example-bucket-2") |> //...

join.inner(
    left: left,
    right: right,
    on: (l, r) => l.column == r.column,
    as: (l, r) => ({l with name: r.name, location: r.location}),
)

import "join"

left = from(bucket: "example-bucket-1") |> //...
right = from(bucket: "example-bucket-2") |> //...

join.left(
    left: left,
    right: right,
    on: (l, r) => l.column == r.column,
    as: (l, r) => ({l with name: r.name, location: r.location}),
)

import "join"

left = from(bucket: "example-bucket-1") |> //...
right = from(bucket: "example-bucket-2") |> //...

join.right(
    left: left,
    right: right,
    on: (l, r) => l.column == r.column,
    as: (l, r) => ({r with name: l.name, location: l.location}),
)

import "join"

left = from(bucket: "example-bucket-1") |> //...
right = from(bucket: "example-bucket-2") |> //...

join.full(
    left: left,
    right: right,
    on: (l, r) => l.id== r.id,
    as: (l, r) => {
        id = if exists l.id then l.id else r.id
        
        return {name: l.name, location: r.location, id: id}
    },
)

import "join"

left = from(bucket: "example-bucket-1") |> //...
right = from(bucket: "example-bucket-2") |> //...

join.time(
    left: left,
    right: right,
    as: (l, r) => ({l with field1: l._value, field2: r._value_}),
)

Troubleshoot join operations

For information about unexpected behaviors and errors when using the join package, see Troubleshoot join operations.