[ODC]JSONSerializeしたListをCSVに変換する(1)とりあえず変換できるところまで

JavaScript用のライブラリで、JSONデータを簡単にCSVに変換できるものがあった。
このライブラリを使って、JSONをText型データとして受け取り、CSVに変換して返す処理を作成してみる。
想定するデータの形式としては、StructureやEntityのList（Structureは他のStructureやListを要素に持たないもの）。

# 環境情報
ODC Studio(Version 1.3.11)
BDD Framework(Version 0.1.2)
[json2csv](https://juanjodiaz.github.io/json2csv/#/)(Version 7.0.4)

# 注意事項
この記事では、変換可能であることを確認できるまでを書く。
ライブラリのロード方法、利用するAPIの選択、エラー処理などは、更に検討・対応が必要と思われる。
その辺は、機会があれば別の記事で検討する。

# 使用するライブラリ（json2csv) 確認
NPMでインストールするライブラリだが、CDN経由でブラウザに直接ロードする方法もある。
ここでは後者で確認する。
## ライセンス
[MIT License](https://github.com/juanjoDiaz/json2csv/blob/main/LICENSE.md)

## 機能確認方法
[Try it live!](https://juanjodiaz.github.io/json2csv/#/live)で変換予定のJSONを左の入力エリアに貼り付け、Convert JSON to CSVボタンをクリックすると、右の入力エリアに結果のCSVが表示される。
この際、Optionsを展開し、Flatten > Objectsのスイッチをオンにしておくと、EntityのListのようにオブジェクトが別の属性にぶら下がるケースにも対応できる。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/332987/24d12b47-d981-7283-da39-11f040dd54ba.png)

## 機能確認結果
- ヘッダ付き
- ヘッダ項目はそれぞれダブルクォートで囲まれている
- 項目がEntityの下にあるものであれば、「Entity名.項目名」がヘッダ名になる
- Entityのデータは展開されて出力される（例えばListに複数のEntityが含まれる場合、1行に各Entityの項目がすべて出力される）
- ボディの各項目は元の型を考慮したものになっている
### Structureのリスト
元のデータ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/332987/fb62c89f-eae5-8ca9-c967-aaebb3016ba7.png)

変換後
```
"BooleanVar","IntegerVar","TextVar"
false,1111,"aaaa"
true,2222,"cccc"
```

### Entityのリスト
元のデータ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/332987/c549f17f-ec1b-ea51-f786-3459fb8e2ec9.png)

変換後
```
"Sample_Office.Id","Sample_Office.Name","Sample_Office.Address","Sample_Office.Latitude","Sample_Office.Longitude"
799,"Australia","333 George St, Sydney, NSW 2000","-33.866716","151.206843"
800,"Japan","Kamiyacho MT Bldg 14th Floor 3-202 Toranomon, Minato-ku, Tokyo, 105-0001","35.663786","139.74466"
801,"Netherlands","Groenewoudsedijk 61, 2nd Floor, 3528 BG Utrecht, NL","52.067297","5.086609"
802,"Portugal","Rua Central Park 2, 2A, 2795-242 Linda-a-Velha","38.717084","-9.238795"
803,"Singapore","Suntec Tower Three, 8 Temasek Boulevard, #41-01, Singapore 038988","1.29533","103.85954"
804,"United Kingdom","3rd Floor, Lighterman House, 26-36 Wharfdale Road, London, N1 9RY","51.533688","-0.120221"
805,"USA - Atlanta, GA","5901 Peachtree Dunwoody Road NE, Building C 495, Atlanta, GA 30328","33.916492","-84.349509"
806,"USA Boston, MA","374 Congress St, 2nd Floor, Boston, MA 02210","42.350137","-71.047277"
```

### ジョインしたEntityのリスト
元のデータ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/332987/014f4fc8-6f20-ae00-4a3a-8b2b6381ef98.png)


