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
			}
		})
	})
}
DataWeave – Transform Nested Structure to Flat using map and flatten
Tagged on:             

4 thoughts on “DataWeave – Transform Nested Structure to Flat using map and flatten”

  • Pingback:DataWeave – Transform Flat Structure to Nested using groupBy, map, mapObject and orderBy – INTEGRATIONLEARN.COM

  • mule
    February 7, 2020 at 10:31 pm
    Permalink

    Please advise how to learn data weave from scratch

    Reply
  • raghu
    June 25, 2021 at 10:55 am
    Permalink

    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
    ]
    }
    ]
    },

    Reply
    • Yee LoonPost author
      June 26, 2021 at 12:18 am
      Permalink

      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
      }
      ]

      Reply

Leave a Reply

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