PostgreSQL

PostgreSQLでjsonb_to_recordset()を使ってJSONをレコードに展開する

やりたかったこと

JSONBの項目に配列として入っているオブジェクトの値を検索してみたり、結合条件にしてみたりしたかった。

JSONBの内容例

カラム名: fruits

[
  {"code": "apple", "name": "りんご"},
  {"code": "orange", "name": "オレンジ"}, 
  {"code": "banana", "name": "バナナ"}
]

実現方法

jsonb_to_recordset() 関数を使えば良いようだ。
ちょっと書き方が独特だけど下記のように書く。

使い方

select 
  *
from 
  [table_name], 
  jsonb_to_recordset([column_name]) 
    as x(
      [jsonKey_1] [type_1],
      [jsonKey_2] [type_2],
      ,
      [jsonkey_n] [type_n]
    );

具体例

DB内容

テーブル名: oyatu

id owner fruits
1 [{"code": "apple", "name": "りんご"}]
2 あいつ [{"code": "grape", "name": "ぶどう"},{"code": "banana", "name": "バナナ"}]
3 そいつ [{"code": "apple", "name": "りんご"},{"code": "orange", "name": "オレンジ"}, {"code": "banana", "name": "バナナ"}]

SQL

select 
  id,owner,code,name
from 
  oyatu, 
  jsonb_to_recordset(fruits) 
    as x(
      code text,
      name text
    );

実行結果

id owner code name
1 apple りんご
2 あいつ grape ぶどう
2 あいつ banana バナナ
3 そいつ apple りんご
3 そいつ orange オレンジ
3 そいつ banana バナナ

雑記

今までDBへJSON形式でデータを入れる事が無かったので、こんなことも出来るんだなぁ…と。