Hiveでfirebaseのjsonをきれいに整形したいときのクエリ。

Hiveでfirebaseのjsonをきれいに整形したいときのクエリ.md

1. 一回無理やり、連結する。
2. 連結後、配列にする。
3. 配列をexplodeする。
4. そのテーブルにクエリを投げる。

WITH source AS (
SELECT 'KEY001' AS key_col, '[{"key":"k1","value":"v11"},{"key":"k2","value":"v12"}]' AS json_array_col
UNION ALL
SELECT 'KEY002' AS key_col, '[{"key":"k2","value":"v22"},{"key":"k3","value":"v23"}]' AS json_array_col
UNION ALL
SELECT 'KEY003' AS key_col, '[{"key":"k1","value":"v31"},{"key":"k3","value":"v33"}]' AS json_array_col
)

SELECT key_col,
GET_JSON_OBJECT(single_json, '$.key') as key,
GET_JSON_OBJECT(single_json,'$.value') as value
from
(
SELECT key_col,single_json from source LATERAL VIEW
EXPLODE(split(regexp_replace(substr(json_array_col,2,LENGTH(json_array_col)-2),'\\},', '},,,,'),',,,,')) single_json_table as single_json) explode_fllaten_json
ORDER BY key_col