Skip to content
KiweelyKiweelyKiweely

Transforming dynamic-key JSON to CSV and grouping flat CSV rows into nested JSON in Orchestrate

Use json.iterator(‘$.value[0].SpecificContent’) with JsonKeyValueIterator and .mkString(‘,’) to build dynamic CSV headers and rows without hardcoding columns. When grouping flat CSV rows into nested JSON in Create Text Template, use !this.isLast instead of unsupported @last or #unless.

  • Checked against Workday on
  • Applies to Workday Orchestrate
  • 4 min read
  • Published
  • By Kiweely
On this page

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"}:

json
{
  "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):

  1. Add a Loop step named ExtractDynamicKeysLoop.
  2. Set Data Type to JsonKeyValueIterator (or AutoType) and set Data Set to:
    javascript
    data.InboundPayload.response.asJSON().iterator("$.value[0].SpecificContent")
  3. Add an Aggregation to ExtractDynamicKeysLoop named DynamicKeyList using 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

  1. Add an outer Loop step named OuterRowLoop iterating over data.InboundPayload.response.asJSON().iterator("$.value[*]").
  2. Inside OuterRowLoop, place an inner Loop step named InnerColumnLoop iterating over data.ExtractDynamicKeysLoop.DynamicKeyList.iterator().
  3. On InnerColumnLoop, add a String List aggregation named DynamicColumnValues that 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:
    javascript
    data.OuterRowLoop.item.stringAtJsonPathOrEmptyString(
      "$.SpecificContent.".append(data.InnerColumnLoop.item)
    )
    (If your dynamic object contains unquoted JSON numbers or booleans alongside strings, use data.OuterRowLoop.item.objectAtJsonPath("$.SpecificContent").iterator("$") or normalize primitives to strings first so .stringAtJsonPathOrEmptyString reads them cleanly.)
  4. Still inside OuterRowLoop (after InnerColumnLoop), combine the dynamic columns with your fixed columns (Status, Output.Result) into a single comma-separated row string and aggregate the rows across OuterRowLoop using a Text Fold (with \n separator) or String List aggregation (AllCsvRows).
  5. 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:

csv
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.00

Trying 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

  1. Add a Batch Loop step named GroupByInvoiceLoop over your CSV iterator (data.CsvInput.asCSV("InvoiceCsvFormat").rows()).
  2. Set Strategy to Group, set Group By to data.GroupByInvoiceLoop.item.columnValue("InvoiceNumber"), and set Group Name to InvoiceGroup. Each batch now contains all CSV rows for a single InvoiceNumber.
  3. 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:
handlebars
{
  "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}}
  ]
}
  1. Add a JSON Fold aggregation on GroupByInvoiceLoop selecting 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

  1. For dynamic JSON-to-CSV, run Debug Step on ExtractDynamicKeysLoop and the final Create Text Template step using two JSON payloads with different SpecificContent key sets, confirming that the header row and column values adapt automatically without code changes.
  2. 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.

Sources checked

Related guides

All guides

Let Workday check the work.

Kiweely writes WQL and builds orchestrations in your own tenant, and Workday, not Kiweely, says whether they passed.

Get the next guide in your inbox.

One email a month with new guides and one Extend tip. Nothing else.

We’ll email you once to confirm. Unsubscribe in one click from any issue.