先日の第7回PostgreSQLアンカンファレンス@東京で紹介した件です。いろんな方法でJSONスキャンできると思いますが、こんな風にもという参考まで。JSON・JSONBどちらも可、ただし微妙に違う点が。PostgreSQL 9.5.3で動作確認しました。
【JSON】
with recursive r (key, j1) as (
select null :: text, '[ 1, 2, 3, { "f1": 1, "f2": [5, 6] }, 4 ]'
:: json -- ↑ arbitrary JSON
union all -- not only union
select k, j2
from r,
json_typeof(j1) as t,
cast(case when t = 'object' then
json_object_keys(j1)
end as text) as k,
cast(case when k is not null then
json_extract_path(j1, k)
when t = 'array' then
json_array_elements(j1) end as json) as j2
where j2 is not null
)
select * from r;
+-----+-------------------------------------------+
| key | j1 |
+-----+-------------------------------------------+
| | [ 1, 2, 3, { "f1": 1, "f2": [5, 6] }, 4 ] |
| | 1 |
| | 2 |
| | 3 |
| | { "f1": 1, "f2": [5, 6] } |
| | 4 |
| f1 | 1 |
| f2 | [5, 6] |
| | 5 |
| | 6 |
+-----+-------------------------------------------+
(10 rows)
【JSONB】
with recursive r (key, j1) as (
select null :: text, '
{ "f2": { "f3": 1 }, "f4": { "f5": 99, "f6": "foo" } }
' :: jsonb -- ↑ arbitrary JSONB
union
select k, j2
from r,
jsonb_typeof(j1) as t,
cast(case when t = 'object' then
jsonb_object_keys(j1)
end as text) as k,
cast(case when k is not null then
jsonb_extract_path(j1, k)
when t = 'array' then
jsonb_array_elements(j1) end as jsonb) as j2
where j2 is not null
)
select * from r;
+-----+--------------------------------------------------+
| key | j1 |
+-----+--------------------------------------------------+
| | {"f2": {"f3": 1}, "f4": {"f5": 99, "f6": "foo"}} |
| f2 | {"f3": 1} |
| f4 | {"f5": 99, "f6": "foo"} |
| f3 | 1 |
| f5 | 99 |
| f6 | "foo" |
+-----+--------------------------------------------------+
(6 rows)
使ってる関数は下記四つ、いずれも9.4で追加されました。だから9.4でも動くと思います、まだ確認してませんが。
json(b)_typeof
json(b)_object_keys
json(b)_extract_path
json(b)_array_elements
JSONとJSONBで微妙に違うのは、前者が等値比較できないのでunionが使えず、union all必須な点。unionは縦に結合する際、各列が等しいか見て、どの列も同じ行があれば統合するので。
WITH RECURSIVEでは、unionによる「同じ行の統合」が鍵になるケースが多いです。統合の結果、新しい行が出現しなければループを抜けるという。でも今回はスキャンするオブジェクトが尽きた時点で終わるので、無関係でした。
アンカンファレンスでデモした、もっと複雑なJSONの場合等は今後追加します。もしかしたら今回のが不十分だと判明するかも ^^;