Overview

Nested mapping is the unified mechanism for any source-to-destination transformation where the source, the destination, or both have hierarchical structure. As of version 9.5.8 the same engine handles all three transform shapes:

• Flat → Nested — build a nested document from flat records.
• Nested → Flat — flatten a nested document into rows.
• Nested → Nested — restructure a nested document into a different nested shape (including denormalization).

Use the mapping editor’s Flat / Nested view-mode toggle to switch into nested mode. These are some of the most common use cases:

• Transform nested documents from one format (for example JSON) to another (for example XML).
• Change the structure of the nested document. 
• Transform nested document into a flat array.
• Transform the flat array into a nested document.
• Denormalize nested document by creating an array of elements from the deepest level and adding elements from the parent level. Example: <orders><order><items><item> to <items><item><order>.

Note: The Mapping editor should be able to handle most of the scenarios. Still, in some extreme cases, when the destination document is so complex that using the drag-and-drop editor is not the best option, you can create nested documents with JavaScript. Read more.

Difference between nested and flat mapping

Both modes are available from the same mapping editor — toggle Flat or Nested at the top.

• When to use Flat. Both source and destination are flat. Flat mapping is significantly faster on large volumes because it runs at load time and supports streaming.
• When to use Nested. Either side has hierarchical structure, or you need explicit control over field order, denormalization, or hierarchy reshaping. Nested mapping is a transformation that runs on the source document right after extraction and before loading.
• Streaming. Flat mapping streams by default. Nested mapping disables streaming — when the destination is a database, enable Force Streaming to keep memory pressure low.
• Default mapping. Flat mapping uses the default mapping concept (unmapped fields are carried through). Nested mapping requires every destination field to be defined explicitly.
• Field order. Nested mapping enforces the order defined in the editor. Flat mapping appends new fields at the end of the record.

Automatic flattening of unused intermediate levels

When the destination only needs leaf values from a deeply nested source, the engine does not emit unused container levels as intermediate records. For example, if you map orders.items.price to a flat destination column and ignore the orders and items container fields, the transformation flattens those containers in one pass instead of producing intermediate rows for each level.

This is automatic — there is nothing to configure. The behavior reduces memory pressure and improves throughput on flows where the destination is shallower than the source.

Create nested mapping

When Etlworks reads a nested data object (JSON, XML, etc.), it parses it and displays the fields as an expandable tree on the source side of the editor.

Switch the editor to Nested view mode using the Flat / Nested toggle at the top. Nested mode renders both the source and destination as trees with hierarchy controls.

You can either:

• Use the Create Mapping button to auto-populate the destination mirroring the source structure (the With Nested Destination option), or
• Add fields manually with + and shape the destination using the indent/outdent controls (Make this field a child of the one above and Move child field up one level) on the destination row.

Source panel controls

The source panel is on the left. It shows source fields as an expandable tree.

Panel header

Per-row controls

Source rows are also draggable. See Drag and drop below.

Destination panel controls

The destination panel is on the right. It shows destination fields as an expandable tree with inline editing for binding, data type, formula, and description.

Panel header

Per-row controls

Binding modes

The binding pill on each destination row controls where the field's value comes from. Click it to open the binding editor and pick a mode.

The pill also signals state at a glance:

• Link icon with the source path — Direct binding.
• Code icon with the label "derived" — Expression or Constant binding.
• Empty circle with the label "empty" — no binding yet; the row is skipped on save.
• Empty circle with the label "add source first" — the source structure has not been defined.

Drag and drop

The editor supports three drag-and-drop interactions:

• Source row → destination row — drag a source field onto a destination row to create or replace its binding. The new binding is set to Direct.
• Source row → empty destination area — drag a source field onto the empty destination panel (when no destination fields exist yet) to create a new destination field with the same name and a Direct binding to that source.
• Destination drag handle → another destination row — reorder rows within their current hierarchy. Drops succeed only when the source and target are siblings (same parent, same level). The drop position (before or after the target) is determined by the mouse Y-position over the target row.

Keyboard shortcuts available when the editor has focus:

• Ctrl/Cmd + K — focus the search box
• Ctrl/Cmd + D — duplicate the selected mapping
• Ctrl/Cmd + / — open the destination structure editor
• Delete or Backspace — clear the binding on the selected row, or delete the row if it has no binding

Test nested mapping

Every source-to-destination transformation in Etlworks can be tested directly from the configuration screen, including nested mappings. Click the Test Transformation button at the bottom of the mapping screen to run a test pass and inspect the generated document in the destination format.

