Perform a right outer join

Use join.right() to perform an right outer join of two streams of data. Right joins output a row for each row in the right data stream with data matching from the left data stream. If there is no matching data in the left data stream, non-group-key columns with values from the left data stream are null.

{{< svg svg="static/svgs/join-diagram.svg" class="right" >}}

{{< expand-wrapper >}} {{% expand "View table illustration of a right outer join" %}} {{< flex >}} {{% flex-content "third" %}}

left

right

Right outer join result

{{% /flex-content %}} {{< /flex >}} {{% /expand %}} {{< /expand-wrapper >}}

Use join.right to join your data

1. Import the join package.

2. Define the left and right data streams to join:

   • Each stream must have one or more columns with common values. Column labels do not need to match, but column values do.
   • Each stream should have identical group keys.

   For more information, see join data requirements.

3. Use join.right() to join the two streams together. Provide the following required parameters:

   • left: Stream of data representing the left side of the join.
   • right: Stream of data representing the right side of the join.
   • on: Join predicate. For example: (l, r) => l.column == r.column.
   • as: Join output function that returns a record with values from each input stream. For example: (l, r) => ({r with column1: l.column1, column2: l.column2}).

The following example uses a filtered selection from the machineProduction sample data set as the left data stream and an ad-hoc table created with array.from() as the right data stream.

{{% note %}}

Example data grouping

The example below ungroups the left stream to match the grouping of the right stream. {{% /note %}}

import "array"
import "influxdata/influxdb/sample"
import "join"

left =
    sample.data(set: "machineProduction")
        |> filter(fn: (r) => r.stationID == "g1" or r.stationID == "g2" or r.stationID == "g3")
        |> filter(fn: (r) => r._field == "oil_temp")
        |> last()

right =
    array.from(
        rows: [
            {station: "g1", opType: "auto", last_maintained: 2021-07-15T00:00:00Z},
            {station: "g2", opType: "manned", last_maintained: 2021-07-02T00:00:00Z},
        ],
    )

join.right(
    left: left |> group(),
    right: right,
    on: (l, r) => l.stationID == r.station,
    as: (l, r) => ({r with last_reported_val: l._value, last_reported_time: l._time}),
)

{{< expand-wrapper >}} {{% expand "View example input and output data" %}}

Input

left {#left-input}

{{% note %}} _start and _stop columns have been omitted. {{% /note %}}

right {#right-input}

Output {#example-output}

{{% /expand %}} {{< /expand-wrapper >}}