Athenaで基礎からしっかり入門 分析SQL（Pythonコード付き） #5

今まで複雑なデータ操作・分析などはPythonでやっており、SQLは普通のアプリ開発程度のライトなものしか触って来なかったのですが、やはり分析用の長いSQLなども書けた方がやりとり等で便利・・・という印象なので、復習も兼ねて記事にしておきます。

また、SQLに加えて検算も兼ねてPythonやPandasなどを使ったコードもSQLと併記していきます（Pythonで書くとどういった記述が該当するのかの比較用として使います）。

※長くなるのでいくつかの記事に分割します。本記事は5記事目となります。

# 他のシリーズ記事

Athenaとはなんぞやという方はこちらをご確認ください:

https://qiita.com/simonritchie/items/069c75059af3ba2ba914

※過去の記事で既に触れたものは本記事では触れません。

`#1`:

用語の説明・SELECT、WHERE、ORDER BY、LIMIT、AS、DISTINCT、基本的な集計関係（COUNTやAVGなど）、Athenaのパーティション、型、CAST、JOIN、UNION（INTERSECTなど含む）など。

https://qiita.com/simonritchie/items/0d84646e3db940e98b14

`#2`:

GROUP BY・HAVING・サブクエリ・CASE・COALESCE・NULLIF・LEAST・GREATEST・四則演算などの基本的な計算・日付と日時の各操作など。

https://qiita.com/simonritchie/items/367e63468e9c2f96b44f

`#3`:

文字列操作全般・正規表現関係など。

https://qiita.com/simonritchie/items/ce6792e9b3f6b4b3f63d

`#4`:

コメント関係・配列操作全般・ラムダ式など。

https://qiita.com/simonritchie/items/d425a9c66f7af5502195

# この記事で触れること

- 辞書関係全般（STRUCT、MAP、ROW、JSON）

# 環境の準備

以下の#1の記事でS3へのAthena用のデータの配置やテーブルのCREATE文などのGitHubに公開しているものに関しての情報を記載していますのでそちらをご参照ください。

https://qiita.com/simonritchie/items/0d84646e3db940e98b14#%E7%92%B0%E5%A2%83%E3%81%AE%E6%BA%96%E5%82%99

# 特記事項

AthenaのクエリエディタのUIが新しいバージョンのものにアクセスができるようになったため、本記事からはお試しで新しいUIのものを使っていっています。

# 辞書の操作

以降の節では辞書について全体的に色々と触れていきます。

## STRUCT型とMAP型とROW型とJSON型

まず型についてですが、辞書に近い挙動をするものも含めるとAthena（Presto）上にはSTRUCTとMAPとROWという型があります。

STRUCT型はキー名と値の型が固定された辞書となります。例えばSTRUCTで`x`というキーを整数、`y`というキーを文字列を定義したとすると`{"x": 10, "y": "allegro"}`といったような辞書の値を扱えるようになります。ただし定義されていないキーを使用することはできません。例えば`{"x": 10, "y": "allegro", "z": true}`といったように定義されていない`z`というキーは扱うことができません。

MAP型はキーの型と値の型が固定されます。型は固定されるものの、キー名自体は固定されません。例えばキーに武器のマスタID、値に攻撃力を持つ辞書・・・といったように、キー名がログの行ごとに様々なようなケースで使えます。例えば`{"10": 1200, "13": 2500}`みたいなログに向いています。

ROW型は辞書のようにキーと値のペアを持つ型となりますが、STRUCTやMAP型などとはCASTの方法や値への参照の書き方などが変わってきます。テーブル自体の定義では基本的にSTRUCTかMAPを使い、ROW型はAthena（Presto）の関数の返却値などで出てきます。例えば前回の配列関係の記事で触れたZIP関数などが該当します。

https://qiita.com/simonritchie/items/d425a9c66f7af5502195#2%E3%81%A4%E4%BB%A5%E4%B8%8A%E3%81%AE%E9%85%8D%E5%88%97%E3%82%92%E7%B5%B1%E5%90%88%E3%81%97%E3%81%9F%E9%85%8D%E5%88%97%E3%82%92%E5%8F%96%E5%BE%97%E3%81%99%E3%82%8B-zip

STRUCTなどのカラムに対してSELECTで表示した場合そのカラムの型はROWとなっています（ROW関係の制御が指定できます。この辺りは後々の節で触れます）。

また、JSON型というデータも存在します。こちらはJSON形式の文字列が特定のカラムに含まれている・・・といった場合に変換を挟んで利用したりします。

## 新しく扱う辞書用のテーブルについて

本記事から辞書関係のデータを扱っていく上で以下のテーブルが追加になっています。それぞれSTRUCT（キーが固定な辞書）用、MAP（キーが様々な辞書）用、STRUCTを格納したARRAY（辞書を格納した配列）用として設定しています。

---

**user_pvp_status テーブル**

user_pvp_status テーブルはユーザーのPVPごとのステータス情報を格納するテーブルです。基本的なカラムに加えて自身のPVP時点でのステータスのカラム（self_status）と相手のPVP時点でのステータスのカラム（enemy_status）を持っています。ステータスのカラムはSTRUCT型で、user_id, attack, defence, speed, luckというキーを持っています。

```sql
SELECT * FROM athena_workshop.user_pvp_status LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/d35b4443-c860-ee6b-f139-2bb32501283f.png)

**user_materials_count テーブル**

user_materials_count テーブルはユーザーの日次の所持している素材アイテム（合成素材的なものをイメージしてください）の所持数のデータを格納するテーブルです。基本的なカラムに加えてキーに対象のアイテムのマスタID、値に所持数を格納した辞書のカラム（materials_count）を持っています。対象のカラムはMAP型でキーも値も整数で設定してあります。

```sql
SELECT * FROM athena_workshop.user_materials_count LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/3fa260b4-9c2f-50ea-28f0-0597d32ba501.png)

**user_weapon テーブル**

user_weapon テーブルはユーザーの所持している武器データの配列をする格納するテーブルです。基本的なカラムに加えてweaponsというカラム名で武器データの辞書を格納した配列が設定されています。辞書には武器のマスタIDのmst_idキー、攻撃力のattackキー、レベルのlevelキーが設定されています。

```sql
SELECT * FROM athena_workshop.user_weapon LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/5645b0f2-d6bb-9d79-13f1-5586401f4de7.png)

**user_weapon_json_str テーブル**

user_weapon_json_str テーブルは内容はほぼ user_weapon テーブルと同じですが、武器のデータのカラムのみ配列ではなくJSONの文字列になっています。後の節でJSON変換などの説明のために使っていきます。

```sql
SELECT * FROM athena_workshop.user_weapon_json_str LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/0ef277aa-3144-7ec2-1dbf-a7ecb81551fa.png)

## STRUCTのカラムの値はSELECTで表示するとROW型となる

STRUCTのカラムの値に関してですが、SELECTで値を表示すると結果はROW型として表示されます。UI上の表示としては`{user_id=3642, attack=905, ...}`といったように辞書のような表示となり、ただしキー名（フィールド名）と値の間がイコールで繋がれる形となります。また、対象のカラムの型を表示するTYPEOF関数を使うとROW型として表示されることが分かります（`row(user_id bigint, attack bigint, ...)`といったような表示になります）。

```sql
SELECT TYPEOF(self_status) AS self_status_type, self_status
FROM athena_workshop.user_pvp_status LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/8dca55f5-218a-6e96-25c9-2cd340ce0e21.png)

## STRUCTのカラム内の特定の値にアクセスする

STRUCTのカラムの値はSELECTするとROW型となるため、ROW型と同じようにカラム内の特定の値にアクセスすることができます。

ROW型は`<対象のカラム名>.<フィールド名>`といった形でドット区切りで指定することで特定の値にアクセスすることができます。なおカラム名はSTRUCT内のフィールド名が自動で設定されるようです。ASとかを指定しなくても該当の名前が表示されています。

```sql
SELECT self_status.user_id, self_status.attack, self_status
FROM athena_workshop.user_pvp_status LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/de4a6972-630e-3506-921a-270365da6981.png)

## MAPのカラムの値はSELECTで表示してもMAPのままとなる

STRUCT型と異なりMAP型の方はSELECTで表示してもそのままMAP型で扱われます。TYPEOF関数で表示してみると`map(bigint, bigint)`といったように表示されることが確認できます。値の表示はROW型と同じように、`{{16=37, 18=24, 10=13}}`といったようにキーと値の間がイコールで繋がれる形となります。

```sql
SELECT TYPEOF(materials_count) AS map_type, materials_count
FROM athena_workshop.user_materials_count
LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/48dcdebb-2e55-e875-8683-d634fe841d87.png)

## MAPのカラムの特定のキーの値にアクセスする

MAP型はSTRUCT型やROW型のデータとは特定のキー（フィールド）へのアクセスの仕方が変わり、`<カラム名>[<キーの値>]`といったように`[]`の括弧を使ってアクセスしていきます。

MAP型はSTRUCTとは異なりキーの値が固定されない（行ごとに様々）ため、指定したキーが存在しない行に関しては表示が`-`といったようにハイフンになります。

以下のSQLでは`[15]`といったように指定してキーが15の値を表示しています。

```sql
SELECT materials_count[15] AS material_id_15_count, materials_count
FROM athena_workshop.user_materials_count
WHERE dt = '2021-01-01'
LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/1e1c4ad1-f53f-e4f0-646d-cb7a88a9b844.png)

## ROW型の値の固定値の作り方

ROW型の固定値は`()`の括弧内にコンマ区切りで値を指定することで作ることができます。

```sql
SELECT ('a', 1) AS result_row
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/068f18b5-6da3-e207-df4d-fb1bfff7f6ab.png)

これだけだとフィールド名（キー名）がfield0, field1, ...と割り振られます。もしフィールド名の指定をしたい場合には以下のように`ROW`関数と`CAST`による変換処理を指定することで対応ができます。

```sql
SELECT CAST(ROW('a', 2) AS ROW(name VARCHAR, age BIGINT)) AS result_row
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/545acd15-e4e0-a745-d70a-2ce52967a837.png)

## MAP型のデータの様々な関数

MAP型に関しては追加でいくつかの関数が用意されています。以降の節ではそれらの各関数について触れていきます。

### 2つの配列からMAPの値を生成する: MAP

`MAP`関数では2つの配列を渡すことで、それぞれがキーと値として使われる形でMAPの値を取得することができます。

```sql
SELECT MAP(ARRAY['a', 'b', 'c'], ARRAY[1, 2, 3]) AS result_map
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/ed139f39-00a6-8e22-e98f-b426241fefd6.png)

#### Pythonでの書き方

ビルトインの辞書の生成処理などはごく基礎的なのでスキップします。

Pandasのシリーズであれば色々書き方はありますが一例として以下のようにしてキーと値のセットを作ることができます。

```py
import pandas as pd

sr: pd.Series = pd.Series(data={'a': 1, 'b': 2, 'c': 3})
print(sr)
```

```
a    1
b    2
c    3
dtype: int64
```

### ROW型を格納した配列からMAPの値を生成する: MAP_FROM_ENTRIES

`MAP_FROM_ENTRIES`関数ではROW型の値を格納した配列からMAPの値を生成します。ROW型の値部分には最初のフィールドにはキー名、2番目の値には値の2件のみを格納した形式が必要になります。

```sql
SELECT MAP_FROM_ENTRIES(ARRAY[('a', 1), ('b', 2), ('c', 3)]) AS result_map
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/4d69672c-5df0-27da-1244-2f3b8b318ee7.png)

### 値が配列となるMAPの値を生成する: MULTIMAP_FROM_ENTRIES

`MULTIMAP_FROM_ENTRIES`関数は`MAP_FROM_ENTRIES`関数と似ていますが、MAPの値が配列になって返ってきます。また、同名のキー名が複数ある場合にも対応しています。

以下のSQLではMAPのaのキーが複数存在する条件を指定しています。

```sql
SELECT MULTIMAP_FROM_ENTRIES(ARRAY[('a', 1), ('b', 2), ('a', 3)]) AS result_map
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/9d6b29c4-96df-00e4-98ed-d21a70a2fdcf.png)

### MAPからROW型の値を格納した配列を取得する: MAP_ENTRIES

`MAP_ENTRIES`では`MAP_FROM_ENTRIES`関数などとは逆にMAPの値からROWの値を格納した配列を取得します。ROWのフィールド名は順番にfield0, field1, ...と設定されていきます。

```sql
SELECT MAP_ENTRIES(materials_count) AS materials_count_rows,
materials_count
FROM athena_workshop.user_materials_count LIMIT 10
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/8d9fd80e-db45-45ef-90f3-ac645f23643f.png)

### キーと値のペアの値を作る: MAP_AGG

`MAP_AGG`関数では第一引数にキー、第二引数に値を指定することで単一の値のMAPの値を取得することができます。

```sql
SELECT MAP_AGG('a', 1) AS result_map
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/63f53382-1917-44d8-8338-8cae7c7248c9.png)

なお、この`MAP_AGG`関数ですがデータの縦持ち・横持ちの変換に使われるケースもあります。そちらに関してはリンクを貼っておきます。

https://qiita.com/k24d/items/79bc4828c918dfeeac34#presto

### 特定のキーの値を取得する: ELEMENT_AT

`[]`による添え字でのアクセスとほぼ同じような挙動をしますが、`ELEMENT_AT`関数でも特定のキー名の部分のMAPの値を取得することができます。第一引数には対象のMAPのカラムもしくはMAPの固定値、第二引数にはキーを指定します。

```sql
SELECT ELEMENT_AT(materials_count, 2) AS element_2,
materials_count
FROM athena_workshop.user_materials_count LIMIT 10
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/e6ea4876-424e-9c3d-482f-7762bc87e03e.png)

