Advanced Operations - Flatten Operation
In the previous section, you have created a base view on top of a SOAP web service data source. This web service returns a hierarchichal structure that includes a return element of type array.
To represent these hierarchical structures, Denodo supports two different complex data types:
- Registers or Records: a register or record represents an element that is formed by several subelements or subfields. Each one of the subelements in a record will have their own name, data type and value.
- Arrays: an array represents multi-valued data that is presented as a list of items. An element of the type array can be thought of as a sub-view included in a main view. An array type always has an associated record type that acts like the schema of the sub-view and defines the columns that make up each item in the list.
Although Denodo is capable of working with these kinds of complex data types, these are not standard relational data types and in most of the scenarios it will be more useful and easier to simplify these hierarchies.
To simplify array columns, Denodo has the special Flatten operation.
Let's see how to create a new view that flattens the get_bill_by_customer_id base view:
- Click on the get_bill_by_customer_id view and select New > Flatten.
- In the Flatten View wizard, we will select the element that needs to be flattened. In this case, the only array that we
have in the base view is: return.
- Go to the Output tab, rename the view to billing_information and also rename the field customer_id to
return_customer_idthen click on
.
You will see that the array has dissapeared from the schema of the new view and the fields that were inside the array in the base view are now regular fields.
The new derived view has the same query capabilites as get_bill_by_customer_id but if you query the new view using the same customer_id = 'C003' condition, you will get the fields that were part of the array in the base view schema. This customer has two pending bills so there are two rows in the result set of the query, one for each bill.
