[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(Version 7.0.4)

注意事項

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

使用するライブラリ（json2csv) 確認

NPMでインストールするライブラリだが、CDN経由でブラウザに直接ロードする方法もある。
ここでは後者で確認する。

ライセンス

MIT License

機能確認方法

Try it live!で変換予定のJSONを左の入力エリアに貼り付け、Convert JSON to CSVボタンをクリックすると、右の入力エリアに結果のCSVが表示される。
この際、Optionsを展開し、Flatten > Objectsのスイッチをオンにしておくと、EntityのListのようにオブジェクトが別の属性にぶら下がるケースにも対応できる。

機能確認結果

• ヘッダ付き
• ヘッダ項目はそれぞれダブルクォートで囲まれている
• 項目がEntityの下にあるものであれば、「Entity名.項目名」がヘッダ名になる
• Entityのデータは展開されて出力される（例えばListに複数のEntityが含まれる場合、1行に各Entityの項目がすべて出力される）
• ボディの各項目は元の型を考慮したものになっている

Structureのリスト

元のデータ

変換後

"BooleanVar","IntegerVar","TextVar"
false,1111,"aaaa"
true,2222,"cccc"

Entityのリスト

元のデータ

変換後

"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のリスト

元のデータ

変換後

"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に計算列を追加したリスト

元のデータ

変換後

"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要素を配置し、以下のコードを記述する。

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オブジェクトのメンバとして保存している。

変換

ButtonのOn Click EventのハンドラーなどのActionを作成する。
Actionに以下のJavaScript要素を作成。エラー処理やオプション設定は改善の余地が大いにある（実際のプロジェクトで使う場合は対応検討。後ほどその辺の対応を入れる記事も作成予定）。

flatten()によって、変換オプション（要素がオブジェクトであれば展開する）用の値を返してくれる。
json2csvParserやJson2csvTransformsはOn Initializeで保存しておいたjson2csvのAPI。

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に変換し、下に表示する。