最近、JSON型のデータの扱いを聞かれて、すぐに回答できなかったので少し使ってみる。（何なら今までJSON型、JSONB型のデータを扱ったDBと縁がなかったので、勉強をサボっていた（汗））

非効率なことをしていたら、ツッコミをください。

本書で書くこと

JSONB型で格納されたデータを一つのJSON配列に結合するためのSQL
（今後、徐々に追記していくつもり）

環境情報

PostgreSQL 11

1. JSONB型で格納されたデータを一つのJSON配列に結合

やりたいこと

任意の複数行に存在するJSON配列を一つのJSON配列にまとめる。

例として、以下のテーブル定義とデータが格納されたテーブルを使う。

postgres=# \d bar
                Table "public.bar"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 
 value  | jsonb   |           |          | 

postgres=# select * from bar;
 id |                           value                            
----+------------------------------------------------------------
  2 | [{"sample11": "text01"}, {"sample12": "text02"}]
  2 | [{"sample13": "text03"}, {"sample14": {"memo": "text04"}}]
  1 | [{"sample01": "text01"}, {"sample02": "text02"}]
  1 | [{"sample03": "text03"}, {"sample04": "text04"}]
(4 rows)

これでできそう

postgres=# WITH elements as (select jsonb_array_elements(value) as v  from bar where id =2) select to_jsonb(array_agg(v)) from elements;
                                                 to_jsonb                                                 
---------------------------------------------------------------------------------------------------------
 [{"sample11": "text01"},{"sample12": "text02"},{"sample13": "text03"},{"sample14": {"memo": "text04"}}]
(1 row)

まずは、複数レコードでJSON配列のデータが格納されているので、このJSON配列をjsonb_array_elements()でJSON値の集合に展開する。
次にarray_agg()で取得したJSON値の集合を配列として結合する。
このままだと、TEXT型のデータ（バックスラッシュでダブルクォートがエスケープされた形）になっているので、to_json()でJSONB型のデータとして出力する

ちなみに今回はJSONB型でやっているけど、もちろんJSON型でも問題ない。その場合はjson_array_elements(json), to_json()を使う必要がある。

これで、まとめたJSON配列新たなレコードとして登録したりもできる。

postgres=# WITH elements as (select jsonb_array_elements(value) as v  from bar where id =2) INSERT INTO bar(id, value) select 3, to_jsonb(array_ag
g(v)) from elements;
INSERT 0 1
postgres=# select * from bar;
 id |                                                   value                                                    
----+------------------------------------------------------------------------------------------------------------
  2 | [{"sample11": "text01"}, {"sample12": "text02"}]
  2 | [{"sample13": "text03"}, {"sample14": {"memo": "text04"}}]
  1 | [{"sample01": "text01"}, {"sample02": "text02"}]
  1 | [{"sample03": "text03"}, {"sample04": "text04"}]
  3 | [{"sample11": "text01"}, {"sample12": "text02"}, {"sample13": "text03"}, {"sample14": {"memo": "text04"}}]
(5 rows)

2. 未着手

今後、追記しよう。

おわり。

PostgreSQLでJSONデータを扱うには（徐々に追記していく予定）