Nested Json to Flat Json
This example show usage of DataWeave to transform nested json structure to flat json structure. i break them into step 1 and 2 to better illustrate the
transformation.
Input Json:
{
"Orders": [{
"OrderID": 2501,
"Customer": "Alex",
"Country": "USA",
"Items": [{
"ProductID": "P05",
"UnitPrice": 50,
"Quantity": 1,
"Variants": [{
"Color": "Blue",
"Quantity": 1
}
]
}
]
}, {
"OrderID": 2502,
"Customer": "Bob",
"Country": "UK",
"Items": [{
"ProductID": "P05",
"UnitPrice": 50,
"Quantity": 5,
"Variants": [{
"Color": "Blue",
"Quantity": 2
}, {
"Color": "Red",
"Quantity": 3
}
]
}, {
"ProductID": "P06",
"UnitPrice": 80,
"Quantity": 1,
"Variants": [{
"Color": "Yellow",
"Quantity": 1
}
]
}
]
}, {
"OrderID": 2503,
"Customer": "Peter",
"Country": "UK",
"Items": [{
"ProductID": "P07",
"UnitPrice": 120,
"Quantity": 10,
"Variants": [{
"Color": "Green",
"Quantity": 4
},{
"Color": "Yellow",
"Quantity": 6
}
]
}
]
}
]
}
After Step 1:
[
[
[
{
"OrderID": 2501,
"Customer": "Alex",
"Country": "USA",
"ProductID": "P05",
"UnitPrice": 50,
"Color": "Blue",
"Quantity": 1
}
]
],
[
[
{
"OrderID": 2502,
"Customer": "Bob",
"Country": "UK",
"ProductID": "P05",
"UnitPrice": 50,
"Color": "Blue",
"Quantity": 2
},
{
"OrderID": 2502,
"Customer": "Bob",
"Country": "UK",
"ProductID": "P05",
"UnitPrice": 50,
"Color": "Red",
"Quantity": 3
}
],
[
{
"OrderID": 2502,
"Customer": "Bob",
"Country": "UK",
"ProductID": "P06",
"UnitPrice": 80,
"Color": "Yellow",
"Quantity": 1
}
]
],
[
[
{
"OrderID": 2503,
"Customer": "Peter",
"Country": "UK",
"ProductID": "P07",
"UnitPrice": 120,
"Color": "Green",
"Quantity": 4
},
{
"OrderID": 2503,
"Customer": "Peter",
"Country": "UK",
"ProductID": "P07",
"UnitPrice": 120,
"Color": "Yellow",
"Quantity": 6
}
]
]
]
After Step 2:
[
{
"OrderID": 2501,
"Customer": "Alex",
"Country": "USA",
"ProductID": "P05",
"UnitPrice": 50,
"Color": "Blue",
"Quantity": 1
},
{
"OrderID": 2502,
"Customer": "Bob",
"Country": "UK",
"ProductID": "P05",
"UnitPrice": 50,
"Color": "Blue",
"Quantity": 2
},
{
"OrderID": 2502,
"Customer": "Bob",
"Country": "UK",
"ProductID": "P05",
"UnitPrice": 50,
"Color": "Red",
"Quantity": 3
},
{
"OrderID": 2502,
"Customer": "Bob",
"Country": "UK",
"ProductID": "P06",
"UnitPrice": 80,
"Color": "Yellow",
"Quantity": 1
},
{
"OrderID": 2503,
"Customer": "Peter",
"Country": "UK",
"ProductID": "P07",
"UnitPrice": 120,
"Color": "Green",
"Quantity": 4
},
{
"OrderID": 2503,
"Customer": "Peter",
"Country": "UK",
"ProductID": "P07",
"UnitPrice": 120,
"Color": "Yellow",
"Quantity": 6
}
]
Step 1:
Using nested map to 1st outer loop at Orders, then 2nd inner loop at Items, finally 3rd inner loop at Variants.
At Orders and Items map, note that after the “->”, is using parentheses (), this is to output array of array only, without array’s elements.
At Variants map, after the “->” is using curly bracket {} to output array elements.
%dw 2.0
output application/json
---
payload.Orders map(o, o_idx) ->(
o.Items map(i, i_idx) ->(
i.Variants map(v, v_idx) -> {
"OrderID": o.OrderID,
"Customer": o.Customer,
"Country": o.Country,
"ProductID": i.ProductID,
"UnitPrice": i.UnitPrice,
"Color": v.Color,
"Quantity": v.Quantity
}
)
)
Step 2:
Adding flatten to convert nested array of arrays to flat. Here have to use flatten 2 times, because there are 2 layers of conversion:
layer 1: flatten Order’s Items
layer 2: flatten Item’s Variants
%dw 2.0
output application/json
---
flatten(payload.Orders map(o, o_idx) ->(
flatten(o.Items map(i, i_idx) ->(
i.Variants map(v, v_idx) -> {
"OrderID": o.OrderID,
"Customer": o.Customer,
"Country": o.Country,
"ProductID": i.ProductID,
"UnitPrice": i.UnitPrice,
"Color": v.Color,
"Quantity": v.Quantity
}
))
))
Nested Xml to Flat Xml
This is another example doing similar thing, but for Xml.
Input XML:
<?xml version="1.0" encoding="UTF-8"?>
<Root>
<Order>
<OrderID>2501</OrderID>
<Customer>Alex</Customer>
<Country>USA</Country>
<Item>
<ProductID>P05</ProductID>
<UnitPrice>50</UnitPrice>
<Quantity>1</Quantity>
<Variant>
<Color>Blue</Color>
<Quantity>1</Quantity>
</Variant>
</Item>
</Order>
<Order>
<OrderID>2502</OrderID>
<Customer>Bob</Customer>
<Country>UK</Country>
<Item>
<ProductID>P05</ProductID>
<UnitPrice>50</UnitPrice>
<Quantity>5</Quantity>
<Variant>
<Color>Blue</Color>
<Quantity>2</Quantity>
</Variant>
<Variant>
<Color>Red</Color>
<Quantity>3</Quantity>
</Variant>
</Item>
<Item>
<ProductID>P06</ProductID>
<UnitPrice>80</UnitPrice>
<Quantity>1</Quantity>
<Variant>
<Color>Yellow</Color>
<Quantity>1</Quantity>
</Variant>
</Item>
</Order>
<Order>
<OrderID>2503</OrderID>
<Customer>Peter</Customer>
<Country>UK</Country>
<Item>
<ProductID>P07</ProductID>
<UnitPrice>120</UnitPrice>
<Quantity>10</Quantity>
<Variant>
<Color>Green</Color>
<Quantity>4</Quantity>
</Variant>
<Variant>
<Color>Yellow</Color>
<Quantity>6</Quantity>
</Variant>
</Item>
</Order>
</Root>
Output XML:
<?xml version='1.0' encoding='windows-1252'?>
<root>
<row>
<OrderID>2501</OrderID>
<Customer>Alex</Customer>
<Country>USA</Country>
<ProductID>P05</ProductID>
<UnitPrice>50</UnitPrice>
<Color>Blue</Color>
<Quantity>1</Quantity>
</row>
<row>
<OrderID>2502</OrderID>
<Customer>Bob</Customer>
<Country>UK</Country>
<ProductID>P05</ProductID>
<UnitPrice>50</UnitPrice>
<Color>Blue</Color>
<Quantity>2</Quantity>
</row>
<row>
<OrderID>2502</OrderID>
<Customer>Bob</Customer>
<Country>UK</Country>
<ProductID>P05</ProductID>
<UnitPrice>50</UnitPrice>
<Color>Red</Color>
<Quantity>3</Quantity>
</row>
<row>
<OrderID>2502</OrderID>
<Customer>Bob</Customer>
<Country>UK</Country>
<ProductID>P06</ProductID>
<UnitPrice>80</UnitPrice>
<Color>Yellow</Color>
<Quantity>1</Quantity>
</row>
<row>
<OrderID>2503</OrderID>
<Customer>Peter</Customer>
<Country>UK</Country>
<ProductID>P07</ProductID>
<UnitPrice>120</UnitPrice>
<Color>Green</Color>
<Quantity>4</Quantity>
</row>
<row>
<OrderID>2503</OrderID>
<Customer>Peter</Customer>
<Country>UK</Country>
<ProductID>P07</ProductID>
<UnitPrice>120</UnitPrice>
<Color>Yellow</Color>
<Quantity>6</Quantity>
</row>
</root>
DataWeave code:
For XML, loop through each layer (Order, Item and Variant) using multi-value selector (.*<key-name>). Note that there is no need to use flatten operator that expect an array.
%dw 2.0
output application/xml
---
"root":{
(payload.Root.*Order map(o, o_idx) -> {
(o.*Item map(i, i_idx) -> {
"row": i.*Variant map(v, v_idx) -> {
"OrderID": o.OrderID,
"Customer": o.Customer,
"Country": o.Country,
"ProductID": i.ProductID,
"UnitPrice": i.UnitPrice,
"Color": v.Color,
"Quantity": v.Quantity
}
})
})
}
Pingback:DataWeave – Transform Flat Structure to Nested using groupBy, map, mapObject and orderBy – INTEGRATIONLEARN.COM
Please advise how to learn data weave from scratch
THis is helpful but i am running into a scenario . what if one of the set doesnt have Variants. I am getting NULL in this situation. But I need the Orders and Items value and empty string for Variants, how this can be done
“Orders”: [{
“OrderID”: 2501,
“Customer”: “Alex”,
“Country”: “USA”,
“Items”: [{
“ProductID”: “P05”,
“UnitPrice”: 50,
“Quantity”: 1
]
}
]
},
Hi Raghu,
Think using ‘default’ can help in your case. see the example defaultVariant below.
the script:
————-
%dw 2.0
output application/json
var payload = {
“Orders”: [{
“OrderID”: 2502,
“Customer”: “Bob”,
“Country”: “UK”,
“Items”: [{
“ProductID”: “P05”,
“UnitPrice”: 50,
“Quantity”: 5,
“Variants”: [{
“Color”: “Blue”,
“Quantity”: 2
}, {
“Color”: “Red”,
“Quantity”: 3
}
]
}, {
“ProductID”: “P06”,
“UnitPrice”: 80,
“Quantity”: 1
}
]
}
]
}
var defaultVariant = {
“Color”: “”,
“Quantity”: null
}
—
flatten(payload.Orders map(o, o_idx) ->(
flatten(o.Items map(i, i_idx) ->(
i.Variants default [defaultVariant] map(v, v_idx) -> {
“OrderID”: o.OrderID,
“Customer”: o.Customer,
“Country”: o.Country,
“ProductID”: i.ProductID,
“UnitPrice”: i.UnitPrice,
“Color”: v.Color,
“Quantity”: v.Quantity
}
))
))
output:
———
[
{
“OrderID”: 2502,
“Customer”: “Bob”,
“Country”: “UK”,
“ProductID”: “P05”,
“UnitPrice”: 50,
“Color”: “Blue”,
“Quantity”: 2
},
{
“OrderID”: 2502,
“Customer”: “Bob”,
“Country”: “UK”,
“ProductID”: “P05”,
“UnitPrice”: 50,
“Color”: “Red”,
“Quantity”: 3
},
{
“OrderID”: 2502,
“Customer”: “Bob”,
“Country”: “UK”,
“ProductID”: “P06”,
“UnitPrice”: 80,
“Color”: “”,
“Quantity”: null
}
]