JSONパースツール
JSONパースツール
ツールごとに学習
JSONパースには、「ツールごとに学習」が用意されています。サンプルワークフロー を参照して、このサンプルを含むさまざまなサンプルを Alteryx Designer で直接利用する方法をご確認ください。
JSONパースを使用すると、ダウンストリーム処理の目的で、JavaScript Object Notation (JSON)テキストをテーブルスキーマに分離できます。
Configure the Tool
JSON Field: Select the fields that hold JSON text. The field must contain valid JSON with all the text for a record in a single cell. Refer to this example:
{ "firstName": "John", "lastName": "Smith", "age": 25, "address": { "streetAddress": "21 2nd Street", "city": "New York", "state": "NY", "postalCode": "10021" }, "phoneNumber": [ { "type": "home", "number": "212 555-1234" }, { "type": "fax", "number": "646 555-4567" } ] }Input in Output: The selected column is included in the data stream coming out of the tool.
Choose how the JSON gets parsed. Choices include...
Output values into single string field
Output values into data type specific fields
Unnest JSON field: This option is available via AMP only.
Flatten array: This option is available via AMP only.
Refer to the sections below for additional information and examples for each of the above options.
This option outputs 2 fields: JSON_Name and JSON_ValueString. Using the input example above, this data comes out of the tool:
Example
JSON Input
{
"firstName": "John",
"lastName": "Smith",
"age": 25,
"address": {
"streetAddress": "21 2nd Street",
"city": "New York",
"state": "NY",
"postalCode": "10021"
},
"phoneNumber": [
{
"type": "home",
"number": "212 555-1234"
},
{
"type": "fax",
"number": "646 555-4567"
}
]
}JSON Parse Tool Output
JSON_Name | JSON_ValueString |
|---|---|
firstName | John |
lastName | Smith |
age | 25 |
address.streetAddress | 21 2nd Street |
address.city | New York |
address.state | NY |
address.postalCode | 10021 |
phoneNumber.0.type | home |
phoneNumber.0.number | 212-555-1234 |
phoneNumber.1.type | fax |
phoneNumber.1.number | 646-555-4567 |
This option outputs these 5 fields:
フィールド名 | 説明 |
|---|---|
JSON_Name | JSON_Nameは、JSONオブジェクト(キー:値のペア)の「キー」です。ドットは、キーと任意の階層カテゴリを区切ります。 |
JSON_ValueString | JSONオブジェクトの対応する文字列「値」(key：値のペア)。値が文字列でない場合、[Null]と表示されます。 |
JSON_ValueInt | JSONオブジェクトの対応する整数「値：(key：値のペア)。値が整数でない場合、[Null]と表示されます。 |
JSON_ValueFloat | JSONオブジェクトの対応するfloat値(key：値のペア)。値が浮動小数でない場合、[Null]と表示されます。 |
JSON_ValueBool | JSONオブジェクトの対応するbool「値」(key：値のペア)。値がboolでなければ、[NULL]と表示されます。 |
Example
JSON Input
{
"firstName": "John",
"lastName": "Smith",
"age": 25,
"address": {
"streetAddress": "21 2nd Street",
"city": "New York",
"state": "NY",
"postalCode": "10021"
},
"phoneNumber": [
{
"type": "home",
"number": "212 555-1234"
},
{
"type": "fax",
"number": "646 555-4567"
}
]
}JSON Parse Tool Output
JSON_Name | JSON_ValueString | JSON_ValueInt | JSON_ValueFloat | JSON_ValueBool |
|---|---|---|---|---|
firstName | John | [Null] | [Null] | [Null] |
lastName | Smith | [Null] | [Null] | [Null] |
age | [Null] | 25 | [Null] | [Null] |
address.streetAddress | 21 2nd Street | [Null] | [Null] | [Null] |
address.city | New York | [Null] | [Null] | [Null] |
address.state | NY | [Null] | [Null] | [Null] |
address.postalCode | 10021 | [Null] | [Null] | [Null] |
phoneNumber.0.type | home | [Null] | [Null] | [Null] |
phoneNumber.0.number | 212-555-1234 | [Null] | [Null] | [Null] |
phoneNumber.1.type | fax | [Null] | [Null] | [Null] |
phoneNumber.1.number | 646-555-4567 | [Null] | [Null] | [Null] |
This option allows to un-nest JSON objects into columns. It goes only one level deeper into the JSON object.
警告
Unnest JSON Field is available via AMP only.
Example
JSON Input
Consider an "hours" column in the input with these values:
{
"Tuesday": {"close": "17:00", "open": "08:00"},
"Friday": {"close": "17:00", "open": "08:00"},
"Monday": {"close": "17:00", "open": "08:00"},
"Wednesday": {"close": "17:00", "open": "08:00"},
"Thursday": {"close": "17:00", "open": "08:00"}
}JSON Parse Tool Output
This is the output when you unnest the above "hours" column:
hours.Tuesday | hours.Friday | hours.Monday | hours.Wednesday | hours.Thursday |
|---|---|---|---|---|
{"close":"17:00","open":"08:00"} | {"close":"17:00","open":"08:00"} | {"close":"17:00","open":"08:00"} | {"close":"17:00","open":"08:00"} | {"close":"17:00","open":"08:00"} |
Unnest on the "hours" column outputs columns "hours.Tuesday", "hours.Friday", "hours.Monday" and so on.
"hours.Tuesday" continues to carry the object: {"close": "17:00", "open": "08:00"}. You can unnest this again to hours.Tuesday.close and hours.Tuesday.open with another Json Parse tool.
This option is applicable for columns that have array values only. It allows you to expand a JSON array column by removing the square brackets. It creates a separate row for each element separated by a comma and assigns an ID for each row.
警告
Flatten Array is available via AMP only.
Example
JSON Input
Consider this array input in square brackets where the column name is "hours" and the column value is an array:
[
{"day": "Monday", "open": "08:00", "close": "17:00"},
{"day": "Tuesday", "open": "08:00", "close": "17:00"},
{"day": "Wednesday", "open": "08:00", "close": "17:00"},
{"day": "Thursday", "open": "08:00", "close": "17:00"},
{"day": "Friday", "open": "08:00", "close": "17:00"}
]JSON Parse Tool Output
hours_flatten | hours_idx |
|---|---|
{"day":"Monday","open":"08:00","close":"17:00"} | 1 |
{"day":"Tuesday","open":"08:00","close":"17:00"} | 2 |
{"day":"Wednesday","open":"08:00","close":"17:00"} | 3 |
{"day":"Thursday","open":"08:00","close":"17:00"} | 4 |
{"day":"Friday","open":"08:00","close":"17:00"} | 5 |