先日の第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の場合等は今後追加します。もしかしたら今回のが不十分だと判明するかも^^;