Skip to content
Complete CDP/
/
/
Expand_jsonフィルター関数
Last updated

Expand_jsonフィルター関数

Treasure DataのData Connector用expand_jsonフィルタープラグインは、JSONを含むカラムを複数のカラムに展開します。このフィルターは以下に説明する5つのオプションを取ります：

オプション説明
json_column_name展開するJSONを含むカラム名（文字列、必須）
root各エントリの取得を開始するプロパティ、JSONPathスタイルで指定（文字列、デフォルト："$."）

expanded_columns

複数のカラムに展開（ハッシュの配列、必須）

  • name：カラムの名前。JSONPathスタイルで定義できます。
  • type：カラムの型（以下を参照）
  • format：typeがtimestampの場合のタイムスタンプの形式
  • timezone：値にタイムゾーンの説明が含まれていない場合のカラムのタイムゾーン
default_timezone値にタイムゾーンの説明が含まれていない場合のタイムスタンプカラムのタイムゾーン（デフォルトはUTC）
keep_expanding_json_columntrueの場合、入力スキーマから展開するJSONカラムを削除しない（デフォルトはfalse）
stop_on_invalid_record無効なレコードが含まれている場合、バルクロードトランザクションを停止（デフォルトはfalse）

カラムの型

名前説明
booleantrueまたはfalse
long64ビット符号付き整数
timestampナノ秒精度の日付と時刻
double64ビット浮動小数点数
string文字列

設定

load.ymlにtype expand_jsonを含むfilterセクションを追加します。例：

in:
...
filters:
  - type: expand_json
    json_column_name: json_payload
    root: "$."
    expanded_columns:
      - {name: "phone_numbers", type: string}
      - {name: "app_id", type: long}
      - {name: "point", type: double}
      - {name: "created_at", type: timestamp, format: "%Y-%m-%d", timezone: "UTC"}
      - {name: "profile.anniversary.et", type: string}
      - {name: "profile.anniversary.voluptatem", type: string}
      - {name: "profile.like_words[1]", type: string}
      - {name: "profile.like_words[2]", type: string}
      - {name: "profile.like_words[0]", type: string}
 out:
 ...

ユースケース1：1つのJSONカラムを展開

テーブルにJSONカラムがある場合、.ymlにこのセクションを追加します。

ソースデータの例

+-----------+---------------------------------------------------------------+
| code:long |                                                  jsons:string |
+-----------+---------------------------------------------------------------+
|         1 |   {"id":1,"name":"A green door","price":11.50,"tags":"green"} |
|         2 |     {"id":2,"name":"A blue door","price":12.50,"tags":"blue"} |
|         3 |       {"id":3,"name":"A red door","price":13.50,"tags":"red"} |
|         4 |     {"id":4,"name":"A pink door","price":14.50,"tags":"pink"} |
|         5 |   {"id":5,"name":"A white door","price":15.50,"tags":"white"} |
|         6 |   {"id":6,"name":"A black door","price":16.50,"tags":"black"} |
|         7 | {"id":7,"name":"A yellow door","price":17.50,"tags":"yellow"} |
|         8 | {"id":8,"name":"A purple door","price":18.50,"tags":"purple"} |
+-----------+---------------------------------------------------------------+

追加されたセクション。この場合、Data Connectorはjsonsカラムを展開します。

filters:
  - type: expand_json
    json_column_name: jsons
    root: "$."
    expanded_columns:
      - {name: "id", type: long}
      - {name: "name", type: string}
      - {name: "price", type: double}
      - {name: "tags", type: string}

結果は以下の通りです：

+-----------+---------+---------------+--------------+-------------+
| code:long | id:long |   name:string | price:double | tags:string |
+-----------+---------+---------------+--------------+-------------+
|         1 |       1 |  A green door |         11.5 |       green |
|         2 |       2 |   A blue door |         12.5 |        blue |
|         3 |       3 |    A red door |         13.5 |         red |
|         4 |       4 |   A pink door |         14.5 |        pink |
|         5 |       5 |  A white door |         15.5 |       white |
|         6 |       6 |  A black door |         16.5 |       black |
|         7 |       7 | A yellow door |         17.5 |      yellow |
|         8 |       8 | A purple door |         18.5 |      purple |
+-----------+---------+---------------+--------------+-------------+

ユースケース2：元のJSONカラムを保持

元のJSONカラムを保持したい場合、.ymlにkeep_expanding_json_columnオプションを追加します。

filters:
  - type: expand_json
    json_column_name: jsons
    root: "$."
    keep_expanding_json_column: true
    expanded_columns:
      - {name: "id", type: long}
      - {name: "name", type: string}
      - {name: "price", type: double}
      - {name: "tags", type: string}

結果は以下の通りです：

