Help us understand the problem. What is going on with this article?

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

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)
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした