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"}