#### Pythonでの書き方

Pythonのビルトインの辞書では`[]`の括弧を使うか、もしくは存在しないキーでもエラーにせずにNoneを返却するようにしたければ`get`メソッドなどを使います。

```py
from typing import Dict, Optional

dict_val: Dict[str, int] = {'a': 1, 'b': 2, 'c': 3}
value: Optional[int] = dict_val.get('b')
print(value)
```

```
2
```

Pandasのシリーズであれば`[]`の括弧を使います。

```py
import pandas as pd

sr: pd.Series = pd.Series(data={'a': 1, 'b': 2, 'c': 3})
print(sr['b'])
```

```
2
```

データフレームであれば`[]`の括弧を2個繋げたり`iloc`や`loc`、`at`など色々書き方があります。`at`を使うと以下のような書き方になります。

```py
import pandas as pd

df: pd.DataFrame = pd.DataFrame(data=[{'a': 1, 'b': 2}, {'a': 3, 'b': 4}])
print(df.at[0, 'a'])
```

```
1
```

### MAPのキーの配列を取得する: MAP_KEYS

`MAP_KEYS`関数ではMAPの値のキーの値の配列を返却します。なお、元のMAPの値の順番は担保されないようなので注意が必要です。Python3.6や3.7辺りから標準の挙動となった順序付きの辞書的な挙動はしません。

```sql
SELECT MAP_KEYS(materials_count) AS keys,
materials_count
FROM athena_workshop.user_materials_count LIMIT 10
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/9504d793-6158-53c1-22af-68bafb01dbe6.png)

#### Pythonでの書き方

ビルトインの辞書であれば`keys`メソッドが用意されています。値がリストで欲しい場合は`list`関数でキャストします。

```py
from typing import Dict

dict_val: Dict[str, int] = {'a': 1, 'b': 2, 'c': 3}
print(list(dict_val.keys()))
```

```
['a', 'b', 'c']
```

Pandasのシリーズもしくはデータフレームであればindex属性でアクセスができます。

```py
import pandas as pd

sr: pd.Series = pd.Series(data={'a': 1, 'b': 2, 'c': 3})
print(sr.index)
```

```
Index(['a', 'b', 'c'], dtype='object')
```

リストが欲しい場合には`tolist`メソッドで変換が効きます。

```py
import pandas as pd

sr: pd.Series = pd.Series(data={'a': 1, 'b': 2, 'c': 3})
print(sr.index.tolist())
```

```
['a', 'b', 'c']
```

### MAPの値の配列を取得する: MAP_VALUES

`MAP_VALUES`関数ではMAPの値の配列を取得することができます。こちらも`MAP_KEYS`関数と同様に値の順番は担保されない（元のMAPの値と同じにはならない）ので注意が必要です。

```sql
SELECT MAP_VALUES(materials_count) AS keys,
materials_count
FROM athena_workshop.user_materials_count LIMIT 10
```

#### Pythonでの書き方

ビルトインの辞書の場合は`values`メソッドで値が取れます。こちらもリストが欲しい場合には`list`関数でキャストする必要があります。

```py
from typing import Dict

dict_val: Dict[str, int] = {'a': 1, 'b': 2, 'c': 3}
print(list(dict_val.values()))
```

```
[1, 2, 3]
```

PandasのシリーズやNumPyのndarrayなどであれば`values`属性で取れます。リストが欲しい場合には`tolist`メソッドが必要になります。

```py
import pandas as pd

sr: pd.Series = pd.Series(data={'a': 1, 'b': 2, 'c': 3})
print(sr.values.tolist())
```

```
[1, 2, 3]
```

### 複数のMAPを統合する: MAP_CONCAT

`MAP_CONCAT`関数は複数のMAPの値を連結します。各引数には各MAPのカラムもしくはMAPの固定値を指定します。

以下のSQLでは第一引数にMAP関数から生成したMAPの固定値（キーに100と101、値に1と2という設定をしてあります）、第二引数にMAPのカラムを指定しています。結果のMAPのキーに100や101が含まれていることが確認できます。

```sql
SELECT MAP_CONCAT(MAP(ARRAY[100, 101], ARRAY[1, 2]), materials_count) AS result_map
FROM athena_workshop.user_materials_count LIMIT 10
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/c7b5af3c-4afa-12e5-6da1-d6faf1098943.png)

この関数の引数は可変長なので、第三引数、第四引数・・・と必要に応じてMAPの引数を増やしていくこともできます。

注意点として、以下のようなものがあります。

- キーの順番は担保されません。第一引数のMAPの値が結果のMAPの先に来たりはしません。
- 各引数でキーが被った場合は、後の引数の値によって上書きされます。
- 各MAPのキーと値の型は一致していないとエラーになります。

キーが被るケースを少し試してみます。以下のSQLでは第一引数のMAPの固定値のキーを1と2、値を3と4としています。

```sql
SELECT MAP_CONCAT(MAP(ARRAY[1, 2], ARRAY[3, 4]), materials_count) AS result_map
FROM athena_workshop.user_materials_count LIMIT 10
```

1と2というキーは第二引数のカラムでも持っていることが多いので、1=97とか1=75といったように第二引数の値で上書きされて第一引数の値は消えていることが確認できます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/613e1ff0-9ba6-2865-23f6-c84eeaacefd9.png)

また、各引数でMAPの型が一致していないケースを試してみます。`materials_count`カラムは`map<bigint, bigint>`の型を持つため、第一引数は`map<string, bigint>`の型で試してみます。

```sql
SELECT MAP_CONCAT(MAP(ARRAY['a', 'b'], ARRAY[3, 4]), materials_count) AS result_map
FROM athena_workshop.user_materials_count LIMIT 10
```

実行してみると以下のようにエラーとなります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/6b33135d-df9d-f15e-d338-165ffd1d87af.png)

#### Pythonでの書き方

ビルトインの辞書であれば`update`メソッドで別の辞書の値を加えることができます。こちらもキーが重複している場合には引数で指定された辞書の方の値が優先されます。

```sql
from typing import Dict

dict_val_1: Dict[str, int] = {'a': 1, 'b': 2, 'c': 3}
dict_val_2: Dict[str, int] = {'c': 4, 'd': 5}

dict_val_1.update(dict_val_2)
print(dict_val_1)
```

```
{'a': 1, 'b': 2, 'c': 4, 'd': 5}
```

Pandasのシリーズなどであれば`concat`関数などで複数のシリーズ（やデータフレーム）を統合することができます。ちなみにシリーズやデータフレームは同じインデックスを複数持つことができるので、Athena（Presto）やビルトインの辞書と異なりインデックスが被った場合でもそれぞれの値が残ります（以下のコードではcというキーの値が2つ残っています）。

