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形式でデータを入れる事が無かったので、こんなことも出来るんだなぁ…と。