Skip to main content

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:

Suggerimento

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.

  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.

    Suggerimento

    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

https://i.redd.it/bzygw285fpp11.jpg

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

https://i.redd.it/wbbufmll0cy11.jpg

a8guou

16778

3t0kmljnmq521.jpg

F/28/5’7” [233lbs to 130lbs] Got tired of being obese and took control of my life!

16778

https://i.redd.it/3t0kmljnmq521.jpg

atla3n

16743

9t6tvsjs16i21.jpg

M/22/5'11" [99lbs > 150lbs = 51lbs] Anorexia my recovery

16743

https://i.redd.it/9t6tvsjs16i21.jpg

Now, you can apply the average function:

Transformation Name

New formula

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

https://i.redd.it/bzygw285fpp11.jpg

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

https://i.redd.it/wbbufmll0cy11.jpg

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

https://i.redd.it/3t0kmljnmq521.jpg

18090.25

atla3n

16743

9t6tvsjs16i21.jpg

M/22/5'11" [99lbs > 150lbs = 51lbs] Anorexia my recovery

16743

https://i.redd.it/9t6tvsjs16i21.jpg

18090.25

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