JSON 與 JSONB 操作：PostgreSQL 的半結構化資料處理 | PostgreSQL

-- 驗證格式差異
SELECT '{"b": 1, "a": 2, "a": 3}'::json;
-- 結果: {"b": 1, "a": 2, "a": 3}  ← 保留原樣

SELECT '{"b": 1, "a": 2, "a": 3}'::jsonb;
-- 結果: {"a": 3, "b": 1}  ← 鍵排序、移除重複（保留最後一個）

-- 效能差異驗證
EXPLAIN (ANALYZE)
SELECT data->>'name' FROM json_table;   -- 每次解析 JSON 文字
EXPLAIN (ANALYZE)
SELECT data->>'name' FROM jsonb_table;  -- 直接從二進位結構提取

-- 測試資料
CREATE TABLE products (
    id    SERIAL PRIMARY KEY,
    data  JSONB NOT NULL
);

INSERT INTO products (data) VALUES ('{
    "name": "MacBook Pro",
    "price": 59900,
    "specs": {
        "cpu": "M3 Pro",
        "ram": 18,
        "storage": {"type": "SSD", "size": "512GB"}
    },
    "tags": ["laptop", "apple", "professional"],
    "in_stock": true
}');

-- -> 取得 JSON 物件（回傳 jsonb）
SELECT data->'specs' FROM products;
-- 結果: {"cpu": "M3 Pro", "ram": 18, "storage": {"type": "SSD", "size": "512GB"}}

-- ->> 取得文字值（回傳 text）
SELECT data->>'name' FROM products;
-- 結果: MacBook Pro

-- #> 路徑取值（回傳 jsonb）
SELECT data#>'{specs,storage,type}' FROM products;
-- 結果: "SSD"

-- #>> 路徑取值（回傳 text）
SELECT data#>>'{specs,storage,type}' FROM products;
-- 結果: SSD

-- 陣列存取（索引從 0 開始）
SELECT data->'tags'->0 FROM products;
-- 結果: "laptop"

SELECT data->'tags'->>1 FROM products;
-- 結果: apple

-- @> 包含（左邊包含右邊）
SELECT data FROM products
WHERE data @> '{"in_stock": true}';

-- <@ 被包含（左邊被右邊包含）
SELECT data FROM products
WHERE '{"name": "MacBook Pro", "price": 59900, "extra": true}' @> data::text::jsonb;

-- ? 鍵存在
SELECT data FROM products
WHERE data ? 'specs';

-- ?| 任一鍵存在
SELECT data FROM products
WHERE data ?| array['warranty', 'specs'];

-- ?& 所有鍵存在
SELECT data FROM products
WHERE data ?& array['name', 'price', 'specs'];

-- 建立 jsonb_ops 索引（預設）
CREATE INDEX idx_products_data ON products USING GIN(data);

-- 建立 jsonb_path_ops 索引（更小、@> 更快）
CREATE INDEX idx_products_data_path ON products USING GIN(data jsonb_path_ops);

-- 針對特定路徑建立表達式索引（最精準）
CREATE INDEX idx_products_name ON products USING GIN((data->'tags'));

-- B-tree 索引適合特定欄位的排序與範圍查詢
CREATE INDEX idx_products_price ON products ((data->>'price'));

-- 驗證索引使用
EXPLAIN (ANALYZE)
SELECT * FROM products WHERE data @> '{"in_stock": true}';
-- 應顯示 Bitmap Index Scan on idx_products_data

-- jsonb_path_query：回傳所有匹配的值
SELECT jsonb_path_query(data, '$.specs.cpu') FROM products;
-- 結果: "M3 Pro"

-- jsonb_path_query_array：回傳匹配結果陣列
SELECT jsonb_path_query_array(data, '$.tags[*]') FROM products;
-- 結果: ["laptop", "apple", "professional"]

-- jsonb_path_exists：檢查路徑是否存在
SELECT jsonb_path_exists(data, '$.specs.gpu') FROM products;
-- 結果: false

-- 條件過濾（filter expression）
SELECT jsonb_path_query(
    '{"items": [
        {"name": "A", "price": 100},
        {"name": "B", "price": 200},
        {"name": "C", "price": 50}
    ]}'::jsonb,
    '$.items[*] ? (@.price > 80)'
);
-- 結果: {"name": "A", "price": 100}
--       {"name": "B", "price": 200}

