Treasure Data (Presto) で MongoDB の Array[BSON::ObjectId] 型のデータを string 型として入れてしまったとき Array にバラして展開する

1. substr で文字列からゴミを取り除く
2. split で string -> array にする
3. UNNEST で展開する 7.16. SELECT — Presto 0.147 Documentation

SELECT 
   *
FROM
  (
  VALUES(
    1,
    '[573ca5ca6200b048be000001, 573ca5cc6200b048be000002]'
  ),
  (
    2,
    '[573ca5ca6200b048be000003, 573ca5cc6200b048be000004]'
  )
  ) AS t(
    id,
    object_ids
  ) CROSS
JOIN
  unnest(
    split(
      substr(object_ids,
        2,
        length(object_ids) -2),
      ','
    )
  ) AS t(object_id)

Hive の場合は LATERAL VIEW というので最後の展開のステップができるらしい; 元々データエンジニアの人に「array の要素の一つに対して = で JOIN するにはどうすればよいか」と聞いて LATERAL VIEW を教えてもらい、 Presto でのやり方を調べた、という順番。

Embulk の設定とかで Array 型で入れられるなら当然そうしたほうがよい（できるのかどうか知らない）