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 above
andMove child field up one level
to 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 transfromation
to 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 transfromation
to check the output.
Comments
0 comments
Please sign in to leave a comment.