Subtables

Subtables

When presented with nested data, Panoply attempts to transform it into a flat relational model in order to be compatible with most analysis tools, like BI suites, which are unable to query nested data effectively.

By default, Panoply transforms nested data into a set of many-to-many or one-to-many relationship tables. This is the classic solution for nested structures in relational databases, where the nested model is transformed into several flat tables that can be joined together.

For example, consider the following record in the users destination table:

{id: 30, friends: [ 'one', 'two' ] }

This record would be transformed to three new records: one record in the users table:

{id: 30}

and two records in the users_friends table:

{users_id: 30, value: 'one'}
{users_id: 30, value: 'two'}

Similarly, this transformation can also be applied to nested objects or nested lists of objects. Panoply extracts every nested value and moves it to its own table, suffixed by the attribute name of the nested data, and creates the relevant foreign key to allow queries to easily join the two tables together. In this example, the foreign key is the users_id.