BroadSQL BroadSQL Downloads
Documentation / API Client: Working with API results

Working with API results

An API response is shown in full, as a structure preserving list by default, and kept in memory as the last result until the next call. From there you can show it as a table or as the raw body, copy it to a spreadsheet, or export it to a file or a local table with DUMP API RESULT, described with the other export sources in Script and API results.

Result display: LIST and TABLE

An API response often has many fields, or fields much wider than a SQL row typically has. Unlike SQL, you cannot usually write SELECT id, name, status against an HTTP endpoint to narrow what comes back. **BroadSQL's default display is therefore a complete, structure-preserving list, never a guessed-at subset of columns:**

CONNECT API MYAPI:Development;
RUN /users;
API: My Service
Environment: Development
Endpoint: GET List Users

GET https://dev.example.com/users

HTTP 200
Duration: 98 ms
Content-Type: application/json

[1]
  id   : 1
  name : Alice

[2]
  id   : 2
  name : Bob

Every field returned by the server appears: **no field is ever hidden, selected, or inferred as "important"** by BroadSQL, regardless of how many fields there are or how wide the response is. A nested object becomes its own indented block under its field name; a nested array becomes indented [1]/[2]/... entries, each in turn showing an object's full fields or a scalar value inline. A long value is shown in full, never truncated or ellipsized, unlike the catalog tables SHOW ENDPOINTS/ SHOW ALL APIS/SHOW API ENVIRONMENTS use, which may ellipsize a long cell for a purely navigational display (see Browsing endpoints); an actual API result is never treated that way.

TABLE remains fully available, as an explicit choice, when a response is naturally table-shaped and you want it displayed that way:

RUN /users TABLE;
API: My Service
Environment: Development
Endpoint: GET List Users

GET https://dev.example.com/users

HTTP 200
Duration: 98 ms
Content-Type: application/json

|--|-----|
|id|name |
|--|-----|
|1 |Alice|
|2 |Bob  |
|--|-----|

2 rows.

This applies to four shapes: an array of objects (one column per key, in first-seen order across every element; a row missing a key is shown with a blank cell rather than shifting the other columns), a single object (a one-row table), an empty array (a table with zero rows and zero columns), and an array of plain values such as numbers or strings (a single column named VALUE, one row per element). A nested object or array inside a cell is shown as compact JSON text, never split into extra columns or rows. Any other JSON shape, such as an array mixing objects with plain values, or a response that is not JSON at all, falls back to pretty-printed JSON (or raw text) even under TABLE. TABLE never truncates or omits a field either: a wide table from an explicit TABLE request is expected and shown in full.

To always see the pretty-printed JSON or raw text, regardless of shape, append RAW instead:

RUN /users RAW;
{
  "id": 1,
  "name": "Alice"
}

LIST, TABLE, and RAW never discard the original response body; the underlying data is exactly what the server returned, whichever mode displays it. There is no way today to select a subset of returned fields (a future, explicit SELECT-style projection is a possible direction, not implemented in this release); every mode described above shows the complete response.

Exporting API results

The last RUN result held in memory can be exported through BroadSQL's normal export/local snapshot machinery with DUMP API RESULT TO ..., the sibling of DUMP / TO ..., which does the same for the last SQL result displayed (see export.md, "Exporting an API execution result", for the full grammar and every destination kind). This capture is always the complete, tabular-shaped snapshot of the result (via the same shape rules TABLE mode uses; see Result display: LIST and TABLE), independent of which display mode was actually shown on screen for that call:

RUN /users;
DUMP API RESULT TO WORKCOPY.USERS AS H2;
2 row(s) exported to WORKCOPY.USERS (table dropped and recreated).

The same result can also be exported to a spreadsheet tab or a flat file:

DUMP API RESULT TO REPORT.USERS AS XLSX;
DUMP API RESULT TO USERS AS CSV;

This works for any tabular-shaped result, not only an array of objects like the example above: a single object becomes a one-row table, an array of plain values becomes a single VALUE column, and so on (see Result display: LIST and TABLE above for the exact shape rules). Every exported column is text (VARCHAR); there is no numeric/date typing for an API result this release, since the API result was never typed to begin with. MODE APPEND is not supported for this source, only a full MODE OVERWRITE (the default). AS JSON exports the flattened tabular shape (its columns), not the original raw API response body; the raw body is unaffected either way.

A result held in memory is always the last successful execution only: any later execution attempt that does not itself complete successfully (a write-method refusal, an unresolved variable, unsupported authentication, an inactive entity, or a network error) clears it immediately, so DUMP API RESULT correctly reports nothing to export rather than silently re-exporting an earlier, unrelated result. This applies identically to every form of RUN, including one that fails only because no API session context is active (for example, right after DISCONNECT API):

DUMP API RESULT TO WORKCOPY.USERS AS H2;
DUMP API RESULT: no API execution result held in memory. Run RUN first.

COPY RESULT also consumes this same result, copying it to the system clipboard as tab-separated text ready to paste into Excel/Calc, exactly as it already does for the last SQL query. It takes no arguments, and always uses whichever of a SQL query and an API result actually ran most recently in the session, regardless of which type it is:

RUN /users;
COPY RESULT;

The row count of the tabular result is confirmed, and the data is ready to paste directly into Excel or Calc.