Enrich Records from a Lookup
An order knows which SKU was bought; the catalogue knows its current display name.
An order knows which SKU was bought; the catalogue knows its current display name. Enrichment combines those two sources. The important decisions are what identifies a match, what happens when none exists, and what happens when more than one exists.
Example 91 — Read a catalogue lookup.
Input payload — read-a-catalogue-lookup-input.json:
{
"orderId": "A-1001",
"customer": "Dana",
"items": [
{
"sku": "PEN-01",
"price": 2.5,
"qty": 4
},
{
"sku": "PAD-22",
"price": 6.0,
"qty": 2
},
{
"sku": "CLP-08",
"price": 1.0,
"qty": 10
}
]
}%dw 2.0
output application/json
var catalogue = { "PEN-01": { name: "Gel pen" }, "PAD-22": { name: "A5 notepad" } }
---
payload.items map (item) -> { sku: item.sku, name: catalogue[item.sku].name default "(not in catalogue)", qty: item.qty }Result:
[
{
"sku": "PEN-01",
"name": "Gel pen",
"qty": 4
},
{
"sku": "PAD-22",
"name": "A5 notepad",
"qty": 2
},
{
"sku": "CLP-08",
"name": "(not in catalogue)",
"qty": 10
}
]The brackets contain an expression this time. item.sku supplies the key to select from the catalogue. The clips have no entry, so the missing path gives null and the default supplies the label.
This lookup assumes each SKU identifies one catalogue entry. Decide that rule before building the lookup from a list that might contain duplicates. Choosing the first record, choosing the latest and rejecting the conflict are different policies.
Exercises
A name lookup. Build an object from SKU to product name, with each SKU once. Run it. What happens if you leave out the distinctBy?
Show answer
Example 92 — Build a unique name lookup.
Input payload — lines.json:
[
{ "orderId": "A-1001", "sku": "PEN-01", "name": "Gel Pen", "category": "writing", "price": 2.5, "qty": 4 },
{ "orderId": "A-1001", "sku": "PAD-22", "name": "Notepad A5", "category": "paper", "price": 6.0, "qty": 2 },
{ "orderId": "A-1001", "sku": "CLP-08", "name": "Binder Clips", "category": "desk", "price": 1.0, "qty": 10 },
{ "orderId": "A-1008", "sku": "PEN-01", "name": "Gel Pen", "category": "writing", "price": 2.5, "qty": 1 },
{ "orderId": "A-1008", "sku": "INK-03", "name": "Ink Refill", "category": "writing", "price": 3.0, "qty": 3 }
]%dw 2.0
output application/json
---
{ ((payload distinctBy (line) -> line.sku) map (line) -> { (line.sku): line.name }) }{
"PEN-01": "Gel Pen",
"PAD-22": "Notepad A5",
"CLP-08": "Binder Clips",
"INK-03": "Ink Refill"
}Without distinctBy, PEN-01 appears twice in the spread, exactly as in the bySku example, and the JSON carries a duplicate key. And the distinctBy call needs its own parentheses, or its lambda swallows the map. Without them the run fails, with map being called on the String "PEN-01".
Joining two arrays
The function I see rewritten most often, usually as a nested map with a filter inside it, is a join. The order’s lines carry SKUs and the catalogue carries names:
Example 93 — Keep unmatched lines in a left join.
Input payload — order.json:
{ "orderId": "A-1001", "customer": "Dana", "coupon": null, "tags": ["gift", null, "rush"],
"items": [
{ "sku": "PEN-01", "price": 2.5, "qty": 4, "note": null },
{ "sku": "PAD-22", "price": 6.0, "qty": 2 },
{ "sku": "CLP-08", "price": 1.0, "qty": 10 }
] }%dw 2.0
import leftJoin from dw::core::Arrays
output application/json
var catalog = [
{ sku: "PEN-01", name: "Gel pen" },
{ sku: "PAD-22", name: "A5 notepad" }
]
---
leftJoin(payload.items, catalog, (i) -> i.sku, (c) -> c.sku)
map { sku: $.l.sku, qty: $.l.qty, name: $.r.name default "(not in catalog)" }[
{
"sku": "PEN-01",
"qty": 4,
"name": "Gel pen"
},
{
"sku": "PAD-22",
"qty": 2,
"name": "A5 notepad"
},
{
"sku": "CLP-08",
"qty": 10,
"name": "(not in catalog)"
}
]leftJoin takes the two arrays and a key function for each, and returns pairs under the keys l and r. CLP-08 is not in the catalogue, so its r is absent — where the default earns its place. The SQL vocabulary is deliberate: join drops the unmatched line instead. outerJoin, run against a catalogue carrying a stapler nobody ordered, returned four rows, with STP-03 having no l.
Example 94 — Keep duplicate catalogue matches visible.
%dw 2.0
import leftJoin from dw::core::Arrays
output application/json
var items = [{ sku: "PEN-01", qty: 4 }]
var catalogue = [{ sku: "PEN-01", name: "Old label" }, { sku: "PEN-01", name: "New label" }]
---
leftJoin(items, catalogue, (item) -> item.sku, (product) -> product.sku)Result:
[
{
"l": {
"sku": "PEN-01",
"qty": 4
},
"r": {
"sku": "PEN-01",
"name": "Old label"
}
},
{
"l": {
"sku": "PEN-01",
"qty": 4
},
"r": {
"sku": "PEN-01",
"name": "New label"
}
}
]One order line now matches two catalogue entries. Inspect the number of returned pairs before projecting them into a report. If both matches are carried into a revenue calculation, the order line can be counted twice.
A left join promises to retain left-side records; it does not promise exactly one result per left-side record when the right side has duplicate keys. The source contract or an explicit conflict-resolution step has to establish uniqueness.
Try it
Remove every catalogue entry and run the left join on the three order lines. Predict the number of results and decide how an absent product name should be represented.
Show answer
A left join retains the three order lines. Their right-side match is absent, so the selected name is null and can receive the documented fallback. An inner join would drop unmatched lines. Test the count as well as the name: a plausible fallback label cannot reveal a missing line.
Use a lookup for a documented one-entry-per-key source. Use a join when you need its matching semantics, then test the cardinality of the result. Neither mechanism can decide which conflicting supplier record is authoritative.
Comments