PowerAutomateで2つのレコードセットを結合する

今回はPowerAutomateでExcel OnlineやSharePointリストなどから取得した複数のデータを組み合わせて用いるときに遭遇した問題のトラブルシューティングです。

Problem

PowerAutomateでワークフローを作っていると、ExcelのテーブルやSharePointリストなどから取得したレコードセット（オブジェクトの配列）の結合をしたいということがたまにあります。ここで「結合」といっているのは、SQLにおけるJOINのような意味です。
PowerAutomateにはそれを行う組み込みの方法は用意されていません。

例えば次のような2つのレコードセットarrayAとarrayBがあったとき、arrayBのforeignKeyを使って2つを結合し、arrayBのkeyに対応するarrayAのvalue1を取得するというオペレーションは簡単にはいきません：

もちろん「Apply to each」や配列変数を利用すればなんとかなるのですが、フローの保守性をある程度維持するためにアクション数を増やしたくないときや、実行速度（フロー所要時間）を落としたくない場合には、別の方法を探ることになります。

Solution

複数の解決策がありえますが、今回はXPathを用いる方法でいこうと思います。

まず、2つのレコードセット（オブジェクトの配列）を用意します。もちろん本当のフローではこれらはExcelのテーブルやSharePointのリストから項目を取得するアクションにより置き換えられることになります：

arrayA.json

[
  {
    "key": 1,
    "value1": "arrayA_item1_value1",
    "value2": "arrayA_item1_value2"
  },
  {
    "key": 2,
    "value1": "arrayA_item2_value1",
    "value2": "arrayA_item2_value2"
  },
  {
    "key": 3,
    "value1": "arrayA_item3_value1",
    "value2": "arrayA_item3_value2"
  }
]

arrayB.json

[
  {
    "key": 100,
    "foreignKey": 1,
    "value1": "arrayB_item100_value1",
    "value2": "arrayB_item100_value2"
  },
  {
    "key": 101,
    "foreignKey": 1,
    "value1": "arrayB_item101_value1",
    "value2": "arrayB_item101_value2"
  },
  {
    "key": 102,
    "foreignKey": 1,
    "value1": "arrayB_item102_value1",
    "value2": "arrayB_item102_value2"
  },
  {
    "key": 200,
    "foreignKey": 2,
    "value1": "arrayB_item200_value1",
    "value2": "arrayB_item200_value2"
  },
  {
    "key": 300,
    "foreignKey": 3,
    "value1": "arrayB_item300_value1",
    "value2": "arrayB_item300_value2"
  },
  {
    "key": 301,
    "foreignKey": 3,
    "value1": "arrayB_item301_value1",
    "value2": "arrayB_item301_value2"
  }
]

このあとXPathによるオペレーションを行うため、arrayAをXMLに変換します。
まずはXMLに変換する下準備として {"root": { item: ... }}： で包みます：

{
  "root": {
    "item": @{outputs('Compose_arrayA')}
  }
}

そしてxml()関数でXML化します：

xml(outputs('Compose_enveloped_arrayA'))

XML化した結果はこんなかんじ：

arrayA.xml

<root>
    <item>
        <key>1</key>
        <value1>arrayA_item1_value1</value1>
        <value2>arrayA_item1_value2</value2>
    </item>
    <item>
        <key>2</key>
        <value1>arrayA_item2_value1</value1>
        <value2>arrayA_item2_value2</value2>
    </item>
    <item>
        <key>3</key>
        <value1>arrayA_item3_value1</value1>
        <value2>arrayA_item3_value2</value2>
    </item>
</root>

これで下準備は完了しました。「選択」アクションと xpath関数を使って結合を行います：

Map：

• arrayBKey: item()['key']
• arrayBValue1: item()['value1']
• arrayBValue2: item()['value2']
• arrayBForeignKey: item()['foreignKey']
• arrayAValue1: first(xpath(outputs('Compose_xmled_arrayA'),concat('//key[text()=',item()['foreignKey'],']/../value1/text()')))
• arrayAValue2: first(xpath(outputs('Compose_xmled_arrayA'),concat('//key[text()=',item()['foreignKey'],']/../value2/text()')))

arrayAValue1の式を細かく見てみましょう：

first(
  xpath(
    outputs('Compose_xmled_arrayA'), // ←XML化されたarrayA
    concat(
      '//key[text()=',
      item()['foreignKey'],
      ']/../value1/text()'
    ) // ←XPathのクエリ
  )
)

XPathのクエリを使って、foreignKeyに合致するテキストを持つ<key/>タグを検索し、当該タグの兄弟要素の中から<value1/>を検索し、そのテキストを返します。
XPath関数が返すのはテキスト（文字列型）の配列になるので、first()関数で単一値に直します。

「選択」アクションの出力は次のようになります：

[
  {
    "arrayBKey": 100,
    "arrayBValue1": "arrayB_item100_value1",
    "arrayBValue2": "arrayB_item100_value2",
    "arrayBForeignKey": 1,
    "arrayAValue1": "arrayA_item1_value1",
    "arrayAValue2": "arrayA_item1_value2"
  },
  {
    "arrayBKey": 101,
    "arrayBValue1": "arrayB_item101_value1",
    "arrayBValue2": "arrayB_item101_value2",
    "arrayBForeignKey": 1,
    "arrayAValue1": "arrayA_item1_value1",
    "arrayAValue2": "arrayA_item1_value2"
  },
  {
    "arrayBKey": 102,
    "arrayBValue1": "arrayB_item102_value1",
    "arrayBValue2": "arrayB_item102_value2",
    "arrayBForeignKey": 1,
    "arrayAValue1": "arrayA_item1_value1",
    "arrayAValue2": "arrayA_item1_value2"
  },
  {
    "arrayBKey": 200,
    "arrayBValue1": "arrayB_item200_value1",
    "arrayBValue2": "arrayB_item200_value2",
    "arrayBForeignKey": 2,
    "arrayAValue1": "arrayA_item2_value1",
    "arrayAValue2": "arrayA_item2_value2"
  },
  {
    "arrayBKey": 300,
    "arrayBValue1": "arrayB_item300_value1",
    "arrayBValue2": "arrayB_item300_value2",
    "arrayBForeignKey": 3,
    "arrayAValue1": "arrayA_item3_value1",
    "arrayAValue2": "arrayA_item3_value2"
  },
  {
    "arrayBKey": 301,
    "arrayBValue1": "arrayB_item301_value1",
    "arrayBValue2": "arrayB_item301_value2",
    "arrayBForeignKey": 3,
    "arrayAValue1": "arrayA_item3_value1",
    "arrayAValue2": "arrayA_item3_value2"
  }
]

いかがでしょう？
正直決して分かりやすいロジックではないのですが、PowerAutomate組み込みの機能だけを使いつつ、結合のために追加されるアクションが正味3つだけで済むというのはメリットではないかと思います：