-- 算術運算
SELECT jsonb_path_query(
    '{"width": 10, "height": 20}'::jsonb,
    '$.width * $.height'
);
-- 結果: 200

-- 帶變數的 JSONPath
SELECT jsonb_path_exists(
    data,
    '$.price ? (@ > $min_price)',
    '{"min_price": 50000}'  -- 變數傳遞
) FROM products;
-- 結果: true

-- @? 路徑存在檢查（可走 GIN 索引）
SELECT * FROM products
WHERE data @? '$.tags[*] ? (@ == "laptop")';

-- @@ JSONPath 布林檢查（可走 GIN 索引）
SELECT * FROM products
WHERE data @@ '$.price > 50000';

-- 使用 GIN 索引加速 JSONPath 查詢
CREATE INDEX idx_products_data ON products USING GIN(data);

-- 以下查詢會使用 GIN 索引
EXPLAIN (ANALYZE)
SELECT * FROM products WHERE data @? '$.specs.cpu ? (@ == "M3 Pro")';

-- || 合併（新增或覆蓋鍵值）
UPDATE products
SET data = data || '{"warranty": "2 years"}'
WHERE id = 1;

-- 巢狀合併
UPDATE products
SET data = jsonb_set(data, '{specs,ram}', '36')
WHERE id = 1;

-- jsonb_set：設定特定路徑的值
-- jsonb_set(target, path, new_value [, create_if_missing])
UPDATE products
SET data = jsonb_set(data, '{specs,gpu}', '"M3 Pro GPU"', true)
WHERE id = 1;
-- create_if_missing = true：路徑不存在時建立

-- - 刪除鍵
UPDATE products
SET data = data - 'warranty'
WHERE id = 1;

-- #- 刪除巢狀路徑
UPDATE products
SET data = data #- '{specs,gpu}'
WHERE id = 1;

-- - 刪除陣列元素（依索引）
UPDATE products
SET data = jsonb_set(
    data,
    '{tags}',
    (data->'tags') - 2  -- 移除索引 2 的元素
)
WHERE id = 1;

-- jsonb_insert：在陣列指定位置插入
UPDATE products
SET data = jsonb_insert(
    data,
    '{tags,0}',        -- 在索引 0 前插入
    '"featured"',
    false              -- false=前插入, true=後插入
)
WHERE id = 1;

-- json_agg / jsonb_agg：將多行聚合為 JSON 陣列
SELECT jsonb_agg(name) FROM departments;
-- 結果: ["Engineering", "Marketing", "Sales"]

-- json_object_agg / jsonb_object_agg：聚合為 JSON 物件
SELECT jsonb_object_agg(id, name) FROM departments;
-- 結果: {"1": "Engineering", "2": "Marketing", "3": "Sales"}

-- 組合使用：建構巢狀 JSON 回應
SELECT jsonb_build_object(
    'department', d.name,
    'employees', (
        SELECT jsonb_agg(jsonb_build_object(
            'name', e.name,
            'title', e.title
        ) ORDER BY e.name)
        FROM employees e
        WHERE e.dept_id = d.id
    )
)
FROM departments d;

-- row_to_json：將整行轉為 JSON
SELECT row_to_json(t)
FROM (SELECT id, name, email FROM users LIMIT 3) t;

-- to_jsonb：將任意值轉為 JSONB
SELECT to_jsonb(ROW(1, 'Alice', true));
-- 結果: {"f1": 1, "f2": "Alice", "f3": true}

-- jsonb_build_array + jsonb_build_object：手動建構
SELECT jsonb_build_object(
    'total', count(*),
    'items', jsonb_agg(jsonb_build_object(
        'id', id,
        'name', data->>'name',
        'price', (data->>'price')::numeric
    ))
)
FROM products;

-- 傳統 EAV（反模式）
CREATE TABLE product_attributes (
    product_id INT,
    attr_name  TEXT,    -- 'color', 'size', 'weight'...
    attr_value TEXT
);
-- 問題：查詢需要大量 JOIN、無法有效使用索引

-- JSONB 替代方案
CREATE TABLE products (
    id         SERIAL PRIMARY KEY,
    name       TEXT NOT NULL,
    category   TEXT NOT NULL,
    attributes JSONB NOT NULL DEFAULT '{}'
);