+-----------+---------------------------------------------------------------+---------+---------------+--------------+-------------+
| code:long |                                                  jsons:string | id:long |   name:string | price:double | tags:string |
+-----------+---------------------------------------------------------------+---------+---------------+--------------+-------------+
|         1 |   {"id":1,"name":"A green door","price":11.50,"tags":"green"} |       1 |  A green door |         11.5 |       green |
|         2 |     {"id":2,"name":"A blue door","price":12.50,"tags":"blue"} |       2 |   A blue door |         12.5 |        blue |
|         3 |       {"id":3,"name":"A red door","price":13.50,"tags":"red"} |       3 |    A red door |         13.5 |         red |
|         4 |     {"id":4,"name":"A pink door","price":14.50,"tags":"pink"} |       4 |   A pink door |         14.5 |        pink |
|         5 |   {"id":5,"name":"A white door","price":15.50,"tags":"white"} |       5 |  A white door |         15.5 |       white |
|         6 |   {"id":6,"name":"A black door","price":16.50,"tags":"black"} |       6 |  A black door |         16.5 |       black |
|         7 | {"id":7,"name":"A yellow door","price":17.50,"tags":"yellow"} |       7 | A yellow door |         17.5 |      yellow |
|         8 | {"id":8,"name":"A purple door","price":18.50,"tags":"purple"} |       8 | A purple door |         18.5 |      purple |
+-----------+---------------------------------------------------------------+---------+---------------+--------------+-------------+

ユースケース3：複数のJSONカラムを展開

複数のJSONカラムがある場合、.ymlにこれらのセクションを追加します。

ソースデータの例

+-----------+---------------------------------------------------------------+-----------------------------------+
| code:long |                                                  jsons:string |                     jsons2:string |
+-----------+---------------------------------------------------------------+-----------------------------------+
|         1 |   {"id":1,"name":"A green door","price":11.50,"tags":"green"} |  {"color":"green","size":"Large"} |
|         2 |     {"id":2,"name":"A blue door","price":12.50,"tags":"blue"} |   {"color":"blue","size":"Small"} |
|         3 |       {"id":3,"name":"A red door","price":13.50,"tags":"red"} |   {"color":"red","size":"Medium"} |
|         4 |     {"id":4,"name":"A pink door","price":14.50,"tags":"pink"} |  {"color":"pink","size":"Medium"} |
|         5 |   {"id":5,"name":"A white door","price":15.50,"tags":"white"} |  {"color":"white","size":"Large"} |
|         6 |   {"id":6,"name":"A black door","price":16.50,"tags":"black"} |  {"color":"black","size":"Large"} |
|         7 | {"id":7,"name":"A yellow door","price":17.50,"tags":"yellow"} | {"color":"yellow","size":"Small"} |
|         8 | {"id":8,"name":"A purple door","price":18.50,"tags":"purple"} | {"color":"purple","size":"Large"} |
+-----------+---------------------------------------------------------------+-----------------------------------+

追加されたセクション。この場合、Data Connectorはjsonsとjsons2カラムを展開します。

filters:
  - type: expand_json
    json_column_name: jsons
    root: "$."
    expanded_columns:
      - {name: "id", type: long}
      - {name: "name", type: string}
      - {name: "price", type: double}
      - {name: "tags", type: string}
  - type: expand_json
    json_column_name: jsons2
    root: "$."
    expanded_columns:
      - {name: "color", type: string}
      - {name: "size", type: string}

結果は以下の通りです：

+-----------+---------+---------------+--------------+-------------+--------------+-------------+
| code:long | id:long |   name:string | price:double | tags:string | color:string | size:string |
+-----------+---------+---------------+--------------+-------------+--------------+-------------+
|         1 |       1 |  A green door |         11.5 |       green |        green |       Large |
|         2 |       2 |   A blue door |         12.5 |        blue |         blue |       Small |
|         3 |       3 |    A red door |         13.5 |         red |          red |      Medium |
|         4 |       4 |   A pink door |         14.5 |        pink |         pink |      Medium |
|         5 |       5 |  A white door |         15.5 |       white |        white |       Large |
|         6 |       6 |  A black door |         16.5 |       black |        black |       Large |
|         7 |       7 | A yellow door |         17.5 |      yellow |       yellow |       Small |
|         8 |       8 | A purple door |         18.5 |      purple |       purple |       Large |
+-----------+---------+---------------+--------------+-------------+--------------+-------------+

ユースケース4：異なるキーを持つJSONを展開

異なるキーを持つJSONカラムがある場合、.ymlにこれらのセクションを追加します。

ソースデータの例

{"id":1,"name":"A green door","price":11.50,"tags":"green", "discount": 1.10}
{"id":2,"name":"A blue door","price":12.50,"tags":"blue"}
{"id":3,"name":"A red door","price":13.50,"tags":"red","discount": 1.00}
{"id":4,"name":"A pink door","price":14.50,"tags":"pink"}

追加されたセクション。この場合、Data ConnectorはJSON行を展開し、存在しないキーにはnull値を設定します。

filters:
  - type: expand_json
    json_column_name: record
    root: "$."
    expanded_columns:
      - {name: "id", type: long}
      - {name: "name", type: string}
      - {name: "price", type: double}
      - {name: "tags", type: string}
      - {name: "discount", type: double}

結果は以下の通りです：

+-----------+--------------+--------------+-------------+-----------------+
| id:string |  name:string | price:double | tags:string | discount:double |
+-----------+--------------+--------------+-------------+-----------------+
|         1 | A green door |         11.5 |       green |             1.1 |
|         2 |  A blue door |         12.5 |        blue |                 |
|         3 |   A red door |         13.5 |         red |             1.0 |
|         4 |  A pink door |         14.5 |        pink |                 |
+-----------+--------------+--------------+-------------+-----------------+

Encryptフィルター関数

Expanding JSON フィルター