CodeGym /課程 /SQL SELF /抽取巢狀資料： jsonb_to_recordset()

抽取巢狀資料： `jsonb_to_recordset()`

SQL SELF

等級 33 , 課堂 3

開放

接下來我們要進階一點，來聊聊怎麼處理 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() 這個 function 可以把 JSONB 物件陣列轉成資料表的列。它會把每個陣列元素變成一列，key 變成欄位。這 function 超適合用在資料很巢狀或有一堆物件陣列的情境。

語法

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
);

表裡面有這些資料：

id	customer_name	products
1	John	`[{"id":1, "product_name":"Laptop", "price":1200}, {"id":2, "product_name":"Mouse", "price":50}]`
2	Alice	`[{"id":3, "product_name":"Smartphone", "price":800}, {"id":4, "product_name":"Charger", "price":30}]`

現在任務來了：要把所有訂單裡的產品都列出來。用 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);

結果會長這樣：

order_id	customer_name	product_id	product_name	price
1	John	1	Laptop	1200
1	John	2	Mouse	50
2	Alice	3	Smartphone	800
2	Alice	4	Charger	30

範例：資料篩選

再來進階一點，只想看那些價格超過 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;

結果：

order_id	customer_name	product_id	product_name	price
1	John	1	Laptop	1200
2	Alice	3	Smartphone	800

範例：資料聚合

想算每個人訂單裡產品的總金額？直接用聚合函數搞定：

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;

結果：

customer_name	total_amount
John	1250
Alice	830

重要提醒

記得 JSON 陣列裡每個物件的結構要一樣。如果有的物件 key 不同或巢狀結構不一樣，可能會出錯或結果怪怪的。

欄位型別要設對。例如 key 是日期就用 DATE，數字就用 NUMERIC 或 INTEGER。

注意 jsonb_to_recordset() 只能處理 JSONB 陣列，單一物件沒辦法用這招。

常見錯誤跟預防方法

型別用錯：如果 JSONB 陣列裡有的值型別不一樣（像是本來要數字結果給字串），就會出錯。建議先把資料轉成正確格式再用 function。

key 寫錯：如果某個物件裡沒有那個 key，也會出錯。查查資料結構再下 query。

沒資料：如果 JSONB 欄位是 NULL，function 會沒結果。這種情況可以加 COALESCE() 來處理。

實戰應用

jsonb_to_recordset() 在實務上超常用，像是訂單處理、報表分析、用戶行為紀錄、外部 API 資料處理都會用到。例如：

抽取巢狀資料： jsonb_to_recordset()

怎麼用 jsonb_to_recordset()