Convert Relational or Hierarchical Data to Nested Struct Data
You can convert relational or hierarchical data in one or more columns in two transformations to nested struct data. Use the Create Nested Complex Port wizard in the Developer tool to perform the conversion.
The wizard converts relational data from two tables to struct data with a nested data type definition.
For example, a relational table contains employee bank account details. Another table contains the bank details. You create a mapping to convert data in the two tables into a hierarchical format that the payment system can process. Select the ports in the two transformations and use the Create Nested Complex Port wizard. The wizard generates struct data that contains an array element for each bank. The array contains a struct element for the employee bank account details.
- Relational input
- The Emp_Details table contains the following columns: employee ID, name, address, bank id, and bank account number.
- The Bank_Details table contains the following columns: bank id, bank name, SWIFT code.
- Struct output
banks{
bank_swift:integer
[bank_name{account_number:integer,employee_id:integer,name:string,address:string]
}
The wizard performs the following tasks:
- •Adds a Joiner transformation to join source data from the parent and child tables based on the join keys.
The transformation that contains ports for the parent complex data type definition is the parent transformation, and the other transformation is the child transformation.
- •Creates a child complex data type definition and adds an Expression transformation that contains the child struct port.
The expression in the struct port generates a struct based on the child complex data type definition.
- •Adds an Aggregator transformation that contains an array port.
The expression in the array port generates an array with the child struct as its element, and groups the struct values based on the group by port.
- •Creates a parent complex data type definition and adds an Expression transformation that contains the nested complex port.
The expression in the nested complex port generates a struct with an array of structs as one of its elements.
Creating A Nested Complex Port
Use the Create Nested Complex Port wizard to convert data that passes through one or more ports in two transformations to a struct data that references a nested data type definition. You specify the transformations from which the wizard creates the parent and child complex data type definitions.
1. Open the mapping or mapplet in which you want to create a nested complex port.
2. Press Ctrl and select the ports in the two transformations that you want to convert as elements of the nested complex port.
3. Right-click the selected ports, and select Hierarchical Conversions > Create Nested Complex Port.
The Create Nested Complex Port wizard appears.
4. Choose the transformation that contains ports for the parent complex data type definition and select the join keys to join the tables.
5. Click Next.
The wizard page to create child complex data type definition appears.
6. Optionally, change the name of the child complex data type definition and make any changes to the elements.
7. Click Next.
The wizard page to select group by port from the parent transformation appears.
8. Click Choose.
The Ports dialog box appears.
9. Select a group by port from the parent transformation and click OK.
The selected port appears on the wizard page.
10. Click Next.
The final wizard page to create parent complex data type definition and nested complex port appears.
11. Optionally, change the name of the parent complex data type definition and make any changes to the elements.
You cannot delete the port that you selected as the group by port.
12. Click Finish.
You can see the following changes in the mapping:
- •The mapping contains a new Joiner transformation Join_<transformation1>_<transformation1> that joins the two tables.
- •The mapping contains a new Expression transformation Create_ChildStruct with a struct output port and a dynamic port with ports from the upstream transformation.
- •The type definition library contains the new child complex data type definition. The struct output port references the child complex data type definition.
The output port contains an expression with the STRUCT_AS function:
STRUCT_AS(:Type.Type_Definition_Library.<child_typedef>,<comma_delimited_child_elements>)
- •The mapping contains a new Aggregator transformation Agg_ArrayOfStruct with the group by port to group the child structs into an array.
- •The mapping contains a new Expression transformation Final_NestedComplexPort with a struct output port and a dynamic port with ports from the upstream transformation.
- •The type definition library contains the new parent complex data type definition, which is a nested data type definition. The struct output port references the parent complex data type definition.
The output port contains an expression with the STRUCT_AS function:
STRUCT_AS(:Type.Type_Definition_Library.<parent_typedef>,<comma_delimited_struct_elements>)
