Pattern 1: Flattening JSON objects with dynamic keys into CSV rows
When routing external queue payloads or audit tables into Workday Prism Analytics Data Change Tasks (DCTs), a single orchestration often needs to convert JSON arrays into flat CSV files where the nested object keys vary by table. For example, one payload’s SpecificContent object may contain {"Name": "Alice", "Age": 34, "Location": "NY"}, while another payload’s SpecificContent contains {"CostCenter": "CC100", "Region": "NA", "Grade": "P4", "Exempt": "true"}:
{
"value": [
{
"Status": "Successful",
"SpecificContent": {
"Name": "Alice",
"Age": 34,
"Location": "NY"
},
"Output": {
"Result": "Processed"
}
}
]
}First, do not use a Join Loop when SpecificContent and Output sit inside the same array element ($.value[*]). A Join Loop merges two separate collections that share a common foreign key; sibling objects inside the same JSON array element are already in scope together on each iteration of a standard Loop.
Second, to avoid hardcoding column names ($.SpecificContent.Name) or array indices (0, 1, 2) per table, extract the keys dynamically using a JsonKeyValueIterator (Reference: JsonKeyValue Member Functions) and iterate over that StringList of keys to assemble both the CSV header line and each CSV data row.
Step 1: Extract the dynamic keys from the first element into a StringList
Assuming all elements in a single JSON payload share the same SpecificContent schema, inspect the first array item ($.value[0].SpecificContent):
- Add a Loop step named
ExtractDynamicKeysLoop. - Set Data Type to
JsonKeyValueIterator(orAutoType) and set Data Set to:javascript data.InboundPayload.response.asJSON().iterator("$.value[0].SpecificContent") - Add an Aggregation to
ExtractDynamicKeysLoopnamedDynamicKeyListusing the String List strategy, and set Select String to:javascript data.ExtractDynamicKeysLoop.item.key()
After ExtractDynamicKeysLoop finishes, data.ExtractDynamicKeysLoop.DynamicKeyList holds a StringList of all dynamic column names (whether there are 3 keys or 25 keys), and data.ExtractDynamicKeysLoop.DynamicKeyList.mkString(",") produces the comma-separated header prefix ("Name,Age,Location").
Step 2: Build each flat CSV row with a nested loop over DynamicKeyList
- Add an outer Loop step named
OuterRowLoopiterating overdata.InboundPayload.response.asJSON().iterator("$.value[*]"). - Inside
OuterRowLoop, place an inner Loop step namedInnerColumnLoopiterating overdata.ExtractDynamicKeysLoop.DynamicKeyList.iterator(). - On
InnerColumnLoop, add a String List aggregation namedDynamicColumnValuesthat looks up the current key dynamically from the outer JSON item. To handle strings, numbers, or nulls uniformly as strings, extract the value via JSONPath:(If your dynamic object contains unquoted JSON numbers or booleans alongside strings, usejavascript data.OuterRowLoop.item.stringAtJsonPathOrEmptyString( "$.SpecificContent.".append(data.InnerColumnLoop.item) )data.OuterRowLoop.item.objectAtJsonPath("$.SpecificContent").iterator("$")or normalize primitives to strings first so.stringAtJsonPathOrEmptyStringreads them cleanly.) - Still inside
OuterRowLoop(afterInnerColumnLoop), combine the dynamic columns with your fixed columns (Status,Output.Result) into a single comma-separated row string and aggregate the rows acrossOuterRowLoopusing a Text Fold (with\nseparator) or String List aggregation (AllCsvRows). - Prepend the header row (
data.ExtractDynamicKeysLoop.DynamicKeyList.mkString(",").append(",Status,Result")) above the aggregated rows in a Create Text Template step.
Pattern 2: Grouping flat CSV invoice rows into nested JSON arrays
The inverse transformation is just as common: an inbound CSV file repeats parent invoice header fields on every line while listing individual line items on separate rows:
InvoiceNumber,CustomerID,LineNumber,LineDesc,Amount
Inv-001,Cust-001,1,Consulting,100.00
Inv-001,Cust-001,2,Travel,200.00
Inv-002,Cust-003,1,Software,150.00Trying to build the parent JSON object and inject the child Lines array using multiple chained Create JSON steps is cumbersome. Instead, combine a Batch Loop configured with the Group strategy and a single Create Text Template step.
Why {{#unless @last}},{{/unless}} fails in Create Text Template
Developers familiar with Handlebars often try to suppress the trailing comma after the last array element in Create Text Template using {{#unless @last}},{{/unless}}. That fails to compile or render because Workday Orchestrate’s Create Text Template engine is not full Handlebars—as documented in Concept: Text Templates, it supports #if, #each, #alias, #with, #function, and #template, along with the unary ! (NOT) operator, and does not support #unless or @last.
Instead, every iterator item inside {{#each ...}} in Create Text Template exposes Orchestrate’s native .isFirst and .isLast boolean properties. To output a comma after every JSON array element except the last one, use {{#if !this.isLast}},{{/if}}.
Step-by-step configuration
- Add a Batch Loop step named
GroupByInvoiceLoopover your CSV iterator (data.CsvInput.asCSV("InvoiceCsvFormat").rows()). - Set Strategy to Group, set Group By to
data.GroupByInvoiceLoop.item.columnValue("InvoiceNumber"), and set Group Name toInvoiceGroup. Each batch now contains all CSV rows for a singleInvoiceNumber. - Inside
GroupByInvoiceLoop, extract the header fields from the first row of the group (or via a Create Values step reading the group) and use a Create Text Template step (Content Type: application/json) to build the complete nested invoice JSON:
{
"Invoice_Number": "{{data.InvoiceHeaderValues.invoiceNumber}}",
"Customer_Reference": "{{data.InvoiceHeaderValues.customerId}}",
"Lines": [
{{#each data.GroupByInvoiceLoop.item.iterator}}
{
"LineNumber": "{{this.columnValue("LineNumber")}}",
"LineDesc": "{{this.columnValue("LineDesc")}}",
"Amount": "{{this.columnValue("Amount")}}"
}{{#if !this.isLast}},{{/if}}
{{/each}}
]
}- Add a JSON Fold aggregation on
GroupByInvoiceLoopselecting the output of the Create Text Template step (cast with.asJSON()if needed) to combine all grouped invoices into a single valid JSON array.
How to verify dynamic CSV and grouped JSON transformations in the tenant
- For dynamic JSON-to-CSV, run Debug Step on
ExtractDynamicKeysLoopand the final Create Text Template step using two JSON payloads with differentSpecificContentkey sets, confirming that the header row and column values adapt automatically without code changes. - For grouped CSV-to-JSON, add an
.assertValid()call (Reference: Json Member Functions) on the generated JSON output (data.BuildInvoiceJson.message.asJSON().assertValid()) and run a test file containing both multi-line invoices (Inv-001) and single-line invoices (Inv-002) to verify that{{#if !this.isLast}},{{/if}}produces valid JSON with no trailing commas.