Hive/PrestoでのJSONの展開方法

Hiveでのjsonの展開方法についてのメモ

こんなJSONを無邪気に入れてしまっていないだろうか。

[{"key":"k1","value":"v11"},{"key":"k2","value":"v12"}]

さてこのデータをこんな感じのテーブルに展開したいが、どうしたらいいのだろうか。配列で入ってればそのままExplodeすればいいが、Stringで格納されている場合はどうしたらいいのだろうか。

key	value
k1	v11
k2	v12

答えだけを知りたい人は以下を参照されたし。

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 
       get_json_object(single_json_table.single_json, '$.key') AS key,
       get_json_object(single_json_table.single_json, '$.value') AS value
FROM
(SELECT EXPLODE(split(regexp_replace(substr(json_array_col,2,LENGTH(json_array_col)-2),'\\},', '},,,,'),',,,,')) as single_json from source) single_json_table

何をやっているか

目的 : 複数のjson一行に格納されているため、1行あたり1JSONにする。 => EXPLODEを使う。

大体の場合こういったJSONはデータを格納することを第一目的にしているため、Stringになっている。残念ながら、そのままExplodeは使えない。ではどうするか？配列を返す関数をSplitを挟むのだ。

案１ : そのままSplitをする。

これを採用するとjsonでもarrayでもなにかが生成される。

SELECT EXPLODE(split(json_array_col,'\},')) as single_json from source

col1
[{"key":"k1","value":"v11"
{"key":"k2","value":"v12"}]

案２ [ ]を外して文字列にする。

1. 配列の [ ] を外す

SELECT substr(json_array_col,2,LENGTH(json_array_col)-2) as single_json from source

こんな感じの結果が返ってくる。

col1
{"key":"k1","value":"v11"},{"key":"k2","value":"v12"}

2. 分割(Split)したい箇所をSplitしやすいように,を,,にする。

SELECT regexp_replace(substr(json_array_col,2,LENGTH(json_array_col)-2),'\},', '},,') as single_json from source

なぜこんなことをするか。最終的にEXPLODEしたものはJSONとして取り回したいのだ。もし、この過程を無視してしまうと以下のようなデータが生成されてしまう。

col1
["{\"key\":\"k1\",\"value\":\"v11\"","{\"key\":\"k2\",\"value\":\"v12\"}"]

Splitの結果として当然ながら'},'は消失する。結果としてJSONとして扱えないデータにしかならないのだ。

そのためregexp_replaceを使って、別のメルクマークを作成するのだ。

3. Splitして配列にする。

SELECT split(regexp_replace(substr(json_array_col,2,LENGTH(json_array_col)-2),'\},', '},,'),',,') as single_json from source

こんな感じの結果になる。

col1
["{\"key\":\"k1\",\"value\":\"v11\"}","{\"key\":\"k2\",\"value\":\"v12\"}"]

ようやくほしい形の配列になった。

4. Explodeする。

SELECT EXPLODE(split(regexp_replace(substr(json_array_col,2,LENGTH(json_array_col)-2),'\},', '},,'),',,')) as single_json from source

結果はこうだ。

col1
{"key":"k1","value":"v11"}
{"key":"k2","value":"v12"}

あとは好きにJSONとして加工してくれればいい。