Workato：Lookup Tables上のデータをSQLで絞り込むには

# はじめに

Lookup Tableで特定の条件にマッチするレコードを絞り込み出力したい場合、通常はSearch entriesアクションを利用します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/64876/24d247f9-e6ac-dee8-2060-ae8ccb91fbef.png)

Search entriesでは、文字列を部分一致させて結果を絞り込み出力することが可能です。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/64876/94cf2780-a53d-749d-288d-5429d2ec2aff.png)

# Search entriesの問題点

Lookup TablesのSearch entriesアクションはあくまでも簡易的な検索（絞り込み）しか行うことができません。また、全てのカラムが文字列型として扱われます。これにより、以下のような制限が発生します。

- 文字列の部分一致のみによる結果の絞り込みしか対応しておらず、詳細な条件指定ができない
  - 前方一致・後方一致ができない 
  - not が指定できない
  - distinct ができない
  - and/or指定ができない（単一キーワードの部分一致しか対応していない）
- Lookup Tablesは全てのカラムが文字列型であるため、数値や日付を範囲とする条件指定ができない

# Lookup Tables上のデータをSQLで絞り込む

以下のようなレシピを実装することで、Lookup Tables上のデータをSQLで絞り込むことが可能となります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/64876/b284973f-849c-f8eb-9372-72419fb9c808.png)

それぞれのステップについては、以下で説明します。

## Step 2

**Lookup Tables**の`Get all entries`アクションを使用して、Lookup Tablesの全レコードを取得します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/64876/359f675e-22c1-6cf2-b2ca-3d219627c0f6.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/64876/f98f169e-7972-5115-fd56-493df9a288dd.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/64876/796b57f4-053f-ded7-019f-2c98a30b7bb1.png)

## Step 3

**Variables by Workato**を追加し、`Create list`アクションを使用して、Lookup Tablesの全レコードをセットします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/64876/9e70a008-82cf-e260-ba7f-4c8c4c8711e5.png)


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/64876/1df5fa59-bed2-0ef4-f8ab-0342f208bbfd.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/64876/e4e77a47-1113-3491-e72f-f757075a942c.png)

### Variables by Workatoの Create list アクションを使用する理由

**Variables by Workato**の`Create list`アクションを使用する理由は次の通りです。

#### １．Lookup Tablesから取得したデータの構造を、2次元のデータ構造（行と列のデータ構造）へ変換

後続の手順に登場するSQL Collectionへは、2次元のデータ構造でデータを渡す必要があります。

Lookup Tablesの出力スキーマは以下のようになっています。の各データは `entry` というハッシュに格納され、各値は `entry` 以下に格納されています。見てわかる通り2次元のデータ構造とはなっておらず、そのままSQL Collectionで処理することができません。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/64876/dba7c451-bab2-48fd-9ba5-fd6795294158.png)

以上によりデータ構造の変換のため、Variables by WorkatoのCreate listを使用しています。

#### ２．各フィールドのデータ型を変換

Lookup Tablesの各フィールドのデータ型はテキスト型となっています。このままですと、例えば数値や日付を範囲とする条件指定（レコードの絞り込み）ができません。

Variables by WorkatoのCreate listにおいて各フィールドの型を設定のうえ、値（データピル）を各フィールドにセットすることで、セットされた値をデータ型に合わせて変換（キャスト）することができます。

### Step 4
**SQL Collection by Workato**を追加し、`Create List` アクションを使用して、Step3で作成したリストをList soruceへセットします。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/64876/8b085ee1-7421-5792-315f-ebcb3c78a836.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/64876/a95866a7-2442-1fe4-dcd7-2f3abc95e945.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/64876/8112d522-ce13-5342-3529-b3a26adf7dc5.png)

### Step 5
**SQL Collection by Workato**を追加し、`Query Lists` アクションを使用して、Step4で作成したリストに対するSQLクエリと出力スキーマ（Output list schemaスキーマ）を定義します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/64876/8b085ee1-7421-5792-315f-ebcb3c78a836.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/64876/943db685-2e33-66b5-8b0d-b5553b8479b9.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/64876/4bec3544-de18-fae7-6253-a6976e7c554d.png)

# おわりに

以上の手順でレシピの実装を進めていくことで、Lookup Tables上のデータをSQLを用いて絞り込むことが可能となります。

なお、今回はSQLを用いた絞り込み方法としてSQL Collection by Workatoを紹介しましたが、絞り込み以外の用途にも活用可能です。例えば、SQLを用いたデータの変換や重複排除にも対応可能です。SQLはSQLiteに準拠しており、標準的なSQL文やSQLiteに準拠したSQL文を使用して様々なデータ処理の実現が可能です。