```py
import pandas as pd

sr_1: pd.Series = pd.Series(data={'a': 1, 'b': 2, 'c': 3})
sr_2: pd.Series = pd.Series(data={'c': 4, 'd': 5})
result_sr: pd.Series = pd.concat([sr_1, sr_2])
print(result_sr)
```

```
a    1
b    2
c    3
c    4
d    5
dtype: int64
```

### MAPの値を特定条件でフィルタリングする: MAP_FILTER

`MAP_FILTER`関数はMAPのキーと値をラムダ式で参照して、条件を満たすもののに結果のMAPに残すという挙動をします。

ラムダ式に関しては以前の記事で触れたので詳しい書き方などはそちらをご確認ください。

https://qiita.com/simonritchie/items/d425a9c66f7af5502195#%E3%83%A9%E3%83%A0%E3%83%80%E5%BC%8Flambda-expressions

ラムダ式の引数にはキーと値の2つが渡されます。この記事ではキーの引数名を`k`、値の引数名を`v`として扱っていきます。

返却値は真偽値で設定し、trueであれば結果に残りfalseであれば結果から除外されます。

以下のSQLでは`1, -2, 5`という3つの値を持っているMAPに対して、値が0以上（`v >= 0`）の値のみ結果のMAPに残しています。

```sql
SELECT MAP_FILTER(MAP(ARRAY['a', 'b', 'c'], ARRAY[1, -2, 5]), (k, v) -> v >= 0) AS result_map
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/7fb6c50d-56bc-8b1b-1d8b-3bc778fa6c71.png)

#### Pythonでの書き方

ビルトインの辞書であればループを回すか、`filter`関数とラムダ式などを組み合わせるか、もしくは内包記法などを使うかといった方法があります。

`filter`関数は以下のような内容になっています。

> filterは第1引数に与えられた関数を第2引数で与えられたシーケンス1つ1つに適応し、Trueになったシーケンスのみを返す関数です。
> [【Python】dictをfilterする](https://tombomemo.com/python-dict-filter/)

```py
from typing import Dict

dict_val: Dict[str, int] = {'a': 1, 'b': -2, 'c': 5}
dict_val = dict(filter(lambda item: item[1] >= 0, dict_val.items()))
print(dict_val)
```

```
{'a': 1, 'c': 5}
```

### キーに調整を加える: TRANSFORM_KEYS

`TRANSFORM_KEYS`関数はラムダ式を使って各キーを調整したMAPのデータを取得することができます。第一引数にはMAPのカラムもしくはMAPの固定値、第二引数にはラムダ式を指定します。

ラムダ式の引数にはキー（`k`）と値（`v`）の2つの引数を受け付け、返却値にはキーの値の1つの値が必要になります。

以下のSQLではキー名を`キー_値`といったようにアンダースコアを挟んでキーと値を連結しています（結果のキーは`a_1`とか`c_1`などになります）。

```sql
SELECT TRANSFORM_KEYS(MAP(ARRAY['a', 'b', 'c'], ARRAY[1, 2, 1]), (k, v) -> k || '_' || CAST(v AS VARCHAR))
AS result_map
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/91091edf-8d9e-4f75-1390-279411bb5055.png)

#### Pythonでの書き方

色々書き方があると思いますが、ビルトインの辞書であれば内包記法とかがシンプルかもしれません。

```py
from typing import Dict

dict_val: Dict[str, int] = {'a': 1, 'b': 2, 'c': 1}
renamed_dict_val: Dict[str, int] = {
    f'{k}_{v}': v for k, v in dict_val.items()}
print(renamed_dict_val)
```

```
{'a_1': 1, 'b_2': 2, 'c_1': 1}
```

※f-stringsとかも使っています。

https://qiita.com/simonritchie/items/74f544944ee11a226613

### 値に調整を加える: TRANSFORM_VALUES

`TRANSFORM_VALUES`関数は`TRANSFORM_KEYS`関数の値版です。値に対してラムダ式を反映したMAPを取得します。

以下のSQLは`TRANSFORM_KEYS`の時とほぼ同じ内容ですが、結果は値の方が`a_1`とかになっていることを確認できます。

```sql
SELECT TRANSFORM_VALUES(MAP(ARRAY['a', 'b', 'c'], ARRAY[1, 2, 1]), (k, v) -> k || '_' || CAST(v AS VARCHAR))
AS result_map
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/b06134a8-9f8a-6c16-4c43-41fe5aa965c3.png)

#### Pythonでの書き方

キーの時とほぼ同じで内包記法で対応しています。

```py
from typing import Dict

dict_val: Dict[str, int] = {'a': 1, 'b': 2, 'c': 1}
result_dict_val: Dict[str, str] = {
    k: f'{k}_{v}' for k, v in dict_val.items()}
print(result_dict_val)
```

```
{'a': 'a_1', 'b': 'b_2', 'c': 'c_1'}
```

### 2つのMAPに対して同じキーの値同士で計算を行う: MAP_ZIP_WITH

`MAP_ZIP_WITH`関数は2つのMAPの値に対して、同じキーを持つ値同士で処理を行うことができます。結果もMAPの値となります。

第一引数には1つ目のMAPのカラムもしくはMAPの固定値、第二引数には2つ目のMAPのカラムもしくはMAPの固定値、第三引数にはラムダ式が必要になります。

ラムダ式にはキー名（`k`）、1つ目のMAPの該当のキーの値（`v1`）、2つ目のMAPの該当のキーの値（`v2`）が渡され、返却値としてはMAPの値として1つの値が必要になります。

以下のSQLでは2つのMAPの値に対して、各値を合算したMAPを`MAP_ZIP_WITH`関数を使って取得しています。

```sql
SELECT MAP_ZIP_WITH(
    MAP(ARRAY['a', 'b', 'c'], ARRAY[1, 2, 3]),
    MAP(ARRAY['a', 'b', 'c'], ARRAY[4, 5, 6]),
    (k, v1, v2) -> v1 + v2
)
AS result_map
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/9bcfc939-6b73-5204-213e-36fb82aa3706.png)

なお、片方にしか無いキーが存在するとそちらの値はNULLになるので注意が必要です（欠損値の制御が必要になります）。

#### Pythonでの書き方

色々書き方がありますが、ビルトインの辞書であれば一例として以下のコードでは一旦辞書の`keys`メソッドや`set`関数などを使って2つの辞書の一意なキーの配列を取得して、それを使って内包記法（もしくは普通のループを回す形でも良いと思います）で処理をしています。

```py
from typing import Dict, List

dict_val_1: Dict[str, int] = {'a': 1, 'b': 2, 'c': 3}
dict_val_2: Dict[str, int] = {'a': 4, 'b': 5, 'c': 6}
keys: List[str] = list(set([*dict_val_1.keys(), *dict_val_2.keys()]))

result_dict: Dict[str, int] = {k: dict_val_1[k] + dict_val_2[k] for k in keys}
print(result_dict)
```

