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
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.