Have you ever stared at a massive spreadsheet or a flat list of JSON data and felt like you were trying to solve a jigsaw puzzle without the picture on the box? That is exactly what working with "flat" hierarchical data feels like.
Imagine a family tree. Instead of a nice branching diagram, you just have a list of names. You know "Record B" belongs to "Record A," but when you have thousands of these connections, things get messy fast. In the world of data analysis and MuleSoft development, we see this constantly: product catalogs, organizational charts, and manufacturing bills of materials.
To build great reports or apps, we often need to turn that flat list into a clean, nested structure. In this guide, we are going to use DataWeave to magically transform a flat JSON list into a structured, easy-to-read hierarchy.
The Challenge: Dealing with Flat Data
Let's look at our starting point. We have a list of records. Some are "parents" (top-level), and others are "children" linked by a parent ID. The problem is that they are all mixed together in one big array.
Input Data
Here is the raw JSON we need to process:
[
{
"ID": "record 0",
"name": "record Level 00",
"parent": "record 123",
"value": 441.25,
"rate": 0.17
},
{
"ID": "record 1",
"name": "record Level 01",
"parent": "record 0",
"value": 1049.99,
"rate": 0.5
},
{
"ID": "record 2",
"name": "record Level 02",
"parent": "record 1",
"value": 748.6,
"rate": 0.4
},
{
"ID": "record 3",
"name": "record Level 03",
"parent": "record 123",
"value": 1206.9,
"rate": 0.36
},
{
"ID": "record 4",
"name": "record Level 04",
"parent": "record 3",
"value": 1081.04,
"rate": 0.14
},
{
"ID": "record 5",
"name": "record Level 05",
"parent": "record 146542",
"value": 1187.64,
"rate": 0.74
},
{
"ID": "record 6",
"name": "record Level 06",
"parent": "record 5",
"value": 411.21,
"rate": 0.09
},
{
"ID": "record 7",
"name": "record Level 07",
"parent": "record 05432",
"value": 1937.97,
"rate": 0.02
},
{
"ID": "record 8",
"name": "record Level 08",
"parent": "record 6",
"value": 681.16,
"rate": 0.46
},
{
"ID": "record 9",
"name": "record Level 09",
"parent": "record 2",
"value": 262.59,
"rate": 0.66
},
{
"ID": "record 10",
"name": "record Level 010",
"parent": "record 8",
"value": 1887.42,
"rate": 0.79
}
]The Goal: A Structured Tree
We want to organize the data so that every child sits snugly inside its parent group. This makes it infinitely easier to pass to a frontend application or iterate over for logic processing.
Target Output
Notice how the data is now grouped by "branches" of the tree:
[
[
{
"ID": "record 0",
"name": "record Level 00",
"parent": "record 123",
"value": 441.25,
"rate": 0.17
},
{
"ID": "record 1",
"name": "record Level 01",
"parent": "record 0",
"value": 1049.99,
"rate": 0.5
},
{
"ID": "record 2",
"name": "record Level 02",
"parent": "record 1",
"value": 748.6,
"rate": 0.4
},
{
"ID": "record 9",
"name": "record Level 09",
"parent": "record 2",
"value": 262.59,
"rate": 0.66
}
],
[
{
"ID": "record 3",
"name": "record Level 03",
"parent": "record 123",
"value": 1206.9,
"rate": 0.36
},
{
"ID": "record 4",
"name": "record Level 04",
"parent": "record 3",
"value": 1081.04,
"rate": 0.14
}
],
[
{
"ID": "record 5",
"name": "record Level 05",
"parent": "record 146542",
"value": 1187.64,
"rate": 0.74
},
{
"ID": "record 6",
"name": "record Level 06",
"parent": "record 5",
"value": 411.21,
"rate": 0.09
},
{
"ID": "record 8",
"name": "record Level 08",
"parent": "record 6",
"value": 681.16,
"rate": 0.46
},
{
"ID": "record 10",
"name": "record Level 010",
"parent": "record 8",
"value": 1887.42,
"rate": 0.79
}
],
[
{
"ID": "record 7",
"name": "record Level 07",
"parent": "record 05432",
"value": 1937.97,
"rate": 0.02
}
]
]The DataWeave Solution
This script does the heavy lifting. It identifies the "root" of each tree and recursively hunts down every branch connected to it. It uses DataWeave 2.0 features like reduce, custom functions, and the -- (difference) operator.
The Code
%dw 2.0
output application/json
// Filter top-level items and build the hierarchy
var parentRecords = payload[?((!(payload.ID contains $.parent)))]
var chaildInfo = payload -- parentRecords
fun getChildData(data, currentChild) =
(flatten([
data
]) reduce ((item, accumulator = []) -> do {
var chaild = currentChild[?(($.parent ~= item.ID))] default []
var latestChaild = (currentChild default []) -- (chaild default [])
---
if (isEmpty(chaild))
(accumulator << item)
else
accumulator ++ getChildData(([item] ++ chaild), latestChaild)
}
)
)
---
parentRecords default [] map (
getChildData($, chaildInfo)
)How It Works
This script might look a little intimidating at first glance, but let's break it down into simple logic blocks:
- Finding the Parents (Roots): First, the script isolates the
parentRecords. It looks for items where theparentID does not exist anywhere else in the list of record IDs. If a record points to a parent that isn't in the file (like "record 123"), DataWeave treats it as a top-level root. - The Recursive Loop: We use a function called
getChildData. This is a recursive function—meaning it calls itself. It grabs a parent, looks for any children linked to that ID, and then (this is the cool part) it asks, "Do these children have children?" - Grouping: It continues this process until it reaches the end of a branch, bundling everything into neat arrays.
Why Use This Approach?
- Efficiency: DataWeave handles this transformation much faster than trying to loop through data manually in imperative languages like Java or Python.
- Flexibility: Whether your hierarchy is 2 levels deep or 20, this script adapts dynamically.
- Readability: Once you understand the recursive pattern, it is much cleaner than writing hundreds of lines of
if/elsestatements.