🎬 Foreign Keys & Relationships (Nested Data)

🎬 Foreign Keys & Relationships (Nested Data)

When ingesting data, Panoply automatically parses this data and places it into a tabular structure that fits the architecture of a columnar data warehouse. By default, Panoply will create separate tables for nested objects, effectively creating a parent table to child table relationship.

For example, let’s look at this sample JSON file that contains data on superheroes. We see high-level data at the squad level, but we also see nested objects such as individual members of the squad. When I ingest the heroes file into Panoply a main heroes table is created. But subtables are created as well, which contain data from the nested objects. Each subtable would be able to refer back to higher-level tables by leveraging a foreign key, which is the name of the higher-level table followed by a suffix of _id.

Let’s take a look at the heroes_members table, for example. When I click into this table I can see that the foreign key is heroes_id. I can leverage this field to join back to the heroes table and facilitate my analysis.

Let’s say I want to join the heroes table together with the heroes_members table to get the name of each individual member and the name of the squad in the same table. To do so I can write a simple query which joins the heroes_members table to the heroes table, setting the foreign key—which is the heroes_id value in the members table—equal to the id field in the heroes table. After running this query I see that I get the results I want.

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