API Lookup

Look up each row against an API and add the answer as new columns.

Category

Lookup

Connection

REST API

Also known as

lookup, enrich, enrichment, http, api, geocode, per row, web service, join, soap, xml, webhook, call

What it does

This is a JOIN against an API. Your rows come from somewhere else (a database, a file, anywhere) and for EACH one it calls an endpoint built from that row's own values (the path, query, headers and body can all read the row with ${column}), then appends the answer as new columns on that row. Same rows out, wider. Say you have 40,000 customers in Postgres and need each one's credit score, and the vendor's API is GET /score/{customer_id}: one customer per request, because there is no “give me everything” endpoint. That is this node: one node, 40,000 calls, the score landing on each row. The Read node cannot do it (its URL is typed once, before any rows exist) and the Write node cannot do it (it has no output port, so the response is discarded and nothing lines it up with the row it belongs to). Which of the three REST nodes do you want? • REST API (Read): rows come FROM the API; one fixed request, then pagination. • API Lookup: rows come from somewhere else and the API ADDS A COLUMN to each one. • REST API (Write): rows go TO the API and nothing comes back.

Inputs and outputs

When to use it

• Enrich rows against a lookup, scoring or geocoding service
• Fetch each record's related detail from an API
• Post each row somewhere and keep the id that comes back
• Pull an XML or SOAP payload per row, then shred it with the XML node
• Call a SOAP service, or an API that takes form fields, once per row

Examples

path: customers/${id}

Method: POST · body: { "address": "${street}, ${city}" }

Send the row as the body: on · records path: (blank)

records path: results · Expand: on

Response format: Text → http_body → XML (Parse tab)

Method: POST · Request body: XML · header SOAPAction · Response format: Text

Method: POST · Request body: Form fields · email = ${email}

Tips

• A GET is retried like any read. Any other method may change something, so a request that may already have reached the API (a timeout after sending, a dropped connection, a 5xx other than 503) is never sent again: that row fails the run (or, with 'If a call fails' set to continue, keeps empty response columns), and the message says the API may have processed it.
• If the URL is the same on every row, you want REST API (Read) in the Input tab instead. That one also walks pagination, which this node does not.
• If you are not using the response at all, REST API (Write) in the Output tab is the simpler node, and it can batch many rows into one request.
• Calling your own code or a hosted model on many rows? API Batch Lookup sends them a hundred or more to a request instead of one, which turns a million calls into ten thousand.
• The path may take a row's value (customers/${id}), but every request stays on the connection's own host: a row whose value would send the request to another host is refused by name, and follows the node's error setting like any failed request, rather than carrying the connection's credentials there.
• Set a column prefix (geo_) so the response can't collide with your own column names.
• Add the status column and follow it with a Filter to route failures without stopping the run.
• More requests in flight is faster until the API starts rate-limiting: 4 is a safe default, 16 the ceiling.
• A Test run skips POST/PUT/PATCH/DELETE calls so a preview never writes to a real system; GETs still run, so the preview shows genuine response columns.
• In the body, put a text column in quotes ("${note}") and it is sent as text, quotes and line breaks included. Without quotes (${id}) the value goes in as JSON: right for numbers, true/false, or a column that already holds JSON.
• The request body can be JSON (the default), XML or form fields. In XML each ${column} is escaped for where it sits, so a row's own text can never break the document, and the template is checked for a well-formed document before anything is sent. Form fields are encoded for you; an empty column is left out of them.
• An XML body is sent as text/xml. For SOAP 1.2 add a Content-Type header of application/soap+xml, and a SOAPAction header if the service asks for one. A Content-Type header you set always wins.
• Declare the response columns (Fetch sample) and they are known before any run, even on a day no row comes in. Learned from the answers instead, they come from the first answer that has records: a row answered with nothing before it keeps its place, with empty response columns. When no answer has any records (a Test run sends no POST, for example), every row goes on with only its input columns and a warning says so.

Related nodes