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.

注記

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:

ヒント

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.
ヒント
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.

このセクションの内容:

EXAMPLE - Unnest JSON Records

検索結果