USER MANUALS

FLATTEN View (Flattening Data Structures)

The data stored in relational databases usually is composed of values of simple types: int, text, long… However, in other types of sources, the data is usually structured in compound values. For example, no-sql databases, in JSON and XML files, in web services, etc., the data has a more complex structure.

The extended relational model of Virtual DataPort allows you to work with compound data. To do this, it provides the data types array and register.

You can think of a value of type array as a view. That is because an array is always formed by one “register” (that you can think of as a row on a table) and each “register” is formed by one or more fields. The type of the fields of a register can be simple (text, int, long…) or compound (register and array). All the registers inside an array have the same structure.

Very often, you need to “flatten” the data coming from sources that return compound values because:

  1. It is easier to combine flattened data with data coming from relational databases.

  2. Consume data from tools that do not support compound types.

To “flatten” data, use the operation FLATTEN:

Syntax of the FLATTEN operation
<flatten view> ::=
    FLATTEN <view identifier> AS <alias:identifier> [( <alias> [. <register field> ]* . <array field> )]+

  • <view identifier>: reference to a view. E.g. customer or customer360.asset.

  • alias is an identifier that identifies the view. You can use whatever identifier you want. For example, v.

  • The next parameters form the “path” to the array that you want to flatten. If the array is inside a register, you need to provide the name of the register, followed by ., followed by the name of the array.

  • You can specify one or several arrays to flatten.

FLATTEN returns the fields of the source view - except the arrays that you flatten - and all the fields of the registers of the array fields you are flattening.

Example

For example, if you have a view called average_revenue, which has a single field result of type array. The register inside this array has two fields: taxid and revenue.

The query below will flatten the data of the view and return the fields “taxid” and “revenue”.

SELECT TAXID, REVENUE
FROM FLATTEN average_revenue AS V (v.result)
Add feedback