```
{'a': 5, 'b': 7, 'c': 9}
```

### MAPのデータのカーディナリティのサイズを取得する: CARDINALITY

`CARDINALITY`関数ではカーディナリティのサイズを取得できます。カーディナリティってなんだ・・・という感じですが、MAP内で異なる値が多い（バリエーションが豊富）な行ほどサイズが大きくなる・・・という値のようです。

> カーディナリティとは、数学で基数あるいは濃度という意味の用語。ITの分野では、リレーショナルデータベースにおいてあるテーブルの同一の列（カラム）に含まれる異なる値の数（バリエーション）のことを指すことが多い。
> [カーディナリティ 【cardinality】](https://e-words.jp/w/%E3%82%AB%E3%83%BC%E3%83%87%E3%82%A3%E3%83%8A%E3%83%AA%E3%83%86%E3%82%A3.html)

引数は第一引数のMAPのカラムもしくはMAPの固定値のみです。

```sql
SELECT CARDINALITY(materials_count) AS cardinality_size, materials_count
FROM athena_workshop.user_materials_count
LIMIT 10
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/8fabf520-7fa7-4083-8102-57aeb02156e2.png)

## MAPの値を行列に変換する

MAPの値をキーと値で各列に展開し、行列として扱うと便利なことがあります。そういった場合はUNNESTを使うと行列に変換できます。

以前の配列の以下の記事で詳しく触れましたが、書き方としては`FROM <対象テーブル>, UNNEST(対象テーブルのMAPのカラム) AS <展開後のテーブル名>(展開後のキー名, 展開後のカラム名)`となります。

https://qiita.com/simonritchie/items/d425a9c66f7af5502195#%E4%BB%96%E3%81%AE%E3%82%AB%E3%83%A9%E3%83%A0%E3%81%AE%E5%80%A4%E3%82%92%E6%AE%8B%E3%81%97%E3%81%A4%E3%81%A4%E9%85%8D%E5%88%97%E3%81%AE%E3%83%8D%E3%82%B9%E3%83%88%E3%81%95%E3%82%8C%E3%81%9F%E3%83%87%E3%83%BC%E3%82%BF%E3%82%92%E8%A1%8C%E5%88%97%E3%81%AB%E3%81%99%E3%82%8B-unnest

配列の時と比べてASの後の`()`の括弧内がキーと値で2つ必要になっている点には注意が必要です。また、配列の記事で書きましたがその後のデータでさらに連結など色々と操作をする場合CROSS JOINを一緒に使ってUNNESTする必要があるケースもあります。詳しくはリンク先をご確認ください。

以下のSQLではMAPの値を行列に展開しています。キーは`materials_id`, 値は`count`という名前にしています。

```sql
SELECT user_id, materials_id, count, materials_count
FROM athena_workshop.user_materials_count, UNNEST(athena_workshop.user_materials_count.materials_count)
AS unnested_table(materials_id, count)
LIMIT 100
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/aa73d85d-784b-1b14-8860-dcd82e505dd6.png)

## STUCTの値を行列に変換する

1次元のSTRUCTの場合はSELECT結果はROW型となるため、そのままダイレクトに使うキーの値をドットで繋いで展開すれば行列として扱えます。

```sql
SELECT
self_status.user_id AS self_user_id,
self_status.attack AS self_attack,
self_status.defence AS self_defence
FROM athena_workshop.user_pvp_status
LIMIT 100
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/0f698e94-9681-a38e-13d3-fe2f26f39121.png)

## STRUCTを格納した配列を行列に変換する

STRUCT型の辞書を格納した配列といった具合に2次元でデータが格納されているケースもよくあります。そういったデータを行列に変換してみます。

まず配列に対しては以下の過去の記事で触れたようにUNNESTで展開できます。

https://qiita.com/simonritchie/items/d425a9c66f7af5502195#%E4%BB%96%E3%81%AE%E3%82%AB%E3%83%A9%E3%83%A0%E3%81%AE%E5%80%A4%E3%82%92%E6%AE%8B%E3%81%97%E3%81%A4%E3%81%A4%E9%85%8D%E5%88%97%E3%81%AE%E3%83%8D%E3%82%B9%E3%83%88%E3%81%95%E3%82%8C%E3%81%9F%E3%83%87%E3%83%BC%E3%82%BF%E3%82%92%E8%A1%8C%E5%88%97%E3%81%AB%E3%81%99%E3%82%8B-unnest

```sql
SELECT user_id, weapon, weapons
FROM athena_workshop.user_weapon, UNNEST(athena_workshop.user_weapon.weapons) AS unnested_weapons(weapon)
LIMIT 100;
```

展開後はROW型で`{mst_id=360, attack=1092, level=9}`といったような値で1次元になっていることが確認できます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/b7e96986-510c-b326-27f6-fcd9b8f8d5ba.png)

あとはSTRUCTの行列変換の節で触れたような形で必要な各キーをSELECT句の部分に指定すれば行列に展開できます。

```sql
SELECT user_id, weapon.mst_id, weapon.attack, weapon.level, weapons
FROM athena_workshop.user_weapon, UNNEST(athena_workshop.user_weapon.weapons) AS unnested_weapons(weapon)
LIMIT 100;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/508a7e9d-2e33-7ada-a423-aa9b22b50e3e.png)

### Pythonでの書き方

他の行列変換の処理も同様なのですが、この手の変換は以前記事を書いたのでそちらをご確認ください。

https://qiita.com/simonritchie/items/7e50b47ccadbbb0fba57

# JSONのデータを扱う

ログによっては配列などではなくJSON形式の文字列で入っている・・・といったケースなどがあります。この節以降ではそういったJSONデータに対して触れていきます。

## 値をJSONへ変換する

JSONへは真偽値・整数値・文字列（VARCHAR）・配列・辞書（MAPもしくはROW）や欠損値（NULL）の値を変換できます。配列などで入れ子にする場合には内部に格納する値もこれらの値である必要があります。

また、MAPの辞書などであればキーは文字列になっている必要があります（整数とかになっていると文字列に変換されます）。配列に関しても複数の型を格納していると変換がうまくいきません（例えば文字列と整数が混在している配列など）。それらは事前に型変換などをかけておく必要があります。

変換処理はCAST関数で行うことができます。CASTのAS部分にはJSONと指定します。表示上はTYPEOFなどで型を表示しない限り違いが分かりづらいですが、TYPEOF関数などで表示される型はJSONとなります。

```sql:整数をJSONに変換する例
SELECT CAST(10 AS JSON) AS json_value, TYPEOF(CAST(10 AS JSON)) AS type
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/293117ae-a84e-810e-869a-cc282994af4c.png)

```sql:配列をJSONに変換する例
SELECT CAST(ARRAY[10, 20, 30] AS JSON) AS json_value, TYPEOF(CAST(ARRAY[10, 20, 30] AS JSON)) AS type
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/e99e84bc-e754-d5cc-392a-6ee7153ce545.png)

