athenaでjsonへのselectを攻略する

# athenaでjsonのカラムから値を抽出したい
必要な時にさっとクエリを書けるようにしたいので、よく使うパターンを例と共にまとめる。
### with句について
```sql
with 名前 as (select文)
```
として実行することで、付けた名前のテーブルのようにクエリが書ける。
この記事の例ではテーブルを作ってデータを入れる代わりに、with句を使って一時的なテーブルを作ってクエリを試していく。
with句と実行したいselect文を一緒に実行すればクエリを試すことができる。


# ユースケース①: jsonの内容を同じ行に展開したい場合
**key-valueで値の入ったjsonのカラムから値を取得したい場合**のユースケース

## クエリの書き方
`json_extract_scalar`か`json_extract`を使う。
**配列やmapの抽出には`json_extract_scalar`は使えない**ので、代わりに`json_extract`を使う。

```sql
SELECT
  json_extract_scalar({jsonのカラム名}, '$.{jsonのキー名}') AS {付けたい名前}
FROM dataset
```

```sql
SELECT
  json_extract({jsonのカラム名}, '$.{jsonのキー名}') AS {付けたい名前}
FROM dataset
```

## 例A: keyでvalueを取り出す
### 使用するデータセット例
some_mapにkey-valueでjsonを入れてある

```sql
WITH dataset AS (
  SELECT
    CAST('john' AS JSON) AS name,
    CAST(12345 AS JSON) AS count,
    CAST(MAP(ARRAY['location', 'role'], ARRAY['Tokyo','engineer']) AS JSON) AS some_map
)
SELECT * FROM dataset
```
#### 実行結果
```csvs

name	count	some_map
john	12345	{u'role': u'engineer', u'location': u'Tokyo'}
```

## jsonからrole,locationを取り出すクエリ
##### クエリ

```sql
SELECT
  name,
  count,
  json_extract_scalar(some_map, '$.role') AS role,
  json_extract_scalar(some_map, '$.location') AS location
FROM dataset
```

##### 実行結果

```csvs
name	count	role	location
john	12345	engineer	Tokyo
```

### 例B: jsonから配列を取り出す
1つのjson1カラムのみのデータにおいて、配列の要素を取り出してみる。
`json_extract_scalar`は使わず`json_extract`を使う
#### 使用するデータセット例
##### クエリ

```sql
WITH dataset AS (
  SELECT '{"name": "John",
           "role": "engineer",
           "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}'
    AS col
)
select * from dataset
```
##### 実行結果

```csvs
col
{"name": "John", "role": "engineer", "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}
```

#### jsonの配列を取り出すクエリ
```sql
SELECT json_extract(blob, '$.projects') AS item
FROM dataset
```
実行結果

```csvs
item
[{"completed":false,"name":"project1"},{"completed":true,"name":"project2"}]
```

### 例C: 配列からN番目の要素を取り出す
`json_array_get({配列}, {index}) `で取り出すことができる

#### 使用するデータセット例
##### クエリ

```sql
WITH dataset AS (
  SELECT '{"name": "John",
           "role": "engineer",
           "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}'
    AS col
)

select * from dataset
```

##### 実行結果

```csvs
col
{"name": "John", "role": "engineer", "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}
```
#### 配列の0番目の要素を取り出す

```sql
SELECT json_array_get(json_extract(col, '$.projects'), 0) AS item
FROM dataset
```

```csvs
item 
{"completed":false,"name":"project1"}
```

# ユースケース②:jsonのカラムの内容を複数行に展開したい場合
**配列で入っているjsonのカラムの内容を展開したい場合**のユースケース

## クエリの書き方
`unset`を使用する。
ぱっと見で理解し難いので例を参考にして欲しい。

```sql
SELECT {好きな名前}[{jsonのkey名}] 
FROM dataset, UNNEST({jsonの配列のカラム名}) AS t({好きな名前})
```

#### 使用するデータセット例
peopleという1カラムに、jsonが配列で入っている

#### クエリ

```sql
WITH
dataset AS (
  SELECT
     ARRAY[
      MAP(ARRAY['first', 'last', 'age'],ARRAY['Bob', 'Smith', '40']),
      MAP(ARRAY['first', 'last', 'age'],ARRAY['Jane', 'Doe', '30']),
      MAP(ARRAY['first', 'last', 'age'],ARRAY['Billy', 'Smith', '8'])
     ] AS people
  )
select * from dataset
```

#### 実行結果
```csvs
people 
[{last=Smith, first=Bob, age=40}, {last=Doe, first=Jane, age=30}, {last=Smith, first=Billy, age=8}]
```

### unnestで分解する
`people`というjsonの配列が入ったカラムをunnestし、
`names`というエンティティ(first,last,ageの3つのkey-valueをもつ)に展開し、
`names['first']`でnamesからvalueを取得する。

```sql
SELECT names['first'] AS first_name,
  names['last'] AS last_name,
  names['age'] AS age 
FROM dataset, UNNEST(people) AS t(names)
```

#### 実行結果

```sql
first_name	last_name	age
Bob	Smith	40
Jane	Doe	30
Billy	Smith	8
```

# 参考文献
athenaの公式ドキュメントくらいしか参考になるものがないため、頑張って読んで、載っているクエリを試してみて理解するのが一番良さそう。
https://docs.aws.amazon.com/athena/latest/ug/extracting-data-from-JSON.html
https://docs.aws.amazon.com/athena/latest/ug/flattening-arrays.html