AWS Athena 配列内のJSON値を使って文字列へ変換する

What is Athena

S3上のCSVやらJSONファイルに対してSQLを発行してCSVを作成できるサービス
Athenaの実態はPrestoとなっているので、ドキュメントを探す場合は
prestodb.io/docsを探す事にする

解決したい事

JSONの中にはよく配列の中の値がさらにJSONになっている場合がよくある
こんな1レコードの結果をごにょごにょ整形してみたい
機械学習のデータセット作る時に重宝します。

{
  "title": "title",
  "list":[
    {"name": 'Bob', "age": 38},
    {"name": 'Alice', "age": 35},
    {"name": 'Jane', "age": 27}
  ]
}

テストデータを作る

prestoではwithを用いてsqlの前提条件を定義する事ができるので
それを利用してテストデータを作成する

WITH test_data AS (
  SELECT 
  CAST('title' AS VARCHAR) as title,
  ARRAY[
    CAST(ROW('Bob', 38) AS ROW(name VARCHAR, age INTEGER)),
    CAST(ROW('Alice', 35) AS ROW(name VARCHAR, age INTEGER)),
    CAST(ROW('Jane', 27) AS ROW(name VARCHAR, age INTEGER))
  ] AS list
)

テストデータを整形する

1. transformとlambdaを組み合わせて{"name": 'Bob', "age": 38}からnameだけを取り出す。　
   結果はこんな感じ

['Bob', 'Alice', 'Jane']

1. array_sortでアルファベット順にソートする
2. ARRAY_JOINで配列を一文字に変換する

WITH test_data AS (
  SELECT 
  CAST('title' AS VARCHAR) as title,
  ARRAY[
    CAST(ROW('Bob', 38) AS ROW(name VARCHAR, age INTEGER)),
    CAST(ROW('Alice', 35) AS ROW(name VARCHAR, age INTEGER)),
    CAST(ROW('Jane', 27) AS ROW(name VARCHAR, age INTEGER))
  ] AS list
),
transformed AS (
  select
   ARRAY_JOIN(array_sort(transform(test_data.list, x -> (x.name))), ' ')  as names
  from test_data
)

以上