EXAMPLE - Unnest JSON Records

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. 

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:

1. In the Recipe panel, click  New Step. 
2. In the Search panel, enter unnest values into new columns. 

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

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

5. You can now delete the source column. In the example, the source column is object. 

   Tip: 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:

Now, you can apply the average function: 

Results:

If needed, these records can be re-nested into JSON records for downstream consumption.