The API response has the data you need, but it is shaped for somebody else’s application. Your consumer wants order_id, a customer name, a total, and an array of SKUs. The source buries those values in an order document.

We will transform three supplied payloads using JSONata, directly inside DuckDB. No live API, credentials, or setup service is required.

Explore the idea

Build the document your consumer expects

Choose a transformation to see which parts of the supplied order documents it uses.

2

fields in the small summary

A nested value becomes a direct field

id becomes order_id, and buyer.name becomes customer. Unselected input fields are left out.

Test zero, one, and many

api_orders3 rows

Three supplied API payloads

Two items, no items, and one item: the cases that expose unstable output shapes.

idpayload
101{"id":101,"buyer":{"name":"Ada"},"currency":"USD","items":[{"sku":"MUG","qty":2,"unit_price":12.5},{"sku":"TEA","qty":1,"unit_price":8}]}
102{"id":102,"buyer":{"name":"Ben"},"currency":"USD","items":[]}
103{"id":103,"buyer":{"name":"Cy"},"currency":"USD","items":[{"sku":"MUG","qty":1,"unit_price":12.5}]}

Ada’s order contains two items, Ben’s contains none, and Cy’s contains one. These are intentionally different: a transformation that looks right for two matches may produce an inconvenient scalar for a single match.

Each row contains one JSON order. The examples assume the source contract guarantees a buyer name, a currency, and an items array containing numeric quantities and unit prices. Transformation does not replace validation; the JSON Schema lesson handles that earlier step.

Construct the object you want

The jsonata function takes an expression followed by the input document. Inside the expression, field paths refer to the current JSON document:

Build a small document with the field names you need

SELECT id, jsonata('{
  "order_id": id,
  "customer": buyer.name
}', payload)::VARCHAR AS summary
FROM api_orders ORDER BY id;
Expected result · sample data3 rows
id summary
101 {"customer":"Ada","order_id":101}
102 {"customer":"Ben","order_id":102}
103 {"customer":"Cy","order_id":103}

The output has order_id and customer, regardless of how those fields were nested or named in the source. Unselected fields stay out of the result. Object key order is not part of the JSON contract; consumers should look up fields by name.

Notice the two quoting layers: SQL uses single quotes around the JSONata expression, while the object’s field names use double quotes. The cast to VARCHAR makes the resulting JSON text convenient to display in the result table.

For one simple field extraction, DuckDB’s built-in JSON functions may be enough. JSONata becomes useful when the task is a document transformation, especially when you already have rules expressed in that language.

Make arrays predictable

Keep an array shape for zero, one, or many matches

SELECT id, jsonata('[items.sku]', payload)::VARCHAR AS skus,
  jsonata('$count(items)', payload)::INTEGER AS line_count
FROM api_orders ORDER BY id;
Expected result · sample data3 rows
id skus line_count
101 ["MUG","TEA"] 2
102 [] 0
103 ["MUG"] 1

The explicit [items.sku] array constructor produces an array for every order: two elements for Ada, none for Ben, one for Cy. A receiving application can iterate the same shape in all three cases.

$count(items) counts line items, not purchased units. Ada has two lines but three units. The distinction matters when naming a field such as item_count in an API response. JSONata’s construction guide explains explicit array and object construction.

Calculate while reshaping

Now build the complete summary:

Compute an order summary inside each JSON document

SELECT id, jsonata('{
  "order_id": id,
  "customer": buyer.name,
  "currency": currency,
  "total": $count(items) = 0 ? 0 : $sum(items.(qty * unit_price)),
  "skus": [items.sku]
}', payload)::VARCHAR AS transformed
FROM api_orders ORDER BY id;
Expected result · sample data3 rows
id transformed
101 {"currency":"USD","customer":"Ada","order_id":101,"skus":["MUG","TEA"],"total":33}
102 {"currency":"USD","customer":"Ben","order_id":102,"skus":[],"total":0}
103 {"currency":"USD","customer":"Cy","order_id":103,"skus":["MUG"],"total":12.5}

items.(qty * unit_price) evaluates the line amount within each item. $sum adds those values. The empty-array branch explicitly returns zero for an order with no items, so Ben’s output still contains a numeric total.

Ada’s amount is 2 × 12.50 + 1 × 8.00 = 33. Cy’s is 12.50. The result keeps currency beside the total; summing different currencies together would require another policy.

These are illustrative prices that do not expose floating-point rounding. For monetary settlement, choose a documented minor-unit or decimal calculation policy and test rounding boundaries. Renaming a field does not make an approximate numeric representation suitable for every financial use.

Your turn · check your answer

Keep only orders with at least one item

Return id and skus (a VARCHAR containing a JSON array) for orders with one or more items. Keep the array wrapper even for Cy’s single SKU.

See the expected result
Your target result2 rows
id skus
101 ["MUG","TEA"]
103 ["MUG"]
Sample data is loaded for you.

Preparing the editor…

Need a hint?

Use $count(items) to filter and [items.sku] to construct an array.

Show a solution
SELECT id, jsonata('[items.sku]', payload)::VARCHAR AS skus FROM api_orders WHERE jsonata('$count(items)', payload)::INTEGER > 0 ORDER BY id;

Separate shape from meaning

A well-shaped output can still contain invalid business values. Missing buyer fields, negative quantities, or malformed prices need explicit handling under the input contract. Validate or quarantine those documents before calling their transformed totals authoritative.

If your goal is a relational item report instead, use the nested JSON tutorial. Here we deliberately keep one output document per order, ready to hand to another JSON-speaking system.