從 JSON 物件提取資料

JSONB 超強，可以存很複雜的資料結構，像是巢狀物件或 array 都沒問題。但光是把資料塞進 JSONB 還不夠，我們還要會怎麼把資料撈出來。舉例來說，假設你有個 data 欄位在 users 這張表裡，所有使用者設定都用 JSONB 存著。想知道使用者選了什麼主題？就要從 JSONB 物件裡面把它挖出來。

如果說 JSONB 是個寶箱，那 ->、->>、#>> 這些 operators 跟像 jsonb_extract_path() 這種 function，就是你的鑰匙。來看看怎麼用這些工具吧！

JSONB 基本操作符

PostgreSQL 給你幾個很重要的 operators 來玩 JSONB。這些可以讓你從 key、巢狀物件或 array 裡面抓值。下面這幾個最常用：

Operator ->

-> 這個 operator 會根據你給的 key 抓出 物件或 array。如果你想要拿到跟 JSON 一樣格式的值，就用這個 operator。

範例：

-- 範例資料
SELECT '{"name": "Alice", "age": 25}'::jsonb -> 'name';
-- 結果: "Alice"

Operator ->>

->> 跟 -> 很像，但它會把抓到的值變成 純文字。如果你想要簡單的文字結果就用這個。

範例：

-- 範例資料
SELECT '{"name": "Alice", "age": 25}'::jsonb ->> 'age';
-- 結果: "25"（字串）

Operator #>>

#>> 這個 operator 可以根據你給的路徑（用 key 組成的 array）去巢狀物件裡面抓資料。

範例：

-- 範例資料
SELECT '{"user": {"name": "Bob", "details": {"age": 30}}}'::jsonb #>> '{user, details, age}';
-- 結果: "30"（字串）

-> 跟 ->> 的差別：

如果你想保留資料型態（像 array 或物件），就用 ->。如果你只要文字，用 ->>。

JSONB 的 function 用法

jsonb_extract_path() 這個 function 可以根據你給的路徑，從 JSONB 物件裡面抓值。它跟 #>> operator 很像，但語法比較直覺。

範例：

SELECT jsonb_extract_path('{"user": {"name": "Alice", "settings": {"theme": "dark"}}}'::jsonb, 'user', 'settings', 'theme');
-- 結果: "dark"

如果你想直接拿到文字結果，可以用 jsonb_extract_path_text()。它跟 jsonb_extract_path() 一樣，但回傳字串。

範例：

SELECT jsonb_extract_path_text('{"user": {"name": "Alice", "settings": {"theme": "dark"}}}'::jsonb, 'user', 'settings', 'theme');
-- 結果: dark

實戰範例

根據 key 抓值。 假設我們有一張 products 表，details 欄位存的是 JSONB：

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    details JSONB
);

INSERT INTO products (name, details) VALUES
    ('Laptop', '{"brand": "Dell", "price": 1200, "specs": {"ram": "16GB", "cpu": "Intel i7"}}'),
    ('Phone', '{"brand": "Apple", "price": 1000, "specs": {"ram": "4GB", "cpu": "A13"}}');

結果：

抓出所有產品的品牌。

SELECT name, details->'brand' AS brand FROM products;

結果：

抓出純文字。 如果你想要沒有引號的品牌，用 ->> operator：

SELECT name, details->>'brand' AS brand FROM products;

結果：

抓巢狀資料。 來抓每個產品的記憶體（ram）：

SELECT name, details#>>'{specs, ram}' AS ram FROM products;

結果：

用路徑抓資料。 也可以用 jsonb_extract_path_text() function 做一樣的事：

SELECT name, jsonb_extract_path_text(details, 'specs', 'ram') AS ram FROM products;

結果：

常見錯誤跟怎麼避免

常見錯誤有：

• 你用錯路徑去抓資料。像是 key 不存在，結果就會是 null。
• 用錯 operator。-> 適合抓物件或 array，要抓文字就要用 ->>。

錯誤範例：

-- 錯誤：沒有 'nonexistent' 這個 key
SELECT details->>'nonexistent' FROM products;
-- 結果: null

小建議：寫 query 前先檢查一下資料結構，這樣比較不會出錯。

實際應用場景

JSONB 資料提取在很多實際應用都用得到：

• 在電商網站處理商品規格。
• 在 web app 存使用者設定。
• 在分析時處理結構化資料，像事件或 log。

再給你一個例子。假設有張 orders 表，裡面存訂單資料：

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

INSERT INTO orders (customer_name, items) VALUES
    ('John', '[{"product": "Laptop", "quantity": 1}, {"product": "Mouse", "quantity": 2}]'),
    ('Alice', '[{"product": "Phone", "quantity": 1}]');

來抓出所有訂單裡的產品名稱：

SELECT customer_name, jsonb_array_elements(items)->>'product' AS product FROM orders;

結果：

接下來我們會更深入玩 JSONB，研究巢狀資料還有怎麼把它們轉成更好分析的格式。準備好發現更多有趣的東西吧！