変換後
```
"Sample_OfficeReviews.Id","Sample_OfficeReviews.OfficeId","Sample_OfficeReviews.ReviewId","Sample_Office.Id","Sample_Office.Name","Sample_Office.Address","Sample_Office.Latitude","Sample_Office.Longitude"
1459,806,5672,806,"USA Boston, MA","374 Congress St, 2nd Floor, Boston, MA 02210","42.350137","-71.047277"
1460,806,5673,806,"USA Boston, MA","374 Congress St, 2nd Floor, Boston, MA 02210","42.350137","-71.047277"
1461,799,5674,799,"Australia","333 George St, Sydney, NSW 2000","-33.866716","151.206843"
1462,799,5675,799,"Australia","333 George St, Sydney, NSW 2000","-33.866716","151.206843"
1463,800,5676,800,"Japan","Kamiyacho MT Bldg 14th Floor 3-202 Toranomon, Minato-ku, Tokyo, 105-0001","35.663786","139.74466"
1464,800,5677,800,"Japan","Kamiyacho MT Bldg 14th Floor 3-202 Toranomon, Minato-ku, Tokyo, 105-0001","35.663786","139.74466"
1465,801,5678,801,"Netherlands","Groenewoudsedijk 61, 2nd Floor, 3528 BG Utrecht, NL","52.067297","5.086609"
1466,801,5679,801,"Netherlands","Groenewoudsedijk 61, 2nd Floor, 3528 BG Utrecht, NL","52.067297","5.086609"
1467,802,5680,802,"Portugal","Rua Central Park 2, 2A, 2795-242 Linda-a-Velha","38.717084","-9.238795"
1468,802,5681,802,"Portugal","Rua Central Park 2, 2A, 2795-242 Linda-a-Velha","38.717084","-9.238795"
1469,803,5682,803,"Singapore","Suntec Tower Three, 8 Temasek Boulevard, #41-01, Singapore 038988","1.29533","103.85954"
1470,803,5683,803,"Singapore","Suntec Tower Three, 8 Temasek Boulevard, #41-01, Singapore 038988","1.29533","103.85954"
1471,804,5684,804,"United Kingdom","3rd Floor, Lighterman House, 26-36 Wharfdale Road, London, N1 9RY","51.533688","-0.120221"
1472,804,5685,804,"United Kingdom","3rd Floor, Lighterman House, 26-36 Wharfdale Road, London, N1 9RY","51.533688","-0.120221"
1473,805,5686,805,"USA - Atlanta, GA","5901 Peachtree Dunwoody Road NE, Building C 495, Atlanta, GA 30328","33.916492","-84.349509"
1474,805,5687,805,"USA - Atlanta, GA","5901 Peachtree Dunwoody Road NE, Building C 495, Atlanta, GA 30328","33.916492","-84.349509"
```

### ジョインしたEntityに計算列を追加したリスト
元のデータ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/332987/d8951fa5-4874-dff6-f6a0-79433374d949.png)

