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
| Control | What it does |
|---|---|
| Search | Filters source rows by name or path (case-insensitive substring match). |
| Add source field | Appends a new root-level source field. Use this when the parsed source structure is incomplete or you want to extend it. |
| Structure editor | Opens an XML-like editor for the entire source structure. Useful for hand-defining or overriding the auto-parsed schema. |
| Refresh source fields | Resets all source-structure overrides to the original metadata. Visible only when source metadata is available. |
Per-row controls
| Control | What it does |
|---|---|
| Outdent | Moves the field up one level in the hierarchy. Disabled at level 0. |
| Indent | Nests the field under its predecessor. Disabled at level 9 (the maximum depth). |
| Add child field | Adds a new field as a direct child of this row. |
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
| Control | What it does |
|---|---|
| Search | Filters destination rows by destination or source name (case-insensitive substring match). |
| Refresh destination fields | Reloads destination metadata from the connection. Visible only when destination metadata is available. |
Per-row controls
| Control | What it does |
|---|---|
| Drag handle | Grab to reorder this row among its siblings. See Drag and drop below. |
| Destination field | Editable dropdown for the destination field name. Auto-populated from destination metadata when available; you can also type a name directly. |
| Binding pill | Shows where this destination's value comes from (Direct, Expression, or Constant). Click to open the binding editor. See Binding modes below. |
| Move up | Moves the row and its children up one position. Disabled when there is no previous sibling. |
| Move down | Moves the row and its children down one position. Disabled when there is no next sibling. |
| Outdent | Ungroups the row from its parent. Disabled at level 0. |
| Indent | Nests the row under the previous row. Disabled at level 9. |
| Add child field | Adds a new field one level deeper, as a child of this row. |
| Add sibling field | Adds a new field at the same level, as the next sibling. |
| Edit formula | Opens the code editor for a JavaScript, Python, or SQL formula to compute the field value. Disabled when the destination does not support calculated fields. |
| Edit data type | Opens the data-type picker (SQL type selector with nullable flag). |
| Description | Opens an inline editor to document this field mapping. |
| Unlink | Removes the source binding and any formula from this destination, leaving it empty. Disabled if the row has no binding. |
| Delete | Deletes this destination field and its entire subtree. |
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.
| Mode | What it does |
|---|---|
| Direct | Field-to-field. Pick a source field from the dropdown; its value flows into the destination. |
| Expression | Inline formula. Use JavaScript, Python, or SQL (where the destination supports it) to compute the destination value from source fields, constants, and lookups. |
| Constant | Hard-coded value. The destination receives the same value for every record. |
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
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 contextSet 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 nameAvailable objects
The following objects can be referenced by name from JavaScript or Python code:
| Object name | Class name / JavaDoc | Description |
|---|---|---|
| dataSet | com.toolsverse.etl.common.DataSet | current destination dataSet for that specific field |
| currentRow | com.toolsverse.etl.common.DataSetRecord | current record in the current source dataSet for that specific field |
| etlConfig | com.toolsverse.etl.core.config.EtlConfig | ETL config |
| scenario | com.toolsverse.etl.core.engine.Scenario | ETL scenario |
| sourceDataSet | com.toolsverse.etl.common.DataSet | the source dataSet for that specific field |
| actualSourceDataSet | com.toolsverse.etl.common.DataSet | the original source dataSet |
| fieldValue | the value of the field in the source dataSet | |
| row | current 0-based row number |
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.