Overview

Use drag&drop mapping editor to create source-to-destination transformations where the source is a nested document, and the destination is a flat file or a database table.  These are some of the most common use cases:

• The source is an array with nested elements
• The source is a nested document, but you only need data from the specific dimension

The mapping editor supports nested data objects for the source and the destination. Read how to create the nested mapping.  Nested mapping can also create flat documents, so consider it before switching to flat mapping. 

Create mapping

When Etlworks reads a nested data object (JSON, XML, etc.), it parses it and displays the fields in a tree-like structure (see the screenshot below).

The initial mapping is created by clicking the Create Mapping button. For flat mapping, select Create instead of Create with nested destination. If you made a flat mapping, you can later enforce the nested mapping by selecting the Enforce nested mapping checkbox.

Use buttons Make this field a child of the one above and Move child field up one level on the left side to control the level of nestedness for a specific field in the source document. You can also move fields up and down.

Click + button to add new fields to the mapping. Read about other controls.

Use Cases

There are two distinct use cases when using the mapping editor to transform the nested source document into a flat dataset makes the most sense. 

The source is an array with nested elements

Nested source

[
  {
    "firstName":"Joe",
    "lastName":"Doe",
    "ids":[
      {
        "type":"ssn",
        "id":"101111222"
      }
    ],
    "age":18,
    "streetAddress":"100 Internet Dr",
    "city":"Pittsburgh",
    "state":"PA",
    "postalCode":"12345",
    "phoneNumbers":[
      {
        "type":"Mobile",
        "phoneNumber":"111-111-1111"
      },
      {
        "type":"Home",
        "phoneNumber":"222-222-2222"
      }
    ]
  }
]

Expected flat destination

Process

Step 1. Create an ETL flow where the source is a file.

Step 2. Create default mapping by clickingCreate Mapping. Etlworks reads the nested data source object and creates the Mapping automatically. 

If the source object is not available when you create a Flow, you can design it yourself by using a combination of these buttons:

• Add field mapping
• Mark this field as a child
• Move child field one level up

Step 3. Map fields in the source (FROM) to the fields in the destination (TO). Exclude the parent field but keep the child fields. In the example above, the parent fields are ids and phoneNumbers. Rename the destination fields if needed.

Step 4. Click Test transfromationto check the output. 

The source is a nested document, but you only need data from the specific dimension 

Nested source document

{
"metadata": {
    "total": 100,
     "page": 1
},
"data": {
    "records": 100,
    "items": [{
        "firstName": "Joe",
        "lastName": "Doe"
      }, {
         "firstName": "Jane",
          "lastName": "Doe"
      }]
    }
}

Expected destination

Mapping

Step 1. Create an ETL flow where the source is a file.

Step 2. Create default mapping by clickingCreate Mapping. Etlworks reads the nested data source object and creates the Mapping automatically. 

If the source object is not available when you create a Flow, you can design it yourself by using a combination of these buttons:

 

• Add field mapping
• Mark this field as a child
• Move child field one level up

Step 3. Map fields in the source (FROM) to the fields in the destination (TO). Exclude all fields except the fields in the actual dimension to extract data from. Rename destination fields if needed. 

You can only keep fields from one dimension. Enabling fields which belong to multiple dimensions will invalidate the mapping.

Step 4. Click Test transfromationto check the output.