跳至主要內容
LinPolly
文章 程式開發

PostgreSQL JSON 操作實戰：jsonb 查詢與索引

PostgreSQL JSON 操作實戰：jsonb 查詢與索引

現代應用常常需要儲存半結構化的資料——使用者偏好設定、第三方 API 回應、動態表單欄位。你可能很想直接用 MongoDB，但其實 PostgreSQL 的 JSON 支援已經相當成熟，很多場景根本不需要換資料庫。

PostgreSQL 提供兩種 JSON 型別：json 和 jsonb。幾乎所有情況都應該用 jsonb：它以二進位格式儲存，支援索引，查詢效能遠優於 json。

基本 CRUD

建立含 JSON 欄位的表

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    attributes JSONB,  -- 動態屬性
    metadata JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 插入資料
INSERT INTO products (name, price, attributes, metadata) VALUES
(
    'MacBook Pro 16"',
    65900,
    '{
        "brand": "Apple",
        "specs": {
            "cpu": "M3 Pro",
            "ram": 36,
            "storage": 512
        },
        "colors": ["太空黑", "銀色"],
        "in_stock": true
    }',
    '{"views": 1520, "source": "import"}'
),
(
    'iPhone 16 Pro',
    36900,
    '{
        "brand": "Apple",
        "specs": {
            "chip": "A18 Pro",
            "storage": 256,
            "camera": "48MP"
        },
        "colors": ["原色鈦金屬", "白色鈦金屬", "黑色鈦金屬"],
        "in_stock": true
    }',
    '{"views": 3200, "source": "manual"}'
);

讀取 JSON 資料

-- -> 回傳 JSON 物件，->> 回傳文字
SELECT
    name,
    attributes -> 'brand' AS brand_json,        -- "Apple" (JSON)
    attributes ->> 'brand' AS brand_text,       -- Apple (text)
    attributes -> 'specs' -> 'ram' AS ram_json, -- 36 (JSON)
    (attributes -> 'specs' ->> 'ram')::int AS ram_int  -- 36 (integer)
FROM products;

-- 取得陣列元素
SELECT
    name,
    attributes -> 'colors' -> 0 AS first_color,  -- 陣列第一個元素
    attributes -> 'colors' ->> 1 AS second_color
FROM products;

-- jsonb_array_elements：展開陣列
SELECT
    name,
    jsonb_array_elements_text(attributes -> 'colors') AS color
FROM products;

查詢操作符

-- @> 包含（左邊是否包含右邊）
SELECT name FROM products
WHERE attributes @> '{"brand": "Apple"}';

-- ? 鍵是否存在
SELECT name FROM products
WHERE attributes ? 'in_stock';

-- ?| 任一鍵存在
SELECT name FROM products
WHERE attributes ?| ARRAY['in_stock', 'discontinued'];

-- ?& 所有鍵都存在
SELECT name FROM products
WHERE attributes ?& ARRAY['brand', 'specs', 'colors'];

-- #> 使用路徑取值
SELECT
    name,
    attributes #> '{specs, ram}' AS ram,
    attributes #>> '{specs, cpu}' AS cpu
FROM products;

修改 JSON 資料

-- jsonb_set：修改特定路徑的值
UPDATE products
SET attributes = jsonb_set(
    attributes,
    '{specs, ram}',   -- 路徑
    '64'::jsonb,      -- 新值
    true              -- 如果路徑不存在是否建立
)
WHERE name = 'MacBook Pro 16"';

-- || 合併（Merge）
UPDATE products
SET metadata = metadata || '{"updated_at": "2026-02-14", "editor": "alice"}'::jsonb
WHERE name = 'MacBook Pro 16"';

-- 移除鍵
UPDATE products
SET attributes = attributes - 'in_stock'  -- 移除 in_stock 鍵
WHERE id = 'some-uuid';

-- 移除陣列中的元素
UPDATE products
SET attributes = attributes #- '{colors, 0}'  -- 移除 colors 陣列的第一個元素
WHERE id = 'some-uuid';

索引策略

這是使用 jsonb 的關鍵優勢：

-- GIN 索引：支援 @>、?、?|、?& 操作
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

-- 部分 GIN 索引（只索引特定路徑）
CREATE INDEX idx_products_attributes_jsonb_path ON products
USING GIN (attributes jsonb_path_ops);

-- B-tree 索引：針對特定路徑的相等查詢
CREATE INDEX idx_products_brand ON products
USING BTREE ((attributes ->> 'brand'));

-- 函式索引：針對型別轉換後的查詢
CREATE INDEX idx_products_ram ON products
USING BTREE (((attributes -> 'specs' ->> 'ram')::int));

查詢效能驗證：

EXPLAIN ANALYZE
SELECT * FROM products
WHERE attributes @> '{"brand": "Apple", "in_stock": true}';
-- 使用 GIN 索引，O(log n) 查詢

JSONPath 查詢（PostgreSQL 12+）

JSONPath 提供更強大的查詢語法：

-- jsonb_path_query
SELECT jsonb_path_query(
    attributes,
    '$.specs.ram'
) AS ram
FROM products;

-- jsonb_path_exists：條件查詢
SELECT name FROM products
WHERE jsonb_path_exists(
    attributes,
    '$.colors[*] ? (@ == "太空黑")'
);

-- 取得特定條件的陣列元素
SELECT
    name,
    jsonb_path_query_array(
        attributes,
        '$.colors[*] ? (@ starts with "白")'
    ) AS white_variants
FROM products;

實際應用：動態表單

CREATE TABLE form_submissions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    form_id UUID NOT NULL,
    user_id UUID NOT NULL,
    data JSONB NOT NULL,
    submitted_at TIMESTAMPTZ DEFAULT NOW()
);

-- 建立 GIN 索引
CREATE INDEX idx_form_submissions_data ON form_submissions USING GIN (data);

-- 查詢特定表單欄位的值
SELECT
    id,
    data ->> 'name' AS applicant_name,
    data ->> 'email' AS email,
    data -> 'experience_years' AS years
FROM form_submissions
WHERE form_id = 'some-form-uuid'
  AND (data ->> 'experience_years')::int >= 3
ORDER BY submitted_at DESC;

-- 統計某個選項的選擇次數
SELECT
    data ->> 'preferred_stack' AS stack,
    COUNT(*) AS count
FROM form_submissions
WHERE form_id = 'tech-survey-form'
GROUP BY data ->> 'preferred_stack'
ORDER BY count DESC;

混合模式：固定欄位 + JSON

最佳實踐是將確定的欄位放在標準欄中，動態或可選的部分放在 JSONB：

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id),
    status TEXT NOT NULL DEFAULT 'pending',
    total DECIMAL(12, 2) NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'TWD',

    -- 固定的收件資訊
    shipping_name TEXT NOT NULL,
    shipping_address TEXT NOT NULL,

    -- 動態的附加資訊（優惠碼、備註、第三方整合資料等）
    extra JSONB DEFAULT '{}',

    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 查詢使用特定優惠碼的訂單
SELECT id, total
FROM orders
WHERE extra ->> 'coupon_code' = 'SUMMER2026';

總結

PostgreSQL 的 jsonb 型別結合 GIN 索引，提供了接近 NoSQL 的靈活性，同時保留了關聯資料庫的 ACID 特性和 JOIN 能力。在選擇「純 NoSQL」還是「PostgreSQL + JSONB」時，後者往往是更務實的選擇：你不需要維護兩套資料庫，也不需要放棄 SQL 的強大查詢能力。

#Supabase #Node.js

分享這篇文章