キーが整数のMAPを指定してみると、キーにダブルクォーテーションが追加されていることから分かるようにキーが文字列に変換されることが分かります（エラーにはなりません）。

```sql:キーが整数のMAPをJSONに変換する例
SELECT CAST(MAP(ARRAY[10], ARRAY[20]) AS JSON) AS json_value
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/25f3f347-8132-6e15-ca40-4b3c94841826.png)

配列に複数の型が含まれている場合、例えば整数と文字列が含まれている場合などには変換しようとしてもエラーになります。

```sql:複数の型を含む配列をJSONに変換するとエラーになる例
SELECT CAST(ARRAY[10, 'cat'] AS JSON) AS json_value
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/6a488177-907a-324f-adfe-3c599d01b1a4.png)

※JSON形式の文字列をJSONに変換する・・・という場合はCASTによる変換ではなく後述するJSON_PARSEを使う必要があります。CASTでそういった文字列に対して処理を反映すると単純にその文字列のJSONの単一値となり、階層構造などは展開されません。

## JSONから別の型に値を変換する

こちらもCAST関数で任意のJSONからの変換がサポートされている型であれば変換ができます（`CAST(<JSONの値> AS <変換したい型>)`といった具合に指定します）。

```sql:JSONの値から整数に変換する例
SELECT CAST(CAST(10 AS JSON) AS BIGINT) AS result, TYPEOF(CAST(CAST(10 AS JSON) AS BIGINT)) AS type
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/62d53d1f-e8a1-bc3f-e693-695486219d62.png)

## JSONの文字列をJSON型に変換する: JSON_PARSE

JSONの文字列をJSON型に変換したい場合`JSON_PARSE`を使います。第一引数に対象のJSONの文字列のカラムを指定します。

```sql
SELECT JSON_PARSE(weapons) AS weapons
FROM athena_workshop.user_weapon_json_str LIMIT 10;
```

結果はSTRUCTなどと異なりROW型のような表記にはならずにJSONの表示そのままになります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/d2cf9494-b545-c737-2590-78f5318cb277.png)

`TYPEOF`で型を見てみると確かにVARCHARなどではなくJSONの型になっています。

```sql
SELECT TYPEOF(JSON_PARSE(weapons)) AS weapons
FROM athena_workshop.user_weapon_json_str LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/38aaf457-237d-8465-ce3b-74d3d9e9a2d7.png)

### Pythonでの書き方

`json`モジュールの`loads`関数でJSON形式の文字列をPythonのデータ（辞書など）に変換できます。

```py
import json
from typing import Dict

dict_val: Dict[str, int] = json.loads('{"a": 10, "b": 20}')
print(dict_val)
```

```
{'a': 10, 'b': 20}
```

PandasなどでそういったJSON形式の文字列のが含まれている場合には、データフレームなどでのapplyメソッドで`loads`関数を指定すれば対応ができます。

```py
import json

import pandas as pd

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/user_weapon_json_str/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, orient='records', compression='gzip')
df['weapons'] = df['weapons'].apply(json.loads)
print(df.head())
print('type:', type(df.at[0, 'weapons']))
```

```
   user_id       date  device_type                                            weapons
0    23475 2021-01-01            2  [{'mst_id': 1700, 'attack': 300, 'level': 2}, ...
1    23826 2021-01-01            2  [{'mst_id': 1672, 'attack': 1709, 'level': 15}...
2     2496 2021-01-01            2  [{'mst_id': 655, 'attack': 1238, 'level': 12},...
3     1192 2021-01-01            1  [{'mst_id': 56, 'attack': 871, 'level': 6}, {'...
4    26195 2021-01-01            1  [{'mst_id': 1427, 'attack': 836, 'level': 9}, ...
type: <class 'list'>
```

## JSONの値をJSONの文字列に変換する: JSON_FORMAT

`JSON_FORMAT`関数はJSONの値をJSON形式の文字列へと変換します。`JSON_PARSE`関数と逆の挙動をします。

```sql
SELECT JSON_FORMAT(CAST(weapons AS JSON)) AS json_str,
TYPEOF(JSON_FORMAT(CAST(weapons AS JSON))) AS type
FROM athena_workshop.user_weapon
LIMIT 10;
```

実行結果の型がVARCHARになっていることが確認できます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/94e93805-eb74-e1e9-aabf-513cf305ace6.png)

※CASTで文字列に変換しただけだと正確なJSONのフォーマットにならないケースが結構ある（JSONに合わせたフォーマットではなくSQLに合わせたフォーマットになったりする）ため、その辺りでエラーなどを避けるためにもCASTではなくJSON_FORMAT関数の利用が推奨されます。

## JSONの値を配列に変換する

`JSON_PARSE`でJSON型にした値は前節までで触れたように`CAST`関数で他の型に変換することができます。今回のJSONは配列になっているため配列にキャストしてみます。

辞書を含んだ配列・・・といったデータであれば、`CAST(<JSONの値> AS ARRAY(ROW(<辞書のフォーマット>)))`といったように、`ARRAY`関数に`ROW`関数を入れ子にすることで表現できます。`ROW`関数は`ROW(<キー名> 値の型, <キー名> 値の型)`といったように書きます（例 : `ROW(a BIGINT, b VARCHAR)`）。

```sql
SELECT CAST(JSON_PARSE(weapons) AS ARRAY(ROW(mst_id VARCHAR, attack BIGINT, level BIGINT))) AS weapons
FROM athena_workshop.user_weapon_json_str LIMIT 10;
```

これでARRAYとSTRUCTを扱った時と同じような値となるので、STRUCT関係の節で触れたような必要な行列変換などを入れれば色々操作が行えます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/7dc0b9b6-42c0-68f7-682a-5d594c2c7ad8.png)

もしくはキーが多すぎて個別に型を定義するのが難しい・・・といった場合には、JSONの型を含んだ配列（`ARRAY(JSON)`）といったように指定するのが楽なケースもあります。こちらの場合は行列変換などはJSONのフォーマットによる参照方法などが必要になってきます（後の節で触れます）。

