Expand JSON Explained: Troubleshooting Common Issues

Thought I’d share some of the expand JSON advice that we’ve previously shared across different threads and emails!

To review the basics of the Expand JSON step, start in our How To docs. This post is more focused on some of the tricky Expand JSON troubleshooting.

When you pull from an API, using the Expand JSON step is often what comes next.

In most cases, you should start by unpacking the JSON in the Pull from/ Enrich with an API step in the “Response JSON” section. Once you’ve unpacked all of the nested objects, it’s possible that there are still additional JSON objects that need to be unpacked using Expand JSON steps.

We hear about two main issues when expanding JSON:

  1. “None of my JSON is expanding”
  2. “Only some of my JSON is expanding/ columns are missing”

Let’s start by understanding how this step works:

Parabola looks for a ‘pattern’ in the top row of JSON. We take the top row’s JSON object and assume, “this is the format that all rows will follow, so we will continue to use this pattern to unpack subsequent rows.” This is problematic when the top row is blank, or when the top row has an incomplete JSON body (most often caused by a null value).

“None of my JSON is expanding”

This is most likely caused by a blank value in the top row of JSON. If you notice this, you’ll want to keep your JSON un-expanded in the “Response JSON” section of your API step. From there, you can use a “Sort Rows” step in between the API step and the “Expand JSON” step, sorting blank rows to the bottom of the table and JSON objects to the top.

If you need to re-sort your data after expansion back to the original order, you can use an Insert row numbers step before Expand JSON, and then re-sort by row number after the data is expanded.

“Only some of my JSON is expanding”

This is most likely happening because your top row of JSON does not contain a complete JSON object. Again, make sure to leave your JSON unexpanded in the “Response JSON” section of your API step.

Consider this example:

Row 1:

{
	"name": "Adam",
	"friends": null
}

Row 2:

{
	"name": "Daniel",
	"friends": {
		"name": "Sachi",
		"company": "Parabola"
	}
}

If you tried to expand this JSON, you’d expect to have columns called friends.name and friends.company. Since Adam’s friends are null, the friends object will not be expanded for any records since there is no pattern in row 1 for the step to follow.

To resolve this issue, we have 2 possible approaches:

  1. Identify a record (pre-JSON-expansion) that has a complete JSON body, and find a way to sort this record to the top row. Most often, this is the solution.
  • If there’s no easy way to sort the data, another option is to isolate a record with a complete JSON object in it’s own filter branch, then use a “Stack Tables” step to re-combine the data, plugging in the step with the isolated record first to make it appear at the top of the Stacked table.

  1. If 1 is not an option for whatever reason, we need to mock out the desired pattern that the step should follow. In this case, we need to define the pattern for the friends object in place of the null. We can use a “Find and Replace” step to replace null with:
{
"name": "",
"company": ""
}

That will change the JSON in row 1 to:

{
	"name": "Adam",
	"friends": {
		"name": "",
		"company": ""
	}
}

This will place a blank value in the newly created friends.name and friends.company columns for row 1 and properly define the JSON pattern for subsequent rows.

Hopefully this covers any JSON issues that you run into! Please comment below with any other JSON issues you run into.

3 Likes