EXAMPLE - Unnest JSON Records
You can unnest a set of JSON records into new columns of tabular data for easier manipulation within the application.
The following example contains records of messages about individual diet and exercise achievements:
{ "object": [ { "score": 19669, "title": "M/07/1'3\" [23lbs > 13lbs = 10lbs] Still a bit to go, but my owner no longer refers to me as his chunky boy!", "ups": 19669, "id": "9kt8ex", "url": "https://i.redd.it/bzygw285fpp11.jpg", "short": "bzygw285fpp11.jpg" }, { "score": 19171, "title": "M/29/5'11\" [605 pounds > 375 pounds = 230 pounds lost] (14 months) Still considered super morbidly obese but I've made some good progress.", "ups": 19171, "id": "9x2774", "url": "https://i.redd.it/wbbufmll0cy11.jpg", "short": "wbbufmll0cy11.jpg" }, { "score": 16778, "title": "F/28/5\u20197\u201d [233lbs to 130lbs] Got tired of being obese and took control of my life!", "ups": 16778, "id": "a8guou", "url": "https://i.redd.it/3t0kmljnmq521.jpg", "short": "3t0kmljnmq521.jpg" }, { "score": 16743, "title": "M/22/5'11\" [99lbs > 150lbs = 51lbs] Anorexia my recovery", "ups": 16743, "id": "atla3n", "url": "https://i.redd.it/9t6tvsjs16i21.jpg", "short": "9t6tvsjs16i21.jpg" } ] }
The outer JSON is a single key-value pair:
key:
object
value: array of JSON records
When source JSON records structured in this manner are imported, each JSON record in the object is imported into a separate row. You can unnest this data by applying an Unnest values transformation.
Nota
The object can contain only one nested array of JSON data. If the object contains multiple nested arrays, it is not not broken into separate rows. All unnesting must be performed in your recipe steps
Suppose you want to compute the average of all workout scores. First, you must unnest the JSON records and then apply the AVERAGE
function.
Steps:
Sugerencia
The easiest way to unnest is to select the column header for the column containing your data. After you select the column header, you are provided with suggestions to Unnest Values into new columns. You can use the Unnest suggestion and click Add. The following steps illustrate how to create this transformation manually.
In the Recipe panel, click New Step.
In the Search panel, enter
unnest values into new columns
.Specify the following transformation. Substitute the Paths to elements values below with the top-level keys in your JSON records:
Transformation Name
Unnest values into new columns
Parameter: Column
object
Parameter: Path to elements
id
Parameter: Path to elements
score
Parameter: Path to elements
short
Parameter: Path to elements
title
Parameter: Paths to elements
ups
Parameter: Path to elements
url
The above step breaks out the key-value pairs for the specified keys into separate columns in the dataset. Each Paths to elements entry specifies a key in the JSON record, which is used to create a new column of the same name. The key's associated value becomes a cell value in the new column.
You can now delete the source column. In the example, the source column is
object
.Sugerencia
You can choose to remove the original from the source or not. In deeper or wider JSON files, removing can help to identify what remains to be unnested. When you're done unnesting a column and have removed data from the original, you should have an empty column.
Your table should look like the following:
id | score | short | title | ups | url |
---|---|---|---|---|---|
9kt8ex | 19669 | bzygw285fpp11.jpg | M/07/1'3" [23lbs > 13lbs = 10lbs] Still a bit to go, but my owner no longer refers to me as his chunky boy! | 19669 | |
9x2774 | 19171 | wbbufmll0cy11.jpg | M/29/5'11" [605 pounds > 375 pounds = 230 pounds lost] (14 months) Still considered super morbidly obese but I've made some good progress. | 19171 | |
a8guou | 16778 | 3t0kmljnmq521.jpg | F/28/5’7” [233lbs to 130lbs] Got tired of being obese and took control of my life! | 16778 | |
atla3n | 16743 | 9t6tvsjs16i21.jpg | M/22/5'11" [99lbs > 150lbs = 51lbs] Anorexia my recovery | 16743 |
Now, you can apply the average function:
Transformation Name | |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | AVERAGE(score) |
Parameter: New column name | Average_score |
Results:
id | score | short | title | ups | url | Average_score |
---|---|---|---|---|---|---|
9kt8ex | 19669 | bzygw285fpp11.jpg | M/07/1'3" [23lbs > 13lbs = 10lbs] Still a bit to go, but my owner no longer refers to me as his chunky boy! | 19669 | 18090.25 | |
9x2774 | 19171 | wbbufmll0cy11.jpg | M/29/5'11" [605 pounds > 375 pounds = 230 pounds lost] (14 months) Still considered super morbidly obese but I've made some good progress. | 19171 | 18090.25 | |
a8guou | 16778 | 3t0kmljnmq521.jpg | F/28/5’7” [233lbs to 130lbs] Got tired of being obese and took control of my life! | 16778 | 18090.25 | |
atla3n | 16743 | 9t6tvsjs16i21.jpg | M/22/5'11" [99lbs > 150lbs = 51lbs] Anorexia my recovery | 16743 | 18090.25 |
If needed, these records can be re-nested into JSON records for downstream consumption.