転送元カラムのJSONに配列が含まれていた際、要素を指定して値を取得する方法を教えてください

概要

転送元カラムに格納されたJSON型のデータに配列が含まれており、その中から特定の要素の値を取得して利用したい場合、次の手順で必要な値を抽出できます。

Facebook Ad Insightsから取得した広告データを例に、特定の要素の値を取得する方法を解説します。
取得データには、actionsというフィールドが含まれており、その中には以下のようなJSON型のオブジェクトの配列が格納されています。
このactionsフィールドの配列を例に、特定のvalueの値を取得する方法を説明します。

[{"value":"20","action_type":"post_engagement"},{"value":"3","action_type":"link_click"}]

手順

1. 対象の転送設定で、「STEP2 データプレビュー・詳細設定」のデータ設定タブにある「カラム定義」の項目から、該当するカラムを選び、「JSONカラムを展開」のチェックボックスにチェックを入れます。
   

2. JSONパスを指定して値を取得します。
   ◆ 特定の配列要素のvalueを取得する
   配列内の特定の要素からvalueを取得する場合、インデックスを指定します（配列のインデックスは0から始まります）。
   例：1番目の要素のvalueを取得する

   • JSONパス

     [0].value

   • 取得される値

     20

     

   ◆ 配列内の全てのvalueを取得する
   配列内のすべての要素からvalueを取得する場合、ワイルドカード[*]を使用します。
   例：配列内の全てのvalueを取得する

   • JSONパス

     [*].value

   • 取得される値

     ["20","3"]

     

   ◆ 特定の条件に一致する要素のvalueを取得する
   特定のaction_typeを持つ要素からvalueを取得する場合、条件式を使用します。
   例：action_typeが'link_click'の要素のvalueを取得する

   • JSONパス

     [?(@.action_type == 'link_click')].value

   • 取得される値

     ["3"]

     

3. 「変更をプレビュー」を実行し、JSONカラムが展開されていることを確認します。

補足

JSONカラム展開後に不要な文字を除去する

取得された値に[""]が含まれている場合、以下の手順で除去できます。

1. 「STEP2 データプレビュー・詳細設定 」のデータ設定タブにある「文字列 正規表現置換」で、以下を設定します。
   • カラム名：[""]を除去する対象のカラム名
   • 正規表現パターン：[\["\]]
   • 置換させる文字列：空欄
2. 「変更をプレビュー」を実行します。

配列の要素を分割してレコード化する

以下のように要素ごとにレコードを分割するには、「JSONカラムを展開」ではなく、転送先での加工が必要です。
転送先がDWHの場合、データマート定義を利用して、UNNESTなどの関数を用いたクエリで要素ごとにレコードを分割できます。