変換後
```
"Sample_OfficeReviews.Id","Sample_OfficeReviews.OfficeId","Sample_OfficeReviews.ReviewId","Sample_Office.Id","Sample_Office.Name","Sample_Office.Address","Sample_Office.Latitude","Sample_Office.Longitude","Now"
1459,806,5672,806,"USA Boston, MA","374 Congress St, 2nd Floor, Boston, MA 02210","42.350137","-71.047277","2024-01-13T11:51:15Z"
1460,806,5673,806,"USA Boston, MA","374 Congress St, 2nd Floor, Boston, MA 02210","42.350137","-71.047277","2024-01-13T11:51:15Z"
1461,799,5674,799,"Australia","333 George St, Sydney, NSW 2000","-33.866716","151.206843","2024-01-13T11:51:15Z"
1462,799,5675,799,"Australia","333 George St, Sydney, NSW 2000","-33.866716","151.206843","2024-01-13T11:51:15Z"
1463,800,5676,800,"Japan","Kamiyacho MT Bldg 14th Floor 3-202 Toranomon, Minato-ku, Tokyo, 105-0001","35.663786","139.74466","2024-01-13T11:51:15Z"
1464,800,5677,800,"Japan","Kamiyacho MT Bldg 14th Floor 3-202 Toranomon, Minato-ku, Tokyo, 105-0001","35.663786","139.74466","2024-01-13T11:51:15Z"
1465,801,5678,801,"Netherlands","Groenewoudsedijk 61, 2nd Floor, 3528 BG Utrecht, NL","52.067297","5.086609","2024-01-13T11:51:15Z"
1466,801,5679,801,"Netherlands","Groenewoudsedijk 61, 2nd Floor, 3528 BG Utrecht, NL","52.067297","5.086609","2024-01-13T11:51:15Z"
1467,802,5680,802,"Portugal","Rua Central Park 2, 2A, 2795-242 Linda-a-Velha","38.717084","-9.238795","2024-01-13T11:51:15Z"
1468,802,5681,802,"Portugal","Rua Central Park 2, 2A, 2795-242 Linda-a-Velha","38.717084","-9.238795","2024-01-13T11:51:15Z"
1469,803,5682,803,"Singapore","Suntec Tower Three, 8 Temasek Boulevard, #41-01, Singapore 038988","1.29533","103.85954","2024-01-13T11:51:15Z"
1470,803,5683,803,"Singapore","Suntec Tower Three, 8 Temasek Boulevard, #41-01, Singapore 038988","1.29533","103.85954","2024-01-13T11:51:15Z"
1471,804,5684,804,"United Kingdom","3rd Floor, Lighterman House, 26-36 Wharfdale Road, London, N1 9RY","51.533688","-0.120221","2024-01-13T11:51:15Z"
1472,804,5685,804,"United Kingdom","3rd Floor, Lighterman House, 26-36 Wharfdale Road, London, N1 9RY","51.533688","-0.120221","2024-01-13T11:51:15Z"
1473,805,5686,805,"USA - Atlanta, GA","5901 Peachtree Dunwoody Road NE, Building C 495, Atlanta, GA 30328","33.916492","-84.349509","2024-01-13T11:51:15Z"
1474,805,5687,805,"USA - Atlanta, GA","5901 Peachtree Dunwoody Road NE, Building C 495, Atlanta, GA 30328","33.916492","-84.349509","2024-01-13T11:51:15Z"
```

# 実装
## OnInitializeでライブラリをロードする
テスト用画面のEvents > On InitializeでClient Actionを作成する。
Action内にJavaScript要素を配置し、以下のコードを記述する。
``` js
if (!window.json2csvParser) {
    var head = document.getElementsByTagName('head')[0];
    var script = document.createElement('script');
    script.type = 'module';
    script.innerText = 
        "import Parser from '//cdn.jsdelivr.net/gh/juanjoDiaz/json2csv@7.0.4/dist/cdn/plainjs/Parser.js';\r\n" + 
        "window.json2csvParser = Parser;" +
        "import { flatten } from '//cdn.jsdelivr.net/gh/juanjoDiaz/json2csv@7.0.4/dist/cdn/transforms/index.js';" +
        "window.Json2csvTransforms = { flatten};";
    head.appendChild(script);
}
```

これは、headタグに以下のscriptタグを書き込むコード。Try it live!を開発者ツールで覗いて表示されたコードを参考にした。importしたjson2csvのオブジェクトをwindowオブジェクトのメンバとして保存している。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/332987/e5857e36-128a-6155-d9bf-399c70a76d88.png)

## 変換
ButtonのOn Click EventのハンドラーなどのActionを作成する。
Actionに以下のJavaScript要素を作成。エラー処理やオプション設定は改善の余地が大いにある（実際のプロジェクトで使う場合は対応検討。後ほどその辺の対応を入れる記事も作成予定）。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/332987/b7498b12-f9f8-b360-67e3-0408d2116c55.png)

flatten()によって、変換オプション（要素がオブジェクトであれば展開する）用の値を返してくれる。
json2csvParserやJson2csvTransformsはOn Initializeで保存しておいたjson2csvのAPI。
``` js
try {
    const opts = {"transforms": [new Json2csvTransforms.flatten()]};
    const parser = new window.json2csvParser(opts);
    $parameters.CSV = parser.parse(eval($parameters.JSON));
} catch (e) {    
    throw e;
}
```

# 動作確認
ボタンの上がJSON。ボタンをクリックするとCSVに変換し、下に表示する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/332987/6a40d5d1-7029-9737-5239-7bbb16d2ade9.png)