PostgreSQLでのJSONBデータの使い方（展開、結合、集約）

はじめに

PostgreSQLでJSONBデータを扱う時の、よく使う方法のメモ。
基本的には以下のマニュアルの通りだが、備忘録として。

[9.15. JSON関数と演算子]
(https://www.postgresql.jp/document/9.4/html/functions-json.html)

サンプルデータ１（単一データ）

まずは、単一のユーザ情報をJSONB形式で保持するサンプルテーブルを用意。

CREATE TABLE user_info (id serial, info jsonb);
INSERT INTO user_info (id, info) VALUES (1, '{"name": "user1-1", "age": 20}');
INSERT INTO user_info (id, info) VALUES (2, '{"name": "user1-2", "age": 21}');
INSERT INTO user_info (id, info) VALUES (3, '{"name": "user2-1", "age": 22}');
INSERT INTO user_info (id, info) VALUES (4, '{"name": "user2-2", "age": 23}');

postgres=# SELECT * FROM user_info;
 id |              info              
----+--------------------------------
  1 | {"age": 20, "name": "user1-1"}
  2 | {"age": 21, "name": "user1-2"}
  3 | {"age": 22, "name": "user2-1"}
  4 | {"age": 23, "name": "user2-2"}
(4 行)

フィールドの抽出

特定のフィールドだけを抽出したいときは、->> 演算子を使用する。

postgres=# SELECT *, info->>'name' user_name, info->>'age' user_age
postgres-#  FROM user_info
postgres-#  WHERE (info->>'age')::int > 20;
 id |              info              | user_name | user_age 
----+--------------------------------+-----------+----------
  2 | {"age": 21, "name": "user1-2"} | user1-2   | 21
  3 | {"age": 22, "name": "user2-1"} | user2-1   | 22
  4 | {"age": 23, "name": "user2-2"} | user2-2   | 23
(3 行)

※ 「info->>'name'」のように、フィールド名にシングルクォーテーションが必要。
※ JSONデータは型を持っていないため、取り出した後でWHERE句の判定などで使用する場合は、「(info->>'age')::int」のように型指定する必要がある。
※ カラム名を指定しないと「?column?」になるようなので、別名を付けたほうがよい。

サンプルデータ２（複数データ）

次に、複数のユーザ情報のIDを配列で保持する、「ユーザグループ」を用意。

postgres=# CREATE TABLE user_group (id serial, name varchar(50), users jsonb);
postgres=# INSERT INTO user_group (id, name, users ) VALUES (1, 'Group1', '[1, 2]');
postgres=# INSERT INTO user_group (id, name, users ) VALUES (2, 'Group2', '[3, 4]');

postgres=# SELECT * FROM user_group;
 id |  name  | users  
----+--------+--------
  1 | Group1 | [1, 2]
  2 | Group2 | [3, 4]
(2 行)

展開

JSONBデータを行データに展開したい場合は、jsonb_array_elements と CROSS JOIN を使用する。
以下のように、ユーザID（1,2,3,4）を縦並びにできる。

postgres=# SELECT user_group.*, users.value user_id FROM user_group
 CROSS JOIN LATERAL jsonb_array_elements (user_group.users) users;
 id |  name  | users  | user_id 
----+--------+--------+---------
  1 | Group1 | [1, 2] | 1
  1 | Group1 | [1, 2] | 2
  2 | Group2 | [3, 4] | 3
  2 | Group2 | [3, 4] | 4
(4 行)

※ フィールド名のないJSONデータはデフォルトで「value」となるようなので、注意。
　　上記では、「users.value user_id」のように別名を付けている。

結合

ユーザ情報とユーザグループを結合したい場合は、上記で展開した「ユーザグループ」の行データと「ユーザ情報」を JOIN で結合する。

postgres=# WITH users AS (
postgres(#  SELECT user_group.*, users.value user_id FROM user_group
postgres(#  CROSS JOIN LATERAL jsonb_array_elements (user_group.users) users
postgres(# )
postgres-# SELECT users.id group_id, users.name, users.users
postgres-# , user_info.id user_id, user_info.info
postgres-#  FROM users
postgres-#  INNER JOIN user_info ON (users.user_id)::int = user_info.id
postgres-# ORDER BY user_info.id;
 group_id |  name  | users  | user_id |              info              
----------+--------+--------+---------+--------------------------------
        1 | Group1 | [1, 2] |       1 | {"age": 20, "name": "user1-1"}
        1 | Group1 | [1, 2] |       2 | {"age": 21, "name": "user1-2"}
        2 | Group2 | [3, 4] |       3 | {"age": 22, "name": "user2-1"}
        2 | Group2 | [3, 4] |       4 | {"age": 23, "name": "user2-2"}
(4 行)

集約

ユーザ情報とユーザグループを結合した上で、元の「ユーザグループ」と同じ構成に戻したい場合は、GROUP BY で集約する。
行データをJSON形式に配列に集約したい場合は、array_to_json、array_agg を使用する。

postgres=# WITH users AS (
postgres(#  SELECT user_group.*, users.value user_id FROM user_group
postgres(#  CROSS JOIN LATERAL jsonb_array_elements (user_group.users) users
postgres(# )
postgres-# , user_info_group AS (
postgres(#  SELECT users.id group_id, users.name, users.users
postgres(#  , user_info.id user_id, user_info.info
postgres(#  FROM users
postgres(#  INNER JOIN user_info ON (users.user_id)::int = user_info.id
postgres(#  ORDER BY user_info.id
postgres(# )
postgres-# SELECT group_id, name, array_to_json(array_agg(user_info_group.info)) info 
postgres-#  FROM user_info_group
postgres-#  GROUP BY group_id, name
postgres-#  ORDER BY group_id;
 group_id |  name  |                              info                               
----------+--------+-----------------------------------------------------------------
        1 | Group1 | [{"age": 20, "name": "user1-1"},{"age": 21, "name": "user1-2"}]
        2 | Group2 | [{"age": 22, "name": "user2-1"},{"age": 23, "name": "user2-2"}]
(2 行)

※ JSONデータの中もソートしたい場合は、集約する前にソートする。
　上記の場合、user_info_group のテーブルを生成する時に「ORDER BY user_info.id」としている。