Lookup Transform

The lookup transform extends a primary data stream by looking up values on a secondary data stream. Lookup accepts one or more key fields from the primary data stream, each of which are then searched for in a single key field of the secondary data stream.

If a match is found, by default the full data object in the secondary stream is added as a property of the data object in the primary stream. However, if the values parameter is supplied, the provided field names will instead be copied from the matched object to the primary object, maintaining a “flat” record structure.

Transform Parameters

Property	Type	Description
from	Data	Required. The name of the secondary data stream upon which to perform the lookup.
key	Field	Required. The key field on the secondary stream.
values	Field[ ]	The data fields to copy from the secondary stream to the primary stream. If not specified, a reference to the full data record is copied.
fields	Field[ ]	Required. The data fields in the primary stream to lookup.
as	String[ ]	The output fields at which to write data found in the secondary stream. If not specified and a values parameter is supplied, the names of the fields in the values array will be used. This parameter is required if multiple fields are provided or values is unspecified.
default	Any	The default value to assign if lookup fails (default `null`).

Usage

For each data object in the input data stream values, this example lookups records where the field foo matches the field id field of the data stream names. Matching records are added to the input stream values as a field named obj.

"data": [
  {
    "name": "names",
    "values": [
      {"id": "A", "name": "label A"},
      {"id": "B", "name": "label B"},
      {"id": "C", "name": "label C"}
    ]
  },
  {
    "name": "values",
    "values": [
      {"foo": "A", "bar": 28},
      {"foo": "B", "bar": 55},
      {"foo": "C", "bar": 43},
      {"foo": "C", "bar": 91},
      {"foo": "D", "bar": 81}
    ],
    "transform": [
      {
        "type": "lookup",
        "from": "names",
        "key": "id",
        "fields": ["foo"],
        "as": ["obj"]
      }
    ]
  }
]

After transformation, the values stream will be:

{"foo": "A", "bar": 28, "obj": {"id": "A", "name": "label A"}},
{"foo": "B", "bar": 55, "obj": {"id": "B", "name": "label B"}},
{"foo": "C", "bar": 43, "obj": {"id": "C", "name": "label C"}},
{"foo": "C", "bar": 91, "obj": {"id": "C", "name": "label C"}},
{"foo": "D", "bar": 81, "obj": null}

This example is similar to the previous example, except that instead of copying a reference to the full data record found in the lookup, the "name" data field value is copied directly to objects in the primary data stream, and a default is provided:

{
  "type": "lookup",
  "from": "names",
  "key": "id",
  "fields": ["foo"],
  "values": ["name"],
  "as": ["obj"],
  "default": "some label"
}

result:

{"foo": "A", "bar": 28, "obj": "label A"},
{"foo": "B", "bar": 55, "obj": "label B"},
{"foo": "C", "bar": 43, "obj": "label C"},
{"foo": "C", "bar": 91, "obj": "label C"},
{"foo": "D", "bar": 81, "obj": "some label"}