See Test transformation for the full how-to.

Use cases

Transform nested documents from one format to another

Data exchange formats that support nested documents

• JSON
• XML
• Avro
• Parquet
• HL7 2.x
• HL7 FHIR
• EDI: X12, EDIFACT, NCPDP and VDA

Example

In this example, we transform the source JSON document into destination XML. We add a new field itemsCount, which calculates the number of items in each order. We also configure itemIdas an XML attribute and itemsCount as a node with CDATA section.

Source JSON document:

{
    "orders": [
       {
          "orderId": "1",
          "orderDate": "01/01/2024",
          "items": [
             {
                "itemId": "1-1",
                 "name": "item1-1",
                 "price": 123,
                 "vendor": {
                     "name": "vendor1",
                     "address": "1 Main st"
                 }
             }]
       }]
}

Expected destination XML:

<?xml version='1.0' encoding='UTF-8'?>
<orders>
    <order>
       <orderId>1</orderId>
       <orderDate>01/01/2024</orderDate>
       <items>
         <item id="1-1">
            <name>item1-1</name>
            <price>123</price>
            <vendor>
               <name>vendor1</name>
               <address>1 Main st</address>
            </vendor>
         </item>
         <itemsCount><![CDATA[1]]></itemsCount>
    </order>
</orders>

Source-to-destination transformation:

Mapping:

Parent|row in XML

Notice mapping for orders and items columns in the screenshot above:

• orders -> orders|order
• items-> items|item

XML, unlike JSON, typically requires an enclosing parent tag for an array of nodes. By default, the transformation will use the same name for the parent tag in the array and the row node:

<orders>
    <orders>
    <orders> 
<orders>

To give a unique name for the row tag, set the destination in mapping to parent|row, for example, orders|order. It will generate the following XML:

<orders>
    <order>
    <order> 
<orders>

Data type modifiers

The data type modifications are used to change how the destination document is rendered. 

To access the modifiers, click the data type icon in the mapping row.

Data type modifiers when the destination is XML

The following modifies are available when the destination is XML:

• XML attribute: This field is an XML attribute. Example: <tag attr="value">
• XML CDATA section: This field is an XML CDATA section. Example: <tag><![CDATA[text]]></tag>
• XML tag with value and attributes: This field is an XML tag with value and attributes. Example: <tag ContentID="11-22-33-44" anotherattr="attr value">some value</tag>. Enable this modifier for the "value" field. Enable XML attribute for "attribute" fields.
• XML hide repeating element root tag: This repeating field root tag will not be added to the nested XML. Example: <row>value</row> instead of <owner><row>value</row></owner>
• XML hide row tag: This repeating field wrapping <row> tag will not be added to the nested XML. Example: <images><image>url</image></images> instead of <images><row><image>url</image></row></images>

Data type modifiers when the destination is JSON

The following modifies are available when the destination is JSON:

