Overview

It is possible to use a 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. 

When the Etlworks Integrator reads a nested data object (for example, JSON or XML), it parses it and represents the fields in a tree-like structure.

The initial mapping is created by clicking the Create Mapping button.

UseMake this field a child of the one aboveandMove child field up one levelto control the level of nestedness for a specific field. You can also drag and drop fields up and down.

The mapping editor supports nested data objects only for the source. 

Use Cases

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

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 Integrator 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 Integrator 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.