ネストされたデータの取り出し: jsonb_to_recordset()

ここからは、JSONB形式のデータをもっと深く扱うシナリオ、つまりネストされたデータを取り出してテーブルの行に変換する方法に進むよ。なんでそんなことするの？って思うかもだけど、めっちゃ便利なんだ。例えば、買い物リストのJSONオブジェクトを渡されて、全部の合計金額を出したり、レポート用にテーブル形式で表示したいときとか。今から一緒にやってみよう！

なんでJSONをそのままテキストや構造体として扱うだけじゃダメなの？って疑問もあるよね。実際、多くのアプリではデータがJSONの配列で保存されてることが多いんだ：

[
  { "id": 1, "product_name": "Laptop", "price": 1200 },
  { "id": 2, "product_name": "Smartphone", "price": 800 },
  { "id": 3, "product_name": "Tablet", "price": 400 }
]

これ、見た目は便利だけど、データ分析のときは配列をテーブルに変換して、フィルタやソート、集計とかしたくなるよね。例えば「500ドル以上の注文だけ見たい」とか。JSONBだけだと、こういうのがサクッとできない。そこでjsonb_to_recordset()の出番！

jsonb_to_recordset()の使い方

jsonb_to_recordset()関数は、JSONBオブジェクトの配列をテーブルの行に変換してくれる。配列の各要素が行になって、キーがカラムになる感じ。この関数は、データが深くネストされてたり、オブジェクトの配列が入ってるときに超便利！

シンタックス

SELECT *
FROM jsonb_to_recordset('[ JSONB配列 ]') AS alias(カラム1 型, カラム2 型, ...);

• [ JSONB配列 ]: データを取り出したいJSONオブジェクトの配列。
• AS alias: 結果テーブルの一時的な名前をつける。
• カラム1 型, カラム2 型: カラム名とデータ型を指定する（例：INTEGER、TEXT、NUMERIC）。

例：JSONB配列を行に変換する

例えば、こんなテーブルがあるとする：

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_name TEXT,
    products JSONB
);

テーブルにはこんなデータが入ってる：

ここで、「全ての注文の全商品をテーブル形式で一覧にしたい」ってときは、jsonb_to_recordset()をこう使う：

SELECT
    o.id AS order_id,
    o.customer_name,
    p.id AS product_id,
    p.product_name,
    p.price
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC);

結果：

例：データのフィルタリング

もうちょい難しくしてみよう。今度は「100ドル以上の商品だけ表示したい」場合：

SELECT
    o.id AS order_id,
    o.customer_name,
    p.id AS product_id,
    p.product_name,
    p.price
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC)
WHERE
    p.price > 100;

結果：

例：データの集計

じゃあ、全注文の商品の合計金額を出したいときは？集計関数を使えばOK：

SELECT
    o.customer_name,
    SUM(p.price) AS total_amount
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC)
GROUP BY
    o.customer_name;

結果：

大事なポイント

JSON配列の構造は全オブジェクトで同じにしておこう。もしキーが違ったり、ネスト構造がバラバラだと、エラーや予期しない動作になることがあるよ。

取り出すカラムのデータ型はちゃんと指定しよう。例えば日付ならDATE、数字ならNUMERICやINTEGERを使う。

jsonb_to_recordset()はJSONBの配列だけを変換できる。単一オブジェクトには使えないから注意！

よくあるミスとその対策

データ型の間違い: JSONB配列の値が型バラバラ（例えば数字のはずが文字列）だとエラーになる。関数を使う前にデータをちゃんと整形しよう。

キーの指定ミス: 配列の中のどれかにキーが無いとエラーになる。クエリ実行前にデータ構造をチェックしよう。

データが無い場合: JSONBカラムが空（NULL）だと、関数は何も返さない。そんなときはCOALESCE()とかでチェックを入れよう。

実践での使いどころ

jsonb_to_recordset()は、実際の現場でもよく使われてる。注文処理、レポート分析、ユーザーアクションのログ、外部APIのデータ処理とか、いろんな場面で活躍！例えば：

• ネットショップなら、商品配列をテーブル化してレポート作成がラクラク。
• REST APIがJSONでデータ返してきても、PostgreSQLでサクッと分析できる。
• 分析系アプリでも、複雑な多階層データの処理にこの関数が大活躍！