athenaでjsonへのselectを攻略する

athenaでjsonのカラムから値を抽出したい

必要な時にさっとクエリを書けるようにしたいので、よく使うパターンを例と共にまとめる。

with句について

with 名前 as (select文)

として実行することで、付けた名前のテーブルのようにクエリが書ける。
この記事の例ではテーブルを作ってデータを入れる代わりに、with句を使って一時的なテーブルを作ってクエリを試していく。
with句と実行したいselect文を一緒に実行すればクエリを試すことができる。

ユースケース①: jsonの内容を同じ行に展開したい場合

key-valueで値の入ったjsonのカラムから値を取得したい場合のユースケース

クエリの書き方

json_extract_scalarかjson_extractを使う。
配列やmapの抽出にはjson_extract_scalarは使えないので、代わりにjson_extractを使う。

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

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

例A: keyでvalueを取り出す

使用するデータセット例

some_mapにkey-valueでjsonを入れてある

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

実行結果

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

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

クエリ

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

実行結果

name	count	role	location
john	12345	engineer	Tokyo

例B: jsonから配列を取り出す

1つのjson1カラムのみのデータにおいて、配列の要素を取り出してみる。
json_extract_scalarは使わずjson_extractを使う

使用するデータセット例

クエリ

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

実行結果

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

jsonの配列を取り出すクエリ

SELECT json_extract(blob, '$.projects') AS item
FROM dataset

実行結果

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

例C: 配列からN番目の要素を取り出す

json_array_get({配列}, {index}) で取り出すことができる

使用するデータセット例

クエリ

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

select * from dataset

実行結果

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

配列の0番目の要素を取り出す

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

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

ユースケース②:jsonのカラムの内容を複数行に展開したい場合

配列で入っているjsonのカラムの内容を展開したい場合のユースケース

クエリの書き方

unsetを使用する。
ぱっと見で理解し難いので例を参考にして欲しい。

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

使用するデータセット例

peopleという1カラムに、jsonが配列で入っている

クエリ

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

実行結果

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を取得する。

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

実行結果

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