INSERT INTO products (name, category, attributes) VALUES
    ('T-Shirt', 'clothing', '{"color": "red", "size": "L", "material": "cotton"}'),
    ('Laptop', 'electronics', '{"cpu": "M3", "ram": 16, "screen": "14inch"}'),
    ('Book', 'media', '{"author": "Alice", "pages": 320, "isbn": "978-xxx"}');

-- 每個 category 有不同的 attributes schema，JSONB 完美處理
CREATE INDEX idx_products_attrs ON products USING GIN(attributes);

-- 查詢特定屬性
SELECT name FROM products
WHERE attributes @> '{"color": "red"}';

-- 跨屬性查詢
SELECT name, attributes->>'color' AS color
FROM products
WHERE category = 'clothing'
  AND attributes @> '{"size": "L"}';

-- 使用 JSONB 儲存變更記錄
CREATE TABLE audit_log (
    id         BIGSERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    record_id  INT NOT NULL,
    action     TEXT NOT NULL,  -- INSERT, UPDATE, DELETE
    old_data   JSONB,
    new_data   JSONB,
    changed_by TEXT NOT NULL DEFAULT current_user,
    changed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 通用稽核 Trigger
CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (table_name, record_id, action, old_data, new_data)
    VALUES (
        TG_TABLE_NAME,
        COALESCE(NEW.id, OLD.id),
        TG_OP,
        CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN to_jsonb(OLD) END,
        CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN to_jsonb(NEW) END
    );
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- 綁定到目標表
CREATE TRIGGER trg_orders_audit
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION audit_trigger();

-- 查詢特定欄位的變更歷史
SELECT
    changed_at,
    old_data->>'status' AS old_status,
    new_data->>'status' AS new_status,
    changed_by
FROM audit_log
WHERE table_name = 'orders'
  AND record_id = 42
  AND old_data->>'status' IS DISTINCT FROM new_data->>'status'
ORDER BY changed_at DESC;

-- JSON_TABLE 基本語法
SELECT jt.*
FROM products p,
     JSON_TABLE(
         p.data,
         '$' COLUMNS (
             product_name TEXT PATH '$.name',
             price        NUMERIC PATH '$.price',
             cpu          TEXT PATH '$.specs.cpu',
             first_tag    TEXT PATH '$.tags[0]'
         )
     ) AS jt;

-- 巢狀路徑展開（NESTED PATH）
SELECT jt.*
FROM products p,
     JSON_TABLE(
         p.data,
         '$' COLUMNS (
             product_name TEXT PATH '$.name',
             NESTED PATH '$.tags[*]' COLUMNS (
                 tag TEXT PATH '$'
             )
         )
     ) AS jt;
-- 每個 tag 展開為一行

-- 在 PG17 之前的替代方案
SELECT
    data->>'name' AS product_name,
    tag
FROM products,
     jsonb_array_elements_text(data->'tags') AS tag;

-- jsonb_each / jsonb_each_text：展開物件為鍵值對
SELECT key, value
FROM jsonb_each('{"a": 1, "b": "hello", "c": true}'::jsonb);
-- key | value
-- a   | 1
-- b   | "hello"
-- c   | true

-- jsonb_array_elements：展開陣列為多行
SELECT value
FROM jsonb_array_elements('[1, 2, 3, "four"]'::jsonb);

-- jsonb_array_elements_text：展開陣列為文字
SELECT value
FROM jsonb_array_elements_text('["apple", "banana", "cherry"]'::jsonb);

-- jsonb_to_record / jsonb_to_recordset：轉為具名欄位
SELECT *
FROM jsonb_to_record('{"name": "Alice", "age": 30}'::jsonb)
     AS t(name TEXT, age INT);

-- jsonb_to_recordset：JSON 陣列轉為多行記錄集
SELECT *
FROM jsonb_to_recordset('[
    {"name": "Alice", "age": 30},
    {"name": "Bob", "age": 25}
]'::jsonb) AS t(name TEXT, age INT);

-- jsonb_typeof：取得 JSON 值的型別
SELECT jsonb_typeof('123'::jsonb);       -- number
SELECT jsonb_typeof('"hello"'::jsonb);   -- string
SELECT jsonb_typeof('true'::jsonb);      -- boolean
SELECT jsonb_typeof('null'::jsonb);      -- null
SELECT jsonb_typeof('[1,2]'::jsonb);     -- array
SELECT jsonb_typeof('{}'::jsonb);        -- object

-- jsonb_strip_nulls：移除所有 null 值的鍵
SELECT jsonb_strip_nulls('{"a": 1, "b": null, "c": {"d": null, "e": 2}}'::jsonb);
-- 結果: {"a": 1, "c": {"e": 2}}

-- jsonb_pretty：格式化輸出
SELECT jsonb_pretty('{"name":"Alice","scores":[90,85,92]}'::jsonb);

-- 使用 CHECK Constraint 驗證 JSONB 結構
CREATE TABLE events (
    id   SERIAL PRIMARY KEY,
    data JSONB NOT NULL,
    CONSTRAINT valid_event CHECK (
        data ? 'type'
        AND data ? 'timestamp'
        AND jsonb_typeof(data->'type') = 'string'
        AND jsonb_typeof(data->'timestamp') = 'string'
    )
);

-- 合法資料
INSERT INTO events (data) VALUES
    ('{"type": "click", "timestamp": "2026-01-01T00:00:00Z", "page": "/home"}');

-- 非法資料（缺少 type）
-- INSERT INTO events (data) VALUES ('{"timestamp": "2026-01-01"}');
-- ERROR: new row violates check constraint "valid_event"

-- PG12+ 使用 JSONPath 做更複雜的驗證
CREATE TABLE orders_v2 (
    id   SERIAL PRIMARY KEY,
    data JSONB NOT NULL,
    CONSTRAINT valid_order CHECK (
        jsonb_path_exists(data, '$.customer_id ? (@ > 0)')
        AND jsonb_path_exists(data, '$.items[0]')
        AND jsonb_path_exists(data, '$.total ? (@ >= 0)')
    )
);

-- PG14+ subscript 語法（更簡潔的路徑存取）
SELECT data['specs']['cpu'] FROM products;
-- 等同於 data->'specs'->'cpu'

UPDATE products
SET data['specs']['ram'] = '36'
WHERE id = 1;
-- 等同於 jsonb_set(data, '{specs,ram}', '36')

-- PG16+ IS JSON 謂詞
SELECT '{"a":1}'::text IS JSON;          -- true
SELECT '[1,2,3]'::text IS JSON ARRAY;    -- true
SELECT '{"a":1}'::text IS JSON OBJECT;   -- true
SELECT '123'::text IS JSON SCALAR;       -- true
SELECT 'not json'::text IS JSON;         -- false

-- 反模式：把所有東西都塞進 JSONB
CREATE TABLE users (
    id   SERIAL PRIMARY KEY,
    data JSONB  -- name, email, phone, address... 全部放 JSONB
);
-- 問題：失去 NOT NULL、UNIQUE、FK 等約束保護

-- 正確：結構化欄位用一般型別，彈性部分用 JSONB
CREATE TABLE users (
    id         SERIAL PRIMARY KEY,
    name       TEXT NOT NULL,
    email      TEXT NOT NULL UNIQUE,
    phone      TEXT,
    metadata   JSONB NOT NULL DEFAULT '{}'  -- 只放彈性資料
);

-- ->> 回傳 text，數值比較需要轉型
SELECT * FROM products
WHERE data->>'price' > '9000';
-- 字串比較！'9000' > '59900' 是 true（字母序）

-- 正確：轉為數值
SELECT * FROM products
WHERE (data->>'price')::numeric > 9000;

-- 或使用 JSONPath（自動處理型別）
SELECT * FROM products
WHERE data @@ '$.price > 9000';

-- JSONB 的每次更新都是「整個值的替換」
-- 即使只改一個欄位，PostgreSQL 仍需：
-- 1. 讀取整個 JSONB
-- 2. 修改目標路徑
-- 3. 寫入全新的 JSONB Tuple

-- 若 JSONB 值很大（數 KB 以上），考慮拆分
-- 反模式：
CREATE TABLE orders (
    id   SERIAL PRIMARY KEY,
    data JSONB  -- 包含 items（可能很大）和 status（經常更新）
);

-- 正確：將頻繁更新的欄位與大型靜態資料分離
CREATE TABLE orders (
    id     SERIAL PRIMARY KEY,
    status TEXT NOT NULL DEFAULT 'pending',  -- 頻繁更新
    items  JSONB NOT NULL                    -- 很少變動
);