• This field is a JSON array of strings: if you need to create a JSON array of strings, for example,  "node_name":["value1","value2,"value3"] set the field data type to ARRAY:

• JSON array: When creating JSON documents, the transformation "decides" whether to create JSON arrays based on the number of elements in the nested dataset. Arrays are used if the number of rows is more than 1. Enable this modifier to enforce the creation of arrays regardless of the number of elements in the nested dataset. 
• JSON array values are not strings: This field is a JSON array with literal values (not strings). Example: [1,2,3] instead of ["1","2","3"]
• Change the data type of the value: to change the data type of the value in"node": value select the Data Type from the dropdown. 

Change the structure of the nested document

You can change the structure of the destination document by adding, hiding, renaming, and moving (up and down) the fields. Use buttons Make this field a child of the one above and Move child field up one level on the right side to control the level of nestedness for a specific field in the destination document. 

Source JSON document

{
    "orders": [
       {
          "orderId": "1",
          "orderDate": "01/01/2024",
          "items": [
             {
                "itemId": "1-1",
                 "name": "item1-1",
                 "price": 123,
                 "vendor": {
                     "name": "vendor1",
                     "address": "1 Main st"
                 }
             }]
       }
}

Expected destination JSON document

{
    "metadata": {
      "timestamp": "01/01/2024 00:00:00",
       "count": 1
    },
    "data": [
       {
          "orderId": "1",
          "orderDate": "01/01/2024",
          "items": [
             {
                "itemId": "1-1",
                 "name": "item1-1",
                 "price": 123          
             }]
       }
}

Source-to-destination transformation

Mapping

Transform nested document into a flat array

You can transform the nested source document into a flat array by reducing the nestedness of the fields on the right (destination) and hiding the parent fields.

Source JSON document

{
    "orders": [
       {
          "orderId": "1",
          "orderDate": "01/01/2024",
          "items": [
             {
                "itemId": "1-1",
                 "name": "item1-1",
                 "price": 123,
                 "vendor": {
                     "name": "vendor1",
                     "address": "1 Main st"
                 }
             }]
       }
}

Expected destination JSON document

[
   { 
     "orderId": "1",
     "orderDate": "01/01/2024",
     "items": [
        {
          "itemId": "1-1",
           "name": "item1-1",
           "price": "123"
   }
] 

Source-to-destination transformation

Mapping

Transform the flat array into a nested document

You can transform the flat source document into a nested one by increasing the nestedness of the fields on the right (destination) and adding the parent fields.

Source JSON document

[
   {
      "orderId": "1",
      "orderDate": "01/01/2024",
      "items": [
         {
           "itemId": "1-1",
           "name": "item1-1",
           "price": 123,
           "vendor": {
              "name": "vendor1",
              "address": "1 Main st"
           }
         }]
   }
} 

Expected destination JSON document

{
   "data": [
     {
       "orderId": "1",
       "orderDate": "01/01/2024",
       "items": [
         {
           "itemId": "1-1",
           "name": "item1-1",
           "price": "123"
         }]
     }]
}

Source-to-destination transformation

Mapping

Denormalize nested document

You can flatten and denormalize the nested source document by extracting data from the deepest level and combining it with the elements of the parent node.

In this example, a source JSON document includes an array of orders, each having its own array of items. The goal is to create a destination JSON document with an array of items where each item has some of the attributes of the parent order.

It is accomplished by setting the level of nestedness of the parent field (order) to the same as that of the deepest field in the nested document (item) and hiding the top-level parent fields (orders and items).

Source JSON document

{
    "orders": [
      {
         "orderId": "1",
         "orderDate": "01/01/2024",
         "items": [
           {
              "itemId": "1-1",
              "name": "item1-1",
              "price": 123
          },
          {
              "itemId": "1-2",
              "name": "item1-2",
              "price": 234
          }]
     },
     {
          "orderId": "2",
          "orderDate": "01/01/2024",
          "items": [
             {
               "itemId": "2-1",
               "name": "item2-1",
               "price": 123
             },
             {
               "itemId": "2-2",
               "name": "item2-2",
               "price": 234,
             }]
     }]
}

Expected destination JSON document

[
   {
      "orderId": "1",
      "orderDate": "01/01/2024",
      "id": "1-1",
      "name": "item1-1",
      "price": "123"
   },
   {
      "orderId": "1",
      "orderDate": "01/01/2024",
      "id": "1-2",
      "name": "item1-2",
      "price": "234"
   },
   {
      "orderId": "1",
      "orderDate": "01/01/2024",
      "id": "1-3",
      "name": "item1-3",
      "price": "456"
   },
   {
      "orderId": "2",
      "orderDate": "01/01/2024",
      "id": "2-1",
      "name": "item2-1",
      "price": "123"
   },
   {
      "orderId": "2",
      "orderDate": "01/01/2024",
      "id": "2-2",
      "name": "item2-2",
      "price": "234"
    }
]

Source-to-destination transformation

Mapping

Calculate field value

By default, a DESTINATION field gets its value from its corresponding SOURCE field. The value of any field can be changed or calculated using JavaScript or Python.

Select or enter the DESTINATION field, click the pen icon to the right, and enter a program in the scripting language to calculate the field's value.

Get a field's value

dataSet.getFieldValue(currentRow, "field name") // any field by name

or

fieldValue // current field in the context

Set a field's value

value = calculated_field_value // current field in the context

or

dataSet.setFieldValue(currentRow, "field name", calculated_field_value) // any field by name

Available objects

The following objects can be referenced by name from JavaScript or Python code:

Streaming is disabled when nested mapping is used 

Etlworks can read and write nested documents in JSON, XML, JSON, Parquet, Avro, EDI, and other supported formats.

By default, it attempts to use streaming to load data into the destination. Streaming is faster and has a very low memory footprint. Unfortunately, streaming cannot be used when reading and writing complex nested data objects, so Etlworks automatically disables it.

If the source is a nested dataset and the destination is a database, it is highly recommended to enable the Force Streaming option. When streaming is forced, the system automatically creates a stream-able view from a non-stream-able source. It still keeps all the records in memory but does not keep all the SQL statements in memory, and it is possible to use bind variables with a stream-able view.