PostgreSQL で連想配列 (JSON 型) の値を配列で取得 #SQL

PostgreSQL に格納された JSON (or JSONB) カラムから連想配列 (object) の値だけを配列で取得したかったが JSON_OBJECT_KEYS はあるのに JSON_OBJECT_VALUES が無かった為、色々と試した時のメモ。

例えば下記のような JSONB 型のカラムがあるテーブルを用意する。

CREATE TABLE hoge (
    id SERIAL NOT NULL,
    data JSONB,
    PRIMARY KEY (id)
) ;

INSERT INTO hoge (data) VALUES ('{ "fuga": { "a": { "id": 1 }, "b": { "id": 2 }, "c": { "id": 3 } } }') ;
INSERT INTO hoge (data) VALUES ('{ "fuga": { "d": { "id": 4 }, "e": { "id": 5 }, "f": { "id": 6 } } }') ;
INSERT INTO hoge (data) VALUES ('{ "fuga": { "g": { "id": 7 }, "h": { "id": 8 }, "i": { "id": 9 } } }') ;

SELECT id, JSONB_PRETTY(data) FROM hoge ;
 id |    jsonb_pretty
----+---------------------
  1 | {                  +
    |     "fuga": {      +
    |         "a": {     +
    |             "id": 1+
    |         },         +
    |         "b": {     +
    |             "id": 2+
    |         },         +
    |         "c": {     +
    |             "id": 3+
    |         }          +
    |     }              +
    | }
  2 | {                  +
    |     "fuga": {      +
    |         "d": {     +
    |             "id": 4+
    |         },         +
    |         "e": {     +
    |             "id": 5+
    |         },         +
    |         "f": {     +
    |             "id": 6+
    |         }          +
    |     }              +
    | }
  3 | {                  +
    |     "fuga": {      +
    |         "g": {     +
    |             "id": 7+
    |         },         +
    |         "h": {     +
    |             "id": 8+
    |         },         +
    |         "i": {     +
    |             "id": 9+
    |         }          +
    |     }              +
    | }
(3 rows)

連想配列の key を配列で取得

連想配列 (object) の key を配列で取得したい場合は JSON_OBJECT_KEYS を使い、下記のように取得することができる。

SELECT id, JSON_OBJECT_KEYS((data->>'fuga')::JSON) AS k1 FROM hoge

 id | k1
----+----
  1 | a
  1 | b
  1 | c
  2 | d
  2 | e
  2 | f
  3 | g
  3 | h
  3 | i
(9 rows)

key ごとにレコードとしてバラけて取得しても使いづらいので、それぞれを配列に変換する。

SELECT id, ARRAY_TO_JSON(ARRAY_AGG(k1)) AS keys
FROM (
  SELECT id, JSON_OBJECT_KEYS((data->>'fuga')::JSON) AS k1 FROM hoge
) AS t1
GROUP BY id
ORDER BY id

 id |      keys
----+---------------
  1 | ["a","b","c"]
  2 | ["d","e","f"]
  3 | ["g","h","i"]
(3 rows)

連想配列の value を配列で取得

連想配列 (object) の value を配列で取得したい場合は JSON_OBJECT_KEYS の代わりとなる JSON_OBJECT_VALUES があれば良いのですが、何故か用意されていない為、色々と関数を駆使して実現を試みた結果が以下の通り。

SELECT id, JSON_EACH((data->>'fuga')::JSON) AS v1 FROM hoge ;

 id |         v1
----+-------------------
  1 | (a,"{""id"": 1}")
  1 | (b,"{""id"": 2}")
  1 | (c,"{""id"": 3}")
  2 | (d,"{""id"": 4}")
  2 | (e,"{""id"": 5}")
  2 | (f,"{""id"": 6}")
  3 | (g,"{""id"": 7}")
  3 | (h,"{""id"": 8}")
  3 | (i,"{""id"": 9}")
(9 rows)

SELECT id, ROW_TO_JSON(JSON_EACH((data->>'fuga')::JSON)) AS v1 FROM hoge ;

 id |              v1
----+-------------------------------
  1 | {"key":"a","value":{"id": 1}}
  1 | {"key":"b","value":{"id": 2}}
  1 | {"key":"c","value":{"id": 3}}
  2 | {"key":"d","value":{"id": 4}}
  2 | {"key":"e","value":{"id": 5}}
  2 | {"key":"f","value":{"id": 6}}
  3 | {"key":"g","value":{"id": 7}}
  3 | {"key":"h","value":{"id": 8}}
  3 | {"key":"i","value":{"id": 9}}
(9 rows)

SELECT id, ROW_TO_JSON(JSON_EACH((data->>'fuga')::JSON))->>'value' AS v1 FROM hoge ;

 id |    v1
----+-----------
  1 | {"id": 1}
  1 | {"id": 2}
  1 | {"id": 3}
  2 | {"id": 4}
  2 | {"id": 5}
  2 | {"id": 6}
  3 | {"id": 7}
  3 | {"id": 8}
  3 | {"id": 9}
(9 rows)

SELECT id, ARRAY_TO_JSON(ARRAY_AGG(v1::JSON)) AS values
FROM (
  SELECT id, ROW_TO_JSON(JSON_EACH((data->>'fuga')::JSON))->>'value' AS v1 FROM hoge
) AS t1
GROUP BY id
ORDER BY id

 id |             values
----+---------------------------------
  1 | [{"id": 1},{"id": 2},{"id": 3}]
  2 | [{"id": 4},{"id": 5},{"id": 6}]
  3 | [{"id": 7},{"id": 8},{"id": 9}]
(3 rows)