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

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;