Expand JSON

JSON stands for "JavaScript Object Notation" and is a widely-used data format. If you're using APIs, you've likely come across JSON and may want to expand it to access the data nested inside it.

If you're trying to troubleshoot a JSON expansion issue, reference this community post.

Input/output

Our table below shows the result of a GET call made to a free API: https://api.punkapi.com/v2/beers. This data has multiple JSON columns within a table of 25 rows and 21 columns.

Below is the JSON object from one cell in the 'method' column, formatted for easier reading. This cell is in the row where the value in column 'name' is equal to 'Berliner Weisse With Yuzu - B-Sides'.

Once we use the Expand JSON step, our output data becomes 28 rows and 30 columns. We've gained 3 rows and 9 columns after expanding the "method mash_temp" dataset with all non-datasets also expanded.

If we filter for beers with the name 'Berliner Weisse With Yuzu - B-Sides' and focus on the newly-expanded columns, we see that there's one row per element in the array 'method mash_temp temp value' for a total of 4 rows with 'Berliner Weisse With Yuzu - B-Sides'. If we look closely at the 6 additional columns that contain the newly-expanded data, we'll see the data nested within the JSON object above neatly arranged in a table format.

Default settings

By default, this step will try and convert as much JSON as it can, and in most cases we won't need to change settings. This step defaults to expanding the first JSON dataset it comes across.

By default, this step also sets to 'Expand all non-datasets'. That setting is nested under the 'Non-dataset Options' menu. This means that all the object keys will be expanded out to as many columns as it requires.

If you're familiar with JSON, you can think of this step's functionality like the following: datasets are JSON arrays that are expanded into new rows. Non-datasets are JSON objects that are expanded into new columns.

Custom settings

To customize the default settings, we'll first want to make sure the right dataset is selected to be expanded. We can only choose one dataset to expand or can select to expand no datasets. Datasets are expanded so that each entry is put into a new row. If our data has no dataset, then we'll see 'N/A' preselected in the first option for 'Expand the' menu.

Next, if we don't need all non-datasets expanded into columns, we can open the 'Non-dataset Options' drawer and switch it from 'Expand all non-datasets' to 'Expand these non-datasets'.

If we select 'Expand these non-datasets', we'll see an option to specify exactly which non-datasets we should expand into new columns. We can select as many as we require. Whichever non-datasets we choose will be expanded into new columns, and if there are more non-datasets contained within, those will be expanded as well.

‍

Helpful Tips

• If you have multiple rows of JSON and the data is not fully expanding as expecting, review the JSON body in the top row of your data. Parabola will look at the JSON in the first row and use that as a template for expanding JSON in subsequent rows. If there is an invalid or missing key in the top row, our expand step JSON step will omit that field in subsequent expanded rows. For additional information on troubleshooting this issue, please reference this community post.