Zenn
Closed1

AthenaのJSONカラムをkey valueに展開する

athena
marushomarusho

AthenaでJSONに含まれるkeyの数をcountしたい場面で詰まったので備忘録です。

JSONをkeyとvalueに展開してからcountするSQLを作成しました。

WITH params_table AS (
   SELECT id,
          params,
          key,
          element_at(map_table.param, key) AS value
   FROM
     (SELECT id,
             params,
             CAST(json_parse(params) AS MAP(varchar, varchar)) AS param
      FROM sample_table ) AS map_table
   CROSS JOIN UNNEST (map_keys(map_table.param)) AS param(key)
  )

SELECT key,
       count(*)
FROM params_table
GROUP BY key
ORDER BY count(*) DESC;
  • Sampleテーブル
id params
1 {"id": 100, "page": 0, "name": "sample1"}
2 {"id": 200, "name": "sample2", "type": 0}
3 {"id": 300, "name": "sample3", "age": 20}
  • JSONを分解する
id params key value
1 {"id": 100, "page": 0, "name": "sample1"} id 100
1 {"id": 100, "page": 0, "name": "sample1"} page 0
1 {"id": 100, "page": 0, "name": "sample1"} name sample1
2 {"id": 200, "name": "sample2", "type": 0} id 200
2 {"id": 200, "name": "sample2", "type": 0} name sample2
2 {"id": 200, "name": "sample2", "type": 0} type 0
3 {"id": 300, "name": "sample3", "age": 20} id 300
3 {"id": 300, "name": "sample3", "age": 20} name sample3
3 {"id": 300, "name": "sample3", "age": 20} age 20
  • カウントする
key count(*)
id 3
name 2
page 1
type 1
age 1
このスクラップは2022/07/29にクローズされました