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

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

More than 1 year has passed since last update.

最近、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)
  1. まずは、複数レコードでJSON配列のデータが格納されているので、このJSON配列をjsonb_array_elements()でJSON値の集合に展開する。
  2. 次にarray_agg()で取得したJSON値の集合を配列として結合する。
  3. このままだと、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. 未着手

今後、追記しよう。

おわり。

U_ikki
PostgreSQL歴は5年ほど。PostgreSQLはいいぞ!
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