Documentation by ClonePartner

Start typing to search.

Task Reference

lookup

View Markdown

Resolve foreign keys and enrich records with a fallback chain of single-row lookups across SQL and MongoDB Connectors.

lookup

Enriches each input item by trying an ordered chain of single-row lookups against SQL or MongoDB Connectors and merging the first match into the record.

When to use it

Use lookup for cross-store joins and foreign-key resolution, such as resolving an email to a staged account row before writing a related record. For destination-ID dedup lookups against the mapping store, prefer sync_record (or get_destination_id in existing tasks).

Configuration

Key Type Required Default Description
keys array Yes — Ordered lookup attempts. The step stops at the first match. An empty list fails with lookup requires at least one keys entry.
keys[].connector string Yes — SQL (sqlite, mssql, mysql, postgres) or MongoDB Connector slot. Other types fail with lookup requires a SQL or MongoDB connector.
keys[].table string For SQL — Table to query. One of table or collection is required per key.
keys[].collection string For MongoDB — Collection to query. One of table or collection is required per key.
keys[].match object Yes — Filter passed to find_one. A string value starting with $record. is replaced by that field's value from the input item.
keys[].select string[] No — Columns to return. SQL: passed as select; MongoDB: converted to a projection.
as string No — Nest the matched row under this key on the output. Omitted, the row's fields are merged into the record.
required boolean No — When true and no key matches, the item is dropped (nothing emitted).
emit string No — matched_only or all. matched_only drops unmatched items, same as required: true.

Example

name: resolve_ticket_accounts
connectors:
  source:
    type: mongodb
  staging:
    type: sqlite
steps:
  - name: read_tickets
    type: call_method
    config:
      connector: source
      method: find
      args:
        collection: tickets
  - name: resolve_account
    type: lookup
    config: |
      {
        "keys": [
          {
            "connector": "staging",
            "table": "accounts",
            "match": { "email": "$record.requester_email" },
            "select": ["destination_id", "name"]
          },
          {
            "connector": "staging",
            "table": "accounts",
            "match": { "domain": "$record.requester_domain" }
          }
        ],
        "as": "account",
        "required": true
      }

Behavior notes

  • Fallback chain. Keys are tried in order with the Connector's find_one method; the first row found wins and later keys are not queried. Use the chain to express "match by email, else by domain" style resolution.
  • $record. references. Only string values in match that start with $record. are resolved against the input item; everything else is passed as a literal. The config as a whole can still be a JSONata string when values need expressions.
  • Merge semantics. With as, the match is attached under one key and the rest of the record is untouched. Without as, the row's fields are merged into the record and overwrite same-named input fields.
  • No match. With required: true or emit: matched_only the item is dropped from the stream. Otherwise the item passes through without enrichment.
  • Non-object input (a bare string or number) is wrapped as { value: <input> } before matching and merging.
  • Lookups are issued per item on every pass; the step does not cache results.