```sql
SELECT CAST(JSON_PARSE(weapons) AS ARRAY(JSON)) AS weapons
FROM athena_workshop.user_weapon_json_str LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/2eeb158d-9bc3-0f0a-c675-acbf91a6bb12.png)

## 特定の位置のJSONの値を抽出する: JSON_EXTRACT と JSON_EXTRACT_SCALAR

JSONの値の特定の位置の値を抽出するには`JSON_EXTRACT`関数を使います。第一引数には対象のJSONのカラムもしくは固定値、第二引数には対象の位置の指定用の文字列を設定します。

第二引数の位置指定用の文字列は[JSONPath - XPath for JSON](https://goessner.net/articles/JsonPath/)の資料に書かれているものが使えるようです。全ては触れませんが良く使いそうなものをこの節で触れていきます。

まずは`$`の記述です。これはJSONのルート（一番上の階層）を示します。`$`を単体で指定した場合は一番上のフォルダから中身の全てのファイルが参照されるように、JSONのデータ全体の参照となるため`JSON_EXTRACT`を使わない場合と同じ結果になります。

```sql
SELECT JSON_EXTRACT(JSON_PARSE(weapons), '$') AS result
FROM athena_workshop.user_weapon_json_str
WHERE dt = '2021-01-01'
LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/d7d6dafa-acf3-bb62-eeb4-b462b58596d0.png)

JSON内の特定の位置のデータを抽出したい場合ルートのデータから子の要素にアクセスしていく必要があります。そういった場合には`.`のドット記号かもしくは`[]`の記号を使ってインデックスの指定をする形で参照します。

例えば今回扱っているデータだとルート部分が配列になっているので、配列の先頭の要素にアクセスしたい場合ドット記号を使うと`.0`といったように書きます。ルートの位置からその配列の先頭のデータ・・・とするため、ルートの`$`と合わせて`$.0`といったように指定します。ちなみにAthena（Presto）のSQL上だと最初のインデックスは1から始まるケースが大半ですが、JSONの場合0から始まります。最初のデータが0となり1ではないため注意が必要です。

```sql
SELECT JSON_EXTRACT(JSON_PARSE(weapons), '$.0') AS result
FROM athena_workshop.user_weapon_json_str
WHERE dt = '2021-01-01'
LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/e1467d9d-afdc-2879-ae7e-69c3986c54c7.png)

JSONの配列の先頭のデータのみを抽出することができました。ドット記号を使うのと同じように`[]`の括弧を使って参照することもできます。例えば配列の先頭のインデックスにアクセスしたい場合には`[0]`といった書き方でもアクセスすることができます。

```sql
SELECT JSON_EXTRACT(JSON_PARSE(weapons), '$[0]') AS result
FROM athena_workshop.user_weapon_json_str
WHERE dt = '2021-01-01'
LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/23b70167-14be-4b31-20ef-cd9d7772989d.png)

配列の先頭のデータの辞書部分でさらに特定のキーにアクセスしたいとします。例えば`attack`キーのデータだけ欲しい・・・といった場合を考えます。

そういった場合は子の要素には整数だけでなくキー名なども指定できるため、ドットを使う場合は`$.0.attack`といったように書くことで子要素にアクセスすることができます。

```sql
SELECT JSON_EXTRACT(JSON_PARSE(weapons), '$.0.attack') AS result
FROM athena_workshop.user_weapon_json_str
WHERE dt = '2021-01-01'
LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/35cda14d-0b99-235c-7c73-fc4ae4ddce22.png)

`[]`の括弧を使うこともできます。キー名で`[]`の括弧を使う場合にはダブルクォーテーションで囲んで書いてもダブルクォーテーションを省略してもどちらでも動くようです（JSONだとダブルクォーテーションで囲む方が自然ですが、特に必要というわけではないようです）。

```sql
SELECT JSON_EXTRACT(JSON_PARSE(weapons), '$[0][attack]') AS result
FROM athena_workshop.user_weapon_json_str
WHERE dt = '2021-01-01'
LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/2ee1ba34-5c99-4f7d-e4c2-1b000e4556c4.png)

その他にもリンク先の資料だと全てを表すアスタリスクや複数条件の`[]`の括弧内のコンマの記述やスライス記法なども書かれていますが、なんだかAthena上ではエラーになる？ようで、Athenaではまだ使えないのかもしれません。

JSON内の配列の全データを展開したい・・・といった場合はこの関数ではなくUNNESTなどで各行を辞書などで行列変換し、その後`JSON_EXTRACT`関数で各キーの値を展開する・・・とすると良いかもしれません。

なお、`JSON_EXTRACT`関数では抽出結果の値は全てJSONの型となります。試しに以下のようにルートの配列部分や配列内の辞書、さらにその辞書の特定のキーの値の型をそれぞれ表示してみても全てjsonと表示されます。

```sql
SELECT
TYPEOF(JSON_EXTRACT(JSON_PARSE(weapons), '$')) AS array_type,
TYPEOF(JSON_EXTRACT(JSON_PARSE(weapons), '$[0]')) AS dict_type,
TYPEOF(JSON_EXTRACT(JSON_PARSE(weapons), '$[0][attack]')) AS scalar_type
FROM athena_workshop.user_weapon_json_str
WHERE dt = '2021-01-01'
LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/569ed6ce-eb3c-b449-99d5-e461e61cb963.png)

一方で`JSON_EXTRACT_SCALAR`関数は文字列で値が返ってくるようです。数値などの他の型であってもVARCHARに結果はなります（文字列以外はどのみちCASTが必要になりそうなので、あまり使い道は無い・・・？かもしれません）。

```sql
SELECT
JSON_EXTRACT_SCALAR(JSON_PARSE(weapons), '$[0][attack]') AS attack,
TYPEOF(JSON_EXTRACT_SCALAR(JSON_PARSE(weapons), '$[0][attack]')) AS attack_type
FROM athena_workshop.user_weapon_json_str
WHERE dt = '2021-01-01'
LIMIT 10;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/d5cc1bfe-ad61-9c4c-dfe0-02ba4e4fc913.png)

### Pythonでの書き方

`json.lodas`で文字列からリストに変換した後に、追加で特定の位置の値を抽出する関数（今回は`extract_attack_from_weapons`という関数名にしてあります）を設けてそちらもapplyメソッドで反映すれば抽出できます。

```py
import json
from typing import Any, Dict, List

import pandas as pd


def extract_attack_from_weapons(weapons: List[Dict[str, Any]]) -> int:
    """
    指定された武器データの配列から、先頭の武器の攻撃力の値を抽出する。

    Parameters
    ----------
    weapons : list of dict
        武器データを格納した配列。

    Returns
    -------
    attack : int
        抽出された攻撃力。空のリストの場合には0が設定される。
    """
    if not weapons:
        return 0
    return weapons[0]['attack']


df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/user_weapon_json_str/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, orient='records', compression='gzip')
df['weapons'] = df['weapons'].apply(json.loads)
df['attack'] = df['weapons'].apply(extract_attack_from_weapons)
print(df[['attack', 'weapons']].head())
```

```
   attack                                            weapons
0     300  [{'mst_id': 1700, 'attack': 300, 'level': 2}, ...
1    1709  [{'mst_id': 1672, 'attack': 1709, 'level': 15}...
2    1238  [{'mst_id': 655, 'attack': 1238, 'level': 12},...
3     871  [{'mst_id': 56, 'attack': 871, 'level': 6}, {'...
4     836  [{'mst_id': 1427, 'attack': 836, 'level': 9}, ...
```

