From any SQL or JSON to dropdown options: building a strict mapping pipeline

The problem

The data source service feeds dropdowns, option lists and lookup tables in other applications. Those applications want one simple, predictable shape:

[
  { "label": "Natural oak", "value": "OAK-NAT" },
  { "label": "Smoked oak",  "value": "OAK-SMK" }
]

But sources are defined by users, and they return anything:

A SQL query returns flat rows with whatever column names the database uses:

[{ "finish_name": "Natural oak", "finish_code": "OAK-NAT", "active": true }]

A JSON API returns nested structures, often with the useful list buried inside:

{
  "status": "ok",
  "result": {
    "items": [
      { "name": { "en": "Natural oak" }, "sku": "OAK-NAT", "stock": { "qty": 12 } }
    ]
  }
}

So the service needs a mapping: a small configuration that says "label comes from here, value comes from there", plus optional extra fields.

The first version of mapping had a quiet flaw. When a path didn't exist (a typo, or an API that renamed a field), it returned null. The dropdown showed empty labels, nobody got an error, and the broken mapping could live for months. After chasing a "random nulls" bug elsewhere in this service, silent nulls were the last thing I wanted to keep.

The details

A mapping is a few paths

{
  "label": "result.items.name.en",
  "value": "result.items.sku",
  "data": "extra",
  "fields": [
    { "key": "stock", "mappedTo": "result.items.stock.qty" }
  ]
}

• label and value are required and become the output's label and value.
• fields adds extra properties to each option (here, the stock quantity).
• data names a property that groups those extra fields in the output. It can't be label or value, because that would overwrite the required keys.

The tricky part: arrays in the middle of paths

Look at result.items.name.en. result is an object, items is an array, and name.en lives inside each element. The path must "walk through" the array and produce one value per element. No indexes like items[0], because the mapping describes every row.

Users need to see the shape to map it

You can't ask someone to type result.items.name.en from memory. They need to see what the source actually returns, ideally without scrolling through 2,000 rows of real data.

The challenges

• Show the shape, not the data. Large results are useless for building a mapping; the user needs the structure.
• Validate paths against reality. A mapping must be checked against what the source really returns, at save time.
• Find the list automatically. For nested responses, the service must figure out which array holds the rows.
• Distinguish "missing" from "empty". A field that exists with value null is legitimate data. A field that doesn't exist is a broken mapping.
• Different strictness for different endpoints. Errors should be precise in the editor's "test" mode, but must never leak details on the public execute endpoint.

The solution

1. The skeleton: a result's shape in a few lines

Before any mapping, the service runs the source once and replaces every value with its type name:

export function buildSkeleton(data: unknown): unknown {
  if (data === null || data === undefined) return 'null';
  if (Array.isArray(data)) return data.length ? [buildSkeleton(data[0])] : [];
  if (typeof data === 'object') {
    const out: Record<string, unknown> = {};
    for (const [key, value] of Object.entries(data)) out[key] = buildSkeleton(value);
    return out;
  }
  return typeof data; // "string", "number", "boolean"
}

The nested API response above becomes:

{
  "status": "string",
  "result": {
    "items": [
      { "name": { "en": "string" }, "sku": "string", "stock": { "qty": "number" } }
    ]
  }
}

That's the shape, compact and readable, whatever the size of the real result. Arrays are represented by their first element's shape.

Two special cases:

• SQL with zero rows: there's no first row to describe, so the service falls back to the column names returned by the query, and users can still map them.
• Endpoints returning an empty array or a non-object: rejected with a clear message, because there's nothing to map.

The editor also offers a skeleton=true option on test and execute, so users can inspect any source's shape without downloading its data.

2. Dotted paths: every reachable field

From the skeleton, the service collects every valid path, joining keys with . and stepping transparently through arrays (no index in the path):

status
result
result.items
result.items.name
result.items.name.en
result.items.sku
result.items.stock
result.items.stock.qty

That list powers two things: autocomplete in the editor, and validation on the server.

3. Validate at save time, against real data

When a mapping is saved, every path in it must exist in that set:

• label and value are required and must be valid paths,
• every field must have a non-empty key and a valid mappedTo path,
• fields can be sent as an array [{ key, mappedTo }] or as an object { key: path }; both are normalized to one form,
• data must not be label or value (reserved output keys),
• extra fields are only allowed when data is set, so they always have a place in the output.

The first violation returns a 400 naming the exact problem, for example mapping.fields[1]: must include non-empty string values for 'key' and 'mappedTo'. Users fix mistakes when they make them, not when a dropdown breaks.

4. Find the list automatically

When the source returns an object rather than an array, the service needs to know which array contains the rows. It looks at all mapping paths and finds their longest common prefix:

result.items.name.en
result.items.sku
result.items.stock.qty
        ↓
common prefix: result.items

If result.items is an array, that's the list. The prefix is stripped from each path (name.en, sku, stock.qty), and every element is mapped as one row. If there's no common array, the whole object is treated as a single row. Users never have to configure "where the list is"; it's inferred from the paths they already chose.

5. Strict resolution: missing is an error, null is data

The resolver walks each path segment by segment:

function walk(current: unknown, segments: string[], i: number, fullPath: string): unknown {
  if (i === segments.length) return current;

  // Arrays mid-path: apply the rest of the path to every element
  if (Array.isArray(current)) return current.map((el) => walk(el, segments, i, fullPath));

  const key = segments[i];
  if (current === null || typeof current !== 'object' || !Object.hasOwn(current, key)) {
    throw new BadRequestException(`Path "${fullPath}" not found in the source result.`);
  }
  return walk((current as Record<string, unknown>)[key], segments, i + 1, fullPath);
}

The rules are precise:

• A key that exists with value null is found. It returns null, because that's real data.
• A key that doesn't exist is an error. It throws, naming the path (and the row) that broke.
• An empty array is fine and yields [].
• Arrays mid-path are mapped element by element, so items.name.en returns one value per item.

This is the strict mode, chosen deliberately: when an upstream API renames sku to code, the mapping fails loudly instead of quietly producing dropdowns full of empty values.

6. Loud in the editor, quiet in public

The same pipeline runs everywhere, but errors surface differently:

Internal sources that return exactly the expected columns can skip mapping entirely; external sources store their mapping with the source and apply it on every execution.

Lessons learned

• Normalize at the boundary. Let sources be messy; give consumers one clean shape.
• Show structure, not data. A type skeleton turns a 2,000-row response into something a person can map in seconds.
• Paths should describe every row, not one. Walking through arrays without indexes keeps mappings simple and general.
• Validate configuration against reality. Checking paths against the real result at save time catches typos immediately.
• Infer what you can. The longest common prefix finds the list without asking the user.
• "Missing" and "null" are different things. Treating absent keys as errors and null as data makes broken mappings visible.
• Fail loudly where people can fix things, quietly where they can't. Detailed errors in the editor, safe labels in public.

FAQ

What is a JSON skeleton?

A copy of a JSON value where every primitive is replaced by its type name ("string", "number"…), and arrays are represented by their first element's shape. It shows the structure of a response without its data.

How do I map nested JSON API responses to a flat list?

Describe each output field with a dotted path into the response, let paths step through arrays element by element, find the array that holds the rows (for example with the longest common path prefix), and resolve each path for every element.

Should missing fields in a mapping return null or throw an error?

For configuration you control, throwing is usually better: it reveals renamed or removed fields immediately. Keep null for fields that exist but genuinely have no value.

How can I validate a data mapping before saving it?

Run the source once, collect every reachable path from the result's structure, and check that each path in the mapping exists in that set. Reject the save with a message that names the invalid path.