In previous post, transformed nested structure to flat. In this post example will do the reverse, transform flat structure back to original nested format, using nested groupBy, map, mapObject and orderBy.


Flat Json to Nested Json

Requirements:
1) nested group by OrderID, then ProductID, then Variant/Color.
2) order by OrderID, ProductID
3) Construct the final json using array, mean using [].

Had break the DataWeave code in incremental logical steps based on how I build them. Showing different steps, able to see how the intermediate json payload look like, before it become final json output. Good for understanding and reference again in the future.

Input Json:

[
  {
    "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 1:

{
  "2502": [
    {
      "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
    }
  ],
  "2501": [
    {
      "OrderID": 2501,
      "Customer": "Alex",
      "Country": "USA",
      "ProductID": "P05",
      "UnitPrice": 50,
      "Color": "Blue",
      "Quantity": 1
    }
  ],
  "2503": [
    {
      "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:

{
  "Orders": {
    "Order": {
      "OrderID": "2502",
      "Customer": "Bob",
      "Country": "UK"
    },
    "Order": {
      "OrderID": "2501",
      "Customer": "Alex",
      "Country": "USA"
    },
    "Order": {
      "OrderID": "2503",
      "Customer": "Peter",
      "Country": "UK"
    }
  }
}

After Step 3:

{
  "Orders": {
    "Order": {
      "OrderID": "2502",
      "Customer": "Bob",
      "Country": "UK",
      "Products": {
        "Product": {
          "ProductID": "P06",
          "UnitPrice": 80,
          "Quantity": 1
        },
        "Product": {
          "ProductID": "P05",
          "UnitPrice": 50,
          "Quantity": 5
        }
      }
    },
    "Order": {
      "OrderID": "2501",
      "Customer": "Alex",
      "Country": "USA",
      "Products": {
        "Product": {
          "ProductID": "P05",
          "UnitPrice": 50,
          "Quantity": 1
        }
      }
    },
    "Order": {
      "OrderID": "2503",
      "Customer": "Peter",
      "Country": "UK",
      "Products": {
        "Product": {
          "ProductID": "P07",
          "UnitPrice": 120,
          "Quantity": 10
        }
      }
    }
  }
}

After Step 4:

{
  "Orders": {
    "Order": {
      "OrderID": "2502",
      "Customer": "Bob",
      "Country": "UK",
      "Products": {
        "Product": {
          "ProductID": "P06",
          "UnitPrice": 80,
          "Quantity": 1,
          "Variants": [
            {
              "Variant": {
                "Color": "Yellow",
                "Quantity": 1
              }
            }
          ]
        },
        "Product": {
          "ProductID": "P05",
          "UnitPrice": 50,
          "Quantity": 5,
          "Variants": [
            {
              "Variant": {
                "Color": "Blue",
                "Quantity": 2
              }
            },
            {
              "Variant": {
                "Color": "Red",
                "Quantity": 3
              }
            }
          ]
        }
      }
    },
    "Order": {
      "OrderID": "2501",
      "Customer": "Alex",
      "Country": "USA",
      "Products": {
        "Product": {
          "ProductID": "P05",
          "UnitPrice": 50,
          "Quantity": 1,
          "Variants": [
            {
              "Variant": {
                "Color": "Blue",
                "Quantity": 1
              }
            }
          ]
        }
      }
    },
    "Order": {
      "OrderID": "2503",
      "Customer": "Peter",
      "Country": "UK",
      "Products": {
        "Product": {
          "ProductID": "P07",
          "UnitPrice": 120,
          "Quantity": 10,
          "Variants": [
            {
              "Variant": {
                "Color": "Green",
                "Quantity": 4
              }
            },
            {
              "Variant": {
                "Color": "Yellow",
                "Quantity": 6
              }
            }
          ]
        }
      }
    }
  }
}

After Step 5:

{
  "Orders": [
    {
      "OrderID": "2502",
      "Customer": "Bob",
      "Country": "UK",
      "Products": [
        {
          "ProductID": "P06",
          "UnitPrice": 80,
          "Quantity": 1,
          "Variants": [
            {
              "Color": "Yellow",
              "Quantity": 1
            }
          ]
        },
        {
          "ProductID": "P05",
          "UnitPrice": 50,
          "Quantity": 5,
          "Variants": [
            {
              "Color": "Blue",
              "Quantity": 2
            },
            {
              "Color": "Red",
              "Quantity": 3
            }
          ]
        }
      ]
    },
    {
      "OrderID": "2501",
      "Customer": "Alex",
      "Country": "USA",
      "Products": [
        {
          "ProductID": "P05",
          "UnitPrice": 50,
          "Quantity": 1,
          "Variants": [
            {
              "Color": "Blue",
              "Quantity": 1
            }
          ]
        }
      ]
    },
    {
      "OrderID": "2503",
      "Customer": "Peter",
      "Country": "UK",
      "Products": [
        {
          "ProductID": "P07",
          "UnitPrice": 120,
          "Quantity": 10,
          "Variants": [
            {
              "Color": "Green",
              "Quantity": 4
            },
            {
              "Color": "Yellow",
              "Quantity": 6
            }
          ]
        }
      ]
    }
  ]
}

After Step 6:

{
  "Orders": [
    {
      "OrderID": "2501",
      "Customer": "Alex",
      "Country": "USA",
      "Products": [
        {
          "ProductID": "P05",
          "UnitPrice": 50,
          "Quantity": 1,
          "Variants": [
            {
              "Color": "Blue",
              "Quantity": 1
            }
          ]
        }
      ]
    },
    {
      "OrderID": "2502",
      "Customer": "Bob",
      "Country": "UK",
      "Products": [
        {
          "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",
      "Products": [
        {
          "ProductID": "P07",
          "UnitPrice": 120,
          "Quantity": 10,
          "Variants": [
            {
              "Color": "Green",
              "Quantity": 4
            },
            {
              "Color": "Yellow",
              "Quantity": 6
            }
          ]
        }
      ]
    }
  ]
}


Step 1:
This step simply group by OrderID.

%dw 2.0
output application/json
---
payload groupBy $.OrderID


Step 2:
This step add the 1st layer Orders.

%dw 2.0
output application/json
---
{
	"Orders": (payload groupBy $.OrderID mapObject(ogrp, ogrp_key) ->(
		"Order": {
			"OrderID": ogrp_key,
			"Customer": ogrp.Customer[0],
			"Country": ogrp.Country[0]
		}
	))
}


Step 3:
This step add nested 2nd layer Items, under 1st Orders layer.

%dw 2.0
output application/json
---
{
	"Orders": (payload groupBy $.OrderID mapObject(ogrp, ogrp_key) ->(
		"Order": {
			"OrderID": ogrp_key,
			"Customer": ogrp.Customer[0],
			"Country": ogrp.Country[0],
			"Products": (ogrp groupBy $.ProductID mapObject(pgrp, pgrp_key) ->(
				"Product": {
					"ProductID": pgrp_key,
					"UnitPrice": pgrp.UnitPrice[0],
					"Quantity": sum(pgrp.Quantity)
				}
			))
		}
	))
}


Step 4:
This step add nested 3rd layer Variants, under 2nd layer Items.

%dw 2.0
output application/json
---
{
	"Orders": (payload groupBy $.OrderID mapObject(ogrp, ogrp_key) ->(
		"Order": {
			"OrderID": ogrp_key,
			"Customer": ogrp.Customer[0],
			"Country": ogrp.Country[0],
			"Products": (ogrp groupBy $.ProductID mapObject(pgrp, pgrp_key) ->(
				"Product": {
					"ProductID": pgrp_key,
					"UnitPrice": pgrp.UnitPrice[0],
					"Quantity": sum(pgrp.Quantity),
					"Variants": (pgrp map(vnt, vnt_idx) -> {
						"Variant": {
							"Color": vnt.Color,
							"Quantity": vnt.Quantity
						}
					})
				}
			))
		}
	))
}


Step 5:
This step convert list of elements to array.

%dw 2.0
output application/json
---
{
	"Orders": (payload groupBy $.OrderID mapObject(ogrp, ogrp_key) ->(
		"Order": {
			"OrderID": ogrp_key,
			"Customer": ogrp.Customer[0],
			"Country": ogrp.Country[0],
			"Products": (ogrp groupBy $.ProductID mapObject(pgrp, pgrp_key) ->(
				"Product": {
					"ProductID": pgrp_key,
					"UnitPrice": pgrp.UnitPrice[0],
					"Quantity": sum(pgrp.Quantity),
					"Variants": (pgrp map(vnt, vnt_idx) -> {
						"Variant": {
							"Color": vnt.Color,
							"Quantity": vnt.Quantity
						}
					}).*Variant
				}
			)).*Product
		}
	)).*Order
}


Step 6:
This step add orderBy OrderID, ProductID, Quantity.

%dw 2.0
output application/json
---
{
	"Orders": (payload groupBy $.OrderID mapObject(ogrp, ogrp_key) ->(
		"Order": {
			"OrderID": ogrp_key,
			"Customer": ogrp.Customer[0],
			"Country": ogrp.Country[0],
			"Products": (ogrp groupBy $.ProductID mapObject(pgrp, pgrp_key) ->(
				"Product": {
					"ProductID": pgrp_key,
					"UnitPrice": pgrp.UnitPrice[0],
					"Quantity": sum(pgrp.Quantity),
					"Variants": (pgrp map(vnt, vnt_idx) -> {
						"Variant": {
							"Color": vnt.Color,
							"Quantity": vnt.Quantity
						}
					}).*Variant
				}
			)).*Product orderBy($.ProductID)
		}
	)).*Order orderBy($.OrderID)
}


Flat Json to Nested Xml

To transform from flat json to nested xml, can reused above steps until Step 4, change output to ‘application/xml’, and add orderBy.

%dw 2.0
output application/xml
---
{
	"Orders": (payload groupBy $.OrderID mapObject(ogrp, ogrp_key) ->(
		"Order": {
			"OrderID": ogrp_key,
			"Customer": ogrp.Customer[0],
			"Country": ogrp.Country[0],
			"Products": (ogrp groupBy $.ProductID mapObject(pgrp, pgrp_key) ->(
				"Product": {
					"ProductID": pgrp_key,
					"UnitPrice": pgrp.UnitPrice[0],
					"Quantity": sum(pgrp.Quantity),
					"Variants": (pgrp map(vnt, vnt_idx) -> {
						"Variant": {
							"Color": vnt.Color,
							"Quantity": vnt.Quantity
						}
					})
				}
			)) orderBy($.ProductID)
		}
	)) orderBy($.OrderID)
}

Output of nested Xml:

<?xml version='1.0' encoding='windows-1252'?>
<Orders>
  <Order>
    <OrderID>2501</OrderID>
    <Customer>Alex</Customer>
    <Country>USA</Country>
    <Products>
      <Product>
        <ProductID>P05</ProductID>
        <UnitPrice>50</UnitPrice>
        <Quantity>1</Quantity>
        <Variants>
          <Variant>
            <Color>Blue</Color>
            <Quantity>1</Quantity>
          </Variant>
        </Variants>
      </Product>
    </Products>
  </Order>
  <Order>
    <OrderID>2502</OrderID>
    <Customer>Bob</Customer>
    <Country>UK</Country>
    <Products>
      <Product>
        <ProductID>P05</ProductID>
        <UnitPrice>50</UnitPrice>
        <Quantity>5</Quantity>
        <Variants>
          <Variant>
            <Color>Blue</Color>
            <Quantity>2</Quantity>
          </Variant>
        </Variants>
        <Variants>
          <Variant>
            <Color>Red</Color>
            <Quantity>3</Quantity>
          </Variant>
        </Variants>
      </Product>
      <Product>
        <ProductID>P06</ProductID>
        <UnitPrice>80</UnitPrice>
        <Quantity>1</Quantity>
        <Variants>
          <Variant>
            <Color>Yellow</Color>
            <Quantity>1</Quantity>
          </Variant>
        </Variants>
      </Product>
    </Products>
  </Order>
  <Order>
    <OrderID>2503</OrderID>
    <Customer>Peter</Customer>
    <Country>UK</Country>
    <Products>
      <Product>
        <ProductID>P07</ProductID>
        <UnitPrice>120</UnitPrice>
        <Quantity>10</Quantity>
        <Variants>
          <Variant>
            <Color>Green</Color>
            <Quantity>4</Quantity>
          </Variant>
        </Variants>
        <Variants>
          <Variant>
            <Color>Yellow</Color>
            <Quantity>6</Quantity>
          </Variant>
        </Variants>
      </Product>
    </Products>
  </Order>
</Orders>

🙂

DataWeave – Transform Flat Structure to Nested using groupBy, map, mapObject and orderBy
Tagged on:                 

8 thoughts on “DataWeave – Transform Flat Structure to Nested using groupBy, map, mapObject and orderBy”

  • Rick Cromer
    March 12, 2020 at 1:41 am
    Permalink

    This was exactly what I needed, thanks for the post

    Reply
  • Paramita Ghosh
    March 16, 2020 at 10:20 pm
    Permalink

    Hello, I am trying with similar payload, but its throwing an error in Step 2

    Reply
    • Paramita Ghosh
      March 16, 2020 at 10:20 pm
      Permalink

      Invalid input it says

      Reply
    • Yee LoonPost author
      May 19, 2020 at 10:44 pm
      Permalink

      i was using mule4, if copy exactly in mule4 should work, since i copy paste out from my output.

      Reply
  • Sarabh
    January 26, 2021 at 12:50 am
    Permalink

    Perfect, this is very helpful. Thank you.

    Reply
  • Ling
    June 9, 2023 at 4:53 pm
    Permalink

    Thank you so much for sharing your knowledge! This page has already helped me solve DW challenges a couple of times!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *