Perform a full outer join

Use join.full() to perform an full outer join of two streams of data. Full outer joins output a row for all rows in both the left and right input streams and join rows that match according to the on predicate.

Use join.full 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.full() 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.

     Account for missing, non-group-key values

     In a full outer join, it’s possible for either the left (l) or right (r) to contain null values for the columns used in the join operation and default to a default record (group key columns are populated and other columns are null). l and r will never both use default records at the same time.

     To ensure non-null values are included in the output for non-group-key columns, check for the existence of a value in the l or r record, and return the value that exists:

     (l, r) => {
         id = if exists l.id then l.id else r.id
     
         return {_time: l.time, location: r.location, id: id}
     }
     

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.

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")
        |> limit(n: 5)

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},
            {station: "g4", opType: "auto", last_maintained: 2021-08-04T00:00:00Z},
        ],
    )

join.full(
    left: left |> group(),
    right: right,
    on: (l, r) => l.stationID == r.station,
    as: (l, r) => {
        stationID = if exists l.stationID then l.stationID else r.station

        return {
            stationID: stationID,
            _time: l._time,
            _field: l._field,
            _value: l._value,
            opType: r.opType,
            maintained: r.last_maintained,
        }
    },
)
    |> group(columns: ["stationID"])
    |> sort(columns: ["_time"])