## 値がJSONで扱えるスカラー値かどうかの真偽値を取得する: IS_JSON_SCALAR

`IS_JSON_SCALAR`関数では特定の値がJSONで扱える単一のスカラー値（単一の数値、文字列、真偽値、NULLなど）かどうかの真偽値を返します。単一の数値などであればtrue、そうではなく配列などの複数の値を含むデータなどであれはfalseとなります。

以下のSQLでは整数・文字列・真偽値・配列の固定値に対して`IS_JSON_SCALAR`関数を実行していますが、配列のみfalseになっていることが確認できます。

```sql
SELECT IS_JSON_SCALAR(CAST(10 AS JSON)) AS int_is_json_scalar,
IS_JSON_SCALAR(CAST('Hello' AS JSON)) AS string_is_json_scalar,
IS_JSON_SCALAR(CAST(true AS JSON)) AS bool_is_json_scalar,
IS_JSON_SCALAR(CAST(ARRAY[1, 2] AS JSON)) AS array_is_json_scalar
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/0834f08a-9524-c74c-eea1-e2b1de468143.png)

## JSONの配列内に特定の値がふくまれているかの真偽値を取得する: JSON_ARRAY_CONTAINS

`JSON_ARRAY_CONTAINS`関数はJSONの配列内に特定の値が含まれているかどうかの真偽値を取得することができます。存在すればtrue、存在しなければfalseとなります。通常の配列でもこの辺の関数はありますが、JSONのまま扱いたい場合などに使用します。

第一引数には対象のJSONの配列、第二引数には検索対象の値を指定します。

```sql
SELECT JSON_ARRAY_CONTAINS(CAST(ARRAY[1, 2, 3] AS JSON), 2) AS result
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/f457e48d-fa9d-2e07-5932-8e06ff280bb2.png)

ちなみにですが、JSONの配列の形式の文字列であればキャストやJSON_PARSE関数などを通さなくても文字列のまま処理が流せるようです。例えば以下のように文字列を直接指定してもtrueが返ります。

```sql
SELECT JSON_ARRAY_CONTAINS('[1, 2, 3]', 2) AS result
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/98f571c1-d72f-3c79-979d-bb48e8992960.png)

### Pythonでの書き方

正直この辺はJSON変換が必要になる以外は配列関係に触れた以前の記事のままなのでリンクだけ貼って割愛します。inキーワードで対応ができます。

https://qiita.com/simonritchie/items/d425a9c66f7af5502195#%E9%85%8D%E5%88%97%E3%81%8C%E7%89%B9%E5%AE%9A%E3%81%AE%E5%80%A4%E3%82%92%E5%90%AB%E3%82%80%E3%81%8B%E3%81%A9%E3%81%86%E3%81%8B%E3%81%AE%E7%9C%9F%E5%81%BD%E5%80%A4%E3%82%92%E5%8F%96%E5%BE%97%E3%81%99%E3%82%8B-contains

## JSONの配列の長さを取得する: JSON_ARRAY_LENGTH

`JSON_ARRAY_LENGTH`関数ではJSONの配列の長さ（値の件数）を取得することができます。

以下のSQLでは3件の値の配列なので3が返っています。

```sql
SELECT JSON_ARRAY_LENGTH(CAST(ARRAY[1, 2, 3] AS JSON)) AS length
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/1ece38d9-5aff-ac2a-ee6d-e7977d052424.png)

こちらもJSON形式の文字列であればキャストやJSON_PARSE関数などを省いても動くようです。

```sql
SELECT JSON_ARRAY_LENGTH('[1, 2, 3]') AS length
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/1e0b072f-5d6e-6e8d-61ab-077e526324c8.png)

### Pythonでの書き方

`json.loads`関数でJSONをリストに変換した後に、`len`関数でリストの値の件数が取れるのでそちらをapplyメソッドで反映すれば取れます。

```py
import json
from typing import Any, Dict, List

import pandas as pd

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/user_weapon_json_str/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, orient='records', compression='gzip')
df['weapons'] = df['weapons'].apply(json.loads)
df['length'] = df['weapons'].apply(len)
print(df[['length', 'weapons']].head())
```

```
   length                                            weapons
0      61  [{'mst_id': 1700, 'attack': 300, 'level': 2}, ...
1      44  [{'mst_id': 1672, 'attack': 1709, 'level': 15}...
2      60  [{'mst_id': 655, 'attack': 1238, 'level': 12},...
3      26  [{'mst_id': 56, 'attack': 871, 'level': 6}, {'...
4      77  [{'mst_id': 1427, 'attack': 836, 'level': 9}, ...
```

## 特定位置のJSONの要素の数を取得する: JSON_SIZE

`JSON_SIZE`関数は特定の位置のJSONの要素の数を取得します。`JSON_ARRAY_LENGTH`関数とは異なり第二引数に位置の指定が必要になります。第二引数の位置の指定には`JSON_EXTRACT`関数の節で触れたようなJSONPathの記法の文字列が必要になります。

対象は配列もしくは辞書の位置でのみ正常な値が取れます。それ以外のスカラー値の位置では返却値は0となります。

以下のSQLでは2次元配列で配列の先頭の位置（`$.0`）の配列の値（`[1, 2, 3]`）の件数を取得しています。

```sql
SELECT JSON_SIZE('[[1, 2, 3], [4]]', '$.0') AS length
```

結果は3となります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/228778/903bf697-7009-ee39-bb84-b137eec030de.png)

# 参考文献・参考サイトまとめ

- [The Applied SQL Data Analytics Workshop: Develop your practical skills and prepare to become a professional data analyst, 2nd Edition](https://www.amazon.com/Applied-SQL-Data-Analytics-Workshop-ebook/dp/B085D91RNK/)
- [Data Types](https://prestodb.io/docs/current/language/types.html)
- [Map Functions and Operators](https://prestodb.io/docs/current/functions/map.html)
- [【Python】dictをfilterする](https://tombomemo.com/python-dict-filter/)
- [カーディナリティ 【cardinality】](https://e-words.jp/w/%E3%82%AB%E3%83%BC%E3%83%87%E3%82%A3%E3%83%8A%E3%83%AA%E3%83%86%E3%82%A3.html)
- [SQL で縦横変換まとめ（pivot と unpivot）](https://qiita.com/k24d/items/79bc4828c918dfeeac34)
- [PrestoのArray, Mapの使い方調査](https://qiita.com/tiny/items/5fe460e899ae65f9c86a)
- [[Amazon Athena]一見json配列に見えるvarcharのデータをパースして集計できる形式に変換する](https://dev.classmethod.jp/articles/amazon-athena-varchar-to-json-array-and-unnest/)
- [JSON Functions and Operators](https://prestodb.io/docs/current/functions/json.html)
- [JSONPath - XPath for JSON](https://goessner.net/articles/JsonPath/)