The JSON Explorer Transform allows you to pull out data from a JSON data structure. JSON stands for JavaScript Object Notation and is a standard data format supported by many different databases and APIs.
JSON is structured in pairs a field name, followed by a colon, and a corresponding value.
Here is a simple example:
"first_name":"John"
You can and usually will have multiple name/value pairs within a single JSON object. The complete JSON object is then wrapped within a pair of curly brackets {}
Here is an example of a basic JSON object:
{
"first_name":"John",
"last_name":"Smith"
}
Additionally, you can have JSON objects inside other JSON objects. These are called nested JSON.
Here is an example of a nested JSON object:
{
"first_name":"John",
"last_name":"Smith",
"address":{
"street_address":"2500 South Broadway St",
"city":"Cupertino",
"state":"California"
}
}
Using the JSON Explorer Transform
The JSON Explorer Transform allows you to create a new column from a name-value pair within a JSON object. Additionally, it allows you to drill into several layers of JSON objects to find the exact name-value pair that you wish to pull out into a column.
Let us look at an example of a JSON object for an ORDER:
For our purposes, we want to have the amount of every purchase as its own column, so we can create a metric for Average Order Values. In order to do this, we need to drill into two layers of the JSON object.
So navigate to the JSON explorer Transform in the Transform list.
Next, you will need to Name the new column you are creating in the New Column Name field. Then select the column with the JSON object you wish to parse in the Column dropdown. In this case, we are calling our new column Amount and the JSON object is in the order field.
In order to pull out the amount values, we need to drill into each layer of JSON objects. The amount value is associated with the corresponding amount field name, which is nested inside the price field name. In the Field field, we need to type in the name of the first JSON field name, in this case is price.
Then we need to select the + Add a child field to create a new Field field. In this second field, we can type in amount, which is our second JSON field name that corresponds to the amount values we want.
At this point, you can now select Run to pull out the amount values into their own column.
Considerations
Arrays
If your JSON object is an array of JSON objects, you will need to parse out each object in the array into its own row using the Array Expander transform before you can use the JSON Explorer transform. You can identify if JSON is in an array by seeing if the curly brackets are wrapped in square brackets.
For example: [{...}, {...}, {}]
Here is an example of an array of JSON objects:
"contacts": [
{
"first_name":"John",
"last_name":"Smith"
},
{
"first_name":"Jane",
"last_name":"Doe"
},
{
"first_name":"Brad",
"last_name":"Lee"
}
]
Field Name Selection
In the event you do not know the exact field you are looking for, you can select the Browse Fields link to open up a modal that will do a sampling of the possible options to pick from at whatever level of nesting the JSON Objects you are currently at.
Click Browse fields.
Which opens this modal
Give Grow a few minutes to sample some of the values available.
If you are still having trouble finding the exact name of the field you are looking for, we suggest exporting the table of data out of Grow and using an online JSON parsing tool online to find the exact field you are looking for and what the exact nested path looks like to that value.
Non-JSON Columns
There are sometimes columns in your data that may look like JSON in formatting, like a column created using a Calculated Columns transform when finding the difference of two timestamps, but these columns are not actual JSON. To help you know which columns in your data are valid JSON and which ones aren't we have grayed out any columns in your data that won't be compatible with this Transform.