Nested Data

Nested Data

Nested data may appear in many data sources that produce nested arrays or objects, like document-stores (Mongo), APIs, and even relational databases like Postgres JSON fields. Additionally, some of Panoply’s data enhancement features may produce nested values for flat data. For example, a record that contains an IP field may be enhanced to include a nested object with the geo-location information for that IP address.

In order to be fully compatible with most popular analysis tools–like BI suite, and standard SQL queries–Panoply uses a strongly relational model that doesn’t allow these nested values to appear in the data. Therefore, Panoply supports several ways to transform these nested structures into a flat relational model:

  • Subtables (default)
  • Flattening
  • Skipping


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.


When flattening is enabled, Panoply flattens the nested structure onto the record that contains it. To do this, we prefix the nested attributes with the name of the outer attribute that contains them.

For example, the following record contains a nested location object:

{location: {city: 'San Francisco', state: 'CA', country: 'USA'}}

It would be transformed into this new, flat format:

{location_city: 'San Francisco', location_state: 'CA', location_country: 'USA'}

Each nested property in the location object is replaced by a new attribute on the containing record, where the attribute name is prefixed with location_.

Note that flattening is applicable for nested objects only, not for nested lists. For example, the following record cannot be flattened:

{locations: [ 'one', 'two', 'three' ]}

You can turn flattening on in the metadata section on the tables page. Simply select the field and scroll down to the Nested field and change it to Flatten on parent table.


Skipping means that the nested data will not be saved at all. The data will not be transformed into a subtable (one-to-many connection), and it will not be flattened into the main table.

You can skip a field with nested data on in the metadata section on the tables page. Simply select the field and scroll down to the Nested field and change it to Skip.

Next Steps

Getting started is easy! Get all your data in one place in minutes.
Try Free