FLATTEN View (Flattening Data Structures)¶
Virtual DataPort supports modeling hierarchical data types through the
use of the types
array (see section Management of Compound Values).
In Virtual DataPort, you may think of an
array element as a subview.
array always has a
register internally associated and each
subelement of the array belongs to this
register. Hence, the fields
of this register may be seen as the schema of the subview being modeled.
You usually need to create Flatten views when processing XML or Web service sources because they usually return compound fields. This section describes how to do this.
Imagine that we have a Web service with a
operation that does not have input parameters and returns an array of
objects with the monthly sales of all the clients of a company. Each
element of the array has two properties:
The base views created over this operation have one attribute of type
array, which contains
register-type elements and only one row
that contains all the data returned by the Web service.
In order to combine these data with data from other sources, we usually
have to apply the “flatten” operation over the result, in order to have
a view with two attributes (
revenue) and one tuple for
FROM clause, you can use the constructor
define queries over “flattened” views. The constructor
below) generates tuples from the compound
subfields of an array of a view.
<flatten view> ::= FLATTEN ( <view name:identifier>[.<register field>]*.<array field> ) | FLATTEN ( <view name:identifier> AS <alias> [, <alias>[.<register field>]*.<array field> AS <alias> ]* /, <alias>[.<register field>]*.<array field> )
As we can see in the figure above, there are two ways of using the
Specifying the name of an attribute of type
array. The output is a view which has the schema of the register contained in the array passed as parameter. The specified array can be inside one register (or even several nested registers), but it cannot be nested inside another array.
<flatten view> ::= FLATTEN ( <view name:identifier>[.<register field>]*.<array field> )
Specifying the name of a view and an alias. The output is the flattened representation of an array (even if it is nested inside another array) and the other fields of the view.
The syntax is specified by indicating an alias for the source view and the array element on which the FLATTEN operation has to be applied.
To apply to an array that is nested inside another, you have to add an alias to the parent array.
To specify which array you want to flatten, you have to indicate a path from the alias.
The resulting schema contains the fields of the source view (except the array on which the operation is carried out) and all the elements of the registers involved in the flattening operation.
<flatten view> ::= FLATTEN ( <view name:identifier> AS <alias> [, <alias>[.<register field>]*.<array field> AS <alias> ]* , <alias>[.<register field>]*.<array field> )
Imagine that we have the base view
that has a field of type array of registers called
register contains two fields:
REVENUE. The following
statement returns the “flattened” contents of
SELECT TAXID, REVENUE FROM FLATTEN (AVERAGE_REVENUE_ARRAY AS V, V.RETURN)