PostgreSQL中JSONB的使用與踩坑指南

更新時間：2025年12月22日 09:29:52 作者：山沐與山

文章介紹了PostgreSQL中JSONB的使用,包括JSONB的基礎(chǔ)操作、索引策略、數(shù)組操作和批量更新,文章通過實例詳細講解了JSONB的使用方法,幫助讀者解決實際工作中的問題,感興趣的朋友跟隨小編一起看看吧

PostgreSQL中JSONB的使用與踩坑記錄

前言

之前接到一個數(shù)據(jù)遷移的需求，要批量修改表里 JSONB 數(shù)組中的某個字段。本以為很簡單，結(jié)果折騰了大半天，踩了不少坑。這篇文章把 JSONB 的常用操作和我踩過的坑都整理出來，希望能幫到遇到類似問題的朋友。

一、JSONB是什么

1.1 JSON vs JSONB

PostgreSQL 提供了兩種 JSON 類型：

類型	存儲方式	查詢性能	寫入性能	支持索引
JSON	原始文本	慢（每次解析）	快	不支持
JSONB	二進制格式	快	稍慢（需要轉(zhuǎn)換）	支持

簡單理解：

JSON 就像把 JSON 字符串原封不動存進去，每次查詢都要重新解析
JSONB 會把 JSON 轉(zhuǎn)成二進制格式存儲，查詢時直接讀取，不需要解析

99% 的場景都應(yīng)該用 JSONB，除非你只是存儲不查詢。

1.2 JSONB的優(yōu)勢

相比傳統(tǒng)的 EAV（Entity-Attribute-Value）模式，JSONB 有明顯優(yōu)勢：

傳統(tǒng) EAV 模式：

-- 商品表
CREATE TABLE products (id INT, name VARCHAR(100));
-- 屬性表（每個屬性一行）
CREATE TABLE product_attributes (
    product_id INT,
    attr_key VARCHAR(50),
    attr_value VARCHAR(200)
);
-- 查詢某商品的所有屬性，需要 JOIN
SELECT p.name, pa.attr_key, pa.attr_value
FROM products p
JOIN product_attributes pa ON p.id = pa.product_id
WHERE p.id = 1;

JSONB 模式：

-- 一張表搞定
CREATE TABLE products (
    id INT,
    name VARCHAR(100),
    attributes JSONB  -- 所有擴展屬性都在這里
);
-- 直接查詢，不需要 JOIN
SELECT name, attributes FROM products WHERE id = 1;
-- 還能直接查詢 JSON 內(nèi)部的字段
SELECT name FROM products WHERE attributes->>'color' = 'red';

JSONB 的核心優(yōu)勢：

靈活：不同商品可以有不同的屬性，不需要改表結(jié)構(gòu)
高效：支持 GIN 索引，查詢性能有保障
簡潔：減少表的數(shù)量，降低 JOIN 復(fù)雜度

二、JSONB基礎(chǔ)操作

2.1 創(chuàng)建和插入

-- 創(chuàng)建包含 JSONB 列的表
CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    profile JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMP DEFAULT NOW()
);
-- 插入數(shù)據(jù)
INSERT INTO user_profiles (user_id, profile) VALUES
(1, '{"name": "張三", "age": 28, "tags": ["開發(fā)", "后端"], "settings": {"theme": "dark", "notify": true}}'),
(2, '{"name": "李四", "age": 32, "tags": ["產(chǎn)品", "管理"], "settings": {"theme": "light", "notify": false}}'),
(3, '{"name": "王五", "age": 25, "tags": ["前端", "全棧"], "settings": {"theme": "dark", "notify": true}}');

2.2 查詢操作符

JSONB 提供了豐富的操作符：

操作符	說明	返回類型	示例
`->`	獲取 JSON 對象字段	JSONB	`profile->'name'` → `"張三"`
`->>`	獲取 JSON 對象字段	TEXT	`profile->>'name'` → `張三`
`->`	獲取數(shù)組元素（按索引）	JSONB	`profile->'tags'->0` → `"開發(fā)"`
`->>`	獲取數(shù)組元素	TEXT	`profile->'tags'->>0` → `開發(fā)`
`#>`	按路徑獲取	JSONB	`profile#>'{settings,theme}'` → `"dark"`
`#>>`	按路徑獲取	TEXT	`profile#>>'{settings,theme}'` → `dark`

實際使用示例：

-- 獲取用戶名（返回 JSONB 類型，帶引號）
SELECT profile->'name' FROM user_profiles WHERE user_id = 1;
-- 結(jié)果："張三"
-- 獲取用戶名（返回 TEXT 類型，不帶引號）
SELECT profile->>'name' FROM user_profiles WHERE user_id = 1;
-- 結(jié)果：張三
-- 獲取嵌套字段
SELECT profile->'settings'->>'theme' FROM user_profiles WHERE user_id = 1;
-- 結(jié)果：dark
-- 使用路徑操作符（更簡潔）
SELECT profile#>>'{settings,theme}' FROM user_profiles WHERE user_id = 1;
-- 結(jié)果：dark
-- 獲取數(shù)組第一個元素
SELECT profile->'tags'->>0 FROM user_profiles WHERE user_id = 1;
-- 結(jié)果：開發(fā)

2.3 條件查詢

-- 查詢年齡大于 30 的用戶
SELECT * FROM user_profiles 
WHERE (profile->>'age')::int > 30;
-- 查詢使用深色主題的用戶
SELECT * FROM user_profiles 
WHERE profile#>>'{settings,theme}' = 'dark';
-- 查詢標簽包含"后端"的用戶
SELECT * FROM user_profiles 
WHERE profile->'tags' ? '后端';
-- 查詢同時包含多個標簽的用戶
SELECT * FROM user_profiles 
WHERE profile->'tags' ?& array['開發(fā)', '后端'];
-- 查詢包含任意一個標簽的用戶
SELECT * FROM user_profiles 
WHERE profile->'tags' ?| array['前端', '后端'];

2.4 包含查詢

@> 操作符用于判斷左邊的 JSONB 是否包含右邊的 JSONB：

-- 查詢 settings 中 theme 為 dark 的用戶
SELECT * FROM user_profiles 
WHERE profile @> '{"settings": {"theme": "dark"}}';
-- 查詢標簽包含"開發(fā)"的用戶
SELECT * FROM user_profiles 
WHERE profile @> '{"tags": ["開發(fā)"]}';

注意：@> 可以利用 GIN 索引，性能很好。

三、JSONB索引詳解

3.1 GIN索引基礎(chǔ)

GIN（Generalized Inverted Index）是 JSONB 最常用的索引類型：

-- 創(chuàng)建默認的 GIN 索引
CREATE INDEX idx_profile_gin ON user_profiles USING gin(profile);

這個索引支持以下操作符：

@> 包含
? 鍵存在
?& 所有鍵存在
?| 任意鍵存在

3.2 jsonb_path_ops

如果你只需要 @> 操作符，可以使用更高效的 jsonb_path_ops：

-- 創(chuàng)建 jsonb_path_ops 索引
CREATE INDEX idx_profile_path ON user_profiles USING gin(profile jsonb_path_ops);

對比：

索引類型	索引大小	支持的操作符
默認 GIN	較大	`@>`, `?`, `?&`, `?
jsonb_path_ops	較?。s 1/3）	僅 `@>`

建議：如果只用 @> 查詢，優(yōu)先選擇 jsonb_path_ops。

3.3 表達式索引

如果經(jīng)常查詢某個特定字段，可以創(chuàng)建表達式索引：

-- 為 profile->>'name' 創(chuàng)建 B-Tree 索引
CREATE INDEX idx_profile_name ON user_profiles ((profile->>'name'));
-- 為 age 創(chuàng)建索引（轉(zhuǎn)換為整數(shù)）
CREATE INDEX idx_profile_age ON user_profiles (((profile->>'age')::int));
-- 查詢時可以利用索引
SELECT * FROM user_profiles WHERE profile->>'name' = '張三';
SELECT * FROM user_profiles WHERE (profile->>'age')::int > 30;

3.4 索引選擇策略

查詢模式	推薦索引
`profile @> '{"key": "value"}'`	GIN (jsonb_path_ops)
`profile ? 'key'`	GIN (默認)
`profile->>'key' = 'value'`	B-Tree 表達式索引
`(profile->>'num')::int > 100`	B-Tree 表達式索引

四、JSONB數(shù)組操作

JSONB 數(shù)組操作是實際開發(fā)中的高頻需求，也是很多人踩坑的地方。

4.1 數(shù)組展開

jsonb_array_elements 函數(shù)可以將數(shù)組展開成多行：

-- 原始數(shù)據(jù)
SELECT profile->'tags' FROM user_profiles WHERE user_id = 1;
-- 結(jié)果：["開發(fā)", "后端"]
-- 展開數(shù)組
SELECT jsonb_array_elements(profile->'tags') AS tag
FROM user_profiles WHERE user_id = 1;
-- 結(jié)果：
-- "開發(fā)"
-- "后端"
-- 展開為文本（去掉引號）
SELECT jsonb_array_elements_text(profile->'tags') AS tag
FROM user_profiles WHERE user_id = 1;
-- 結(jié)果：
-- 開發(fā)
-- 后端

4.2 保留數(shù)組順序

展開數(shù)組時，如果需要保留原始順序，使用 WITH ORDINALITY：

SELECT elem, idx
FROM user_profiles,
     jsonb_array_elements(profile->'tags') WITH ORDINALITY AS t(elem, idx)
WHERE user_id = 1;
-- 結(jié)果：
-- elem     | idx
-- "開發(fā)"   | 1
-- "后端"   | 2

這個技巧非常重要，后面批量更新時會用到。

4.3 數(shù)組聚合

jsonb_agg 函數(shù)可以將多行聚合成數(shù)組：

-- 將所有用戶的名字聚合成數(shù)組
SELECT jsonb_agg(profile->>'name') FROM user_profiles;
-- 結(jié)果：["張三", "李四", "王五"]
-- 按順序聚合
SELECT jsonb_agg(elem ORDER BY idx)
FROM user_profiles,
     jsonb_array_elements(profile->'tags') WITH ORDINALITY AS t(elem, idx)
WHERE user_id = 1;

4.4 數(shù)組修改

-- 追加元素到數(shù)組末尾
UPDATE user_profiles
SET profile = jsonb_set(profile, 'PostgreSQL
JSONB
', profile->'tags' || '"運維"'::jsonb)
WHERE user_id = 1;
-- 刪除數(shù)組中的某個元素
UPDATE user_profiles
SET profile = jsonb_set(
    profile, 
    'PostgreSQL
JSONB
', 
    (SELECT jsonb_agg(elem) FROM jsonb_array_elements(profile->'tags') AS elem WHERE elem <> '"后端"')
)
WHERE user_id = 1;

五、JSONB批量更新實戰(zhàn)

這是本文的重點，也是實際工作中最容易出問題的地方。

5.1 場景描述

假設(shè)我們有一個促銷配置表，每個商品可以配置多條促銷規(guī)則：

CREATE TABLE product_promo_config (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_id UUID NOT NULL,
    shop_id UUID NOT NULL,
    promo_rules JSONB NOT NULL,  -- 促銷規(guī)則數(shù)組
    created_at TIMESTAMP DEFAULT NOW()
);
-- 插入測試數(shù)據(jù)
INSERT INTO product_promo_config (product_id, shop_id, promo_rules) VALUES
('a1111111-1111-1111-1111-111111111111', 's1111111-1111-1111-1111-111111111111',
 '[{"code": "discount-fixed", "enabled": true, "params": {"rate": 0.8}},
   {"code": "coupon-amount", "enabled": true, "params": {"max": 50}},
   {"code": "gift-random", "enabled": false, "params": {}}]'),
('a2222222-2222-2222-2222-222222222222', 's1111111-1111-1111-1111-111111111111',
 '[{"code": "discount-percent", "enabled": true, "params": {"percent": 10}}]');

現(xiàn)在需求來了：批量修改所有規(guī)則的 code 字段，按照映射關(guān)系：

舊 code	新 code
discount-fixed	price-discount-fixed
discount-percent	price-discount-percent
coupon-amount	order-coupon-amount
gift-random	activity-gift-random

5.2 錯誤示范

很多人第一反應(yīng)是用 jsonb_set 直接改：

-- 錯誤！只能改數(shù)組第一個元素
UPDATE product_promo_config
SET promo_rules = jsonb_set(promo_rules, '{0,code}', '"price-discount-fixed"')
WHERE promo_rules->0->>'code' = 'discount-fixed';

這樣只能改數(shù)組的第一個元素，如果一個商品配了多條規(guī)則，后面的規(guī)則就改不到。

5.3 正確方案

正確的做法是：展開 → 替換 → 聚合。

第一步：創(chuàng)建映射表

CREATE TEMPORARY TABLE code_mapping (
    old_code VARCHAR(100) PRIMARY KEY,
    new_code VARCHAR(100) NOT NULL
);
INSERT INTO code_mapping (old_code, new_code) VALUES
('discount-fixed', 'price-discount-fixed'),
('discount-percent', 'price-discount-percent'),
('coupon-amount', 'order-coupon-amount'),
('coupon-percent', 'order-coupon-percent'),
('gift-random', 'activity-gift-random'),
('gift-specific', 'activity-gift-specific');

第二步：預(yù)覽更新結(jié)果

在執(zhí)行 UPDATE 之前，先用 SELECT 預(yù)覽：

SELECT 
    ppc.id,
    ppc.promo_rules AS old_rules,
    (
        SELECT jsonb_agg(
            CASE 
                WHEN cm.new_code IS NOT NULL 
                THEN jsonb_set(elem, '[code]', to_jsonb(cm.new_code))
                ELSE elem
            END
            ORDER BY idx  -- 保持原始順序
        )
        FROM jsonb_array_elements(ppc.promo_rules) WITH ORDINALITY AS t(elem, idx)
        LEFT JOIN code_mapping cm ON elem->>'code' = cm.old_code
    ) AS new_rules
FROM product_promo_config ppc;

解釋這段 SQL：

jsonb_array_elements(ppc.promo_rules) WITH ORDINALITY AS t(elem, idx)
將 promo_rules 數(shù)組展開成多行
- elem 是每個元素，idx 是原始位置（從 1 開始）
- LEFT JOIN code_mapping cm ON elem->>'code' = cm.old_code
和映射表關(guān)聯(lián)，找到對應(yīng)的新 code
- CASE WHEN ... THEN jsonb_set(...) ELSE elem END
如果找到映射就替換，找不到就保持原樣
jsonb_agg(... ORDER BY idx)
- 聚合回數(shù)組，按原始順序排列

第三步：執(zhí)行更新

確認預(yù)覽結(jié)果正確后，執(zhí)行更新：

BEGIN;
UPDATE product_promo_config ppc
SET promo_rules = (
    SELECT COALESCE(
        jsonb_agg(
            CASE 
                WHEN cm.new_code IS NOT NULL 
                THEN jsonb_set(elem, '[code]', to_jsonb(cm.new_code))
                ELSE elem
            END
            ORDER BY idx
        ),
        '[]'::jsonb  -- 處理空數(shù)組的情況
    )
    FROM jsonb_array_elements(ppc.promo_rules) WITH ORDINALITY AS t(elem, idx)
    LEFT JOIN code_mapping cm ON elem->>'code' = cm.old_code
)
WHERE EXISTS (
    SELECT 1 
    FROM jsonb_array_elements(ppc.promo_rules) AS e
    JOIN code_mapping cm ON e->>'code' = cm.old_code
);
-- 驗證結(jié)果
SELECT id, promo_rules FROM product_promo_config;
-- 確認無誤后提交
COMMIT;

5.4 通用模板

這是 JSONB 數(shù)組批量更新的通用模板，可以直接套用：

UPDATE your_table t
SET json_column = (
    SELECT COALESCE(
        jsonb_agg(
            CASE 
                WHEN mapping.new_val IS NOT NULL 
                THEN jsonb_set(elem, '{field_name}', to_jsonb(mapping.new_val))
                ELSE elem
            END
            ORDER BY idx  -- 保持原順序
        ),
        '[]'::jsonb  -- 處理空數(shù)組
    )
    FROM jsonb_array_elements(t.json_column) WITH ORDINALITY AS x(elem, idx)
    LEFT JOIN mapping_table mapping ON elem->>'field_name' = mapping.old_val
)
WHERE EXISTS (
    SELECT 1 
    FROM jsonb_array_elements(t.json_column) AS e
    JOIN mapping_table mapping ON e->>'field_name' = mapping.old_val
);

六、JSONB性能優(yōu)化

6.1 避免全表掃描

-- 慢：沒有索引支持
SELECT * FROM user_profiles WHERE profile->>'name' = '張三';
-- 快：創(chuàng)建表達式索引
CREATE INDEX idx_profile_name ON user_profiles ((profile->>'name'));
SELECT * FROM user_profiles WHERE profile->>'name' = '張三';
-- 快：使用 @> 操作符 + GIN 索引
CREATE INDEX idx_profile_gin ON user_profiles USING gin(profile jsonb_path_ops);
SELECT * FROM user_profiles WHERE profile @> '{"name": "張三"}';

6.2 減少 JSONB 大小

JSONB 越大，查詢和更新越慢。建議：

只存必要的字段
避免深層嵌套（建議不超過 3 層）
大文本考慮單獨存儲

-- 不推薦：把所有東西都塞進 JSONB
profile = '{"name": "...", "avatar_base64": "超長字符串...", "history": [...]}'
-- 推薦：大字段單獨存儲
profile = '{"name": "...", "avatar_id": "xxx"}'
-- avatar 內(nèi)容存在單獨的表或?qū)ο蟠鎯?/pre>

6.3 批量操作優(yōu)化

-- 慢：逐行更新
UPDATE user_profiles SET profile = jsonb_set(profile, '{age}', '29') WHERE user_id = 1;
UPDATE user_profiles SET profile = jsonb_set(profile, '{age}', '33') WHERE user_id = 2;
UPDATE user_profiles SET profile = jsonb_set(profile, '{age}', '26') WHERE user_id = 3;
-- 快：批量更新
UPDATE user_profiles AS up
SET profile = jsonb_set(up.profile, '{age}', to_jsonb(v.new_age))
FROM (VALUES (1, 29), (2, 33), (3, 26)) AS v(user_id, new_age)
WHERE up.user_id = v.user_id;

6.4 使用 EXPLAIN 分析

EXPLAIN ANALYZE 
SELECT * FROM user_profiles WHERE profile @> '{"settings": {"theme": "dark"}}';
-- 查看是否使用了索引
-- Index Scan using idx_profile_gin on user_profiles  (cost=...)

七、常見問題與最佳實踐

7.1 常見問題

問題	原因	解決方案
查詢慢	沒有合適的索引	根據(jù)查詢模式創(chuàng)建 GIN 或表達式索引
更新數(shù)組只改了第一個	用了 `jsonb_set(col, '{0,field}', ...)`	使用展開-替換-聚合模式
數(shù)組順序亂了	`jsonb_agg` 不保證順序	加 `WITH ORDINALITY` + `ORDER BY`
空數(shù)組報錯	`jsonb_agg` 對空集返回 NULL	用 `COALESCE(..., '[]'::jsonb)`
類型轉(zhuǎn)換錯誤	`->` 返回 JSONB，`->>` 返回 TEXT	注意操作符的返回類型

7.2 最佳實踐

設(shè)計階段：

明確哪些字段放 JSONB，哪些放普通列
高頻查詢的字段考慮提取為普通列
設(shè)計合理的 JSON 結(jié)構(gòu)，避免過深嵌套

開發(fā)階段：

優(yōu)先使用 @> 操作符（可以利用 GIN 索引）
數(shù)組操作記得保持順序
UPDATE 前先 SELECT 預(yù)覽

運維階段：

監(jiān)控 JSONB 列的大小
定期 VACUUM 清理死元組
關(guān)注慢查詢?nèi)罩?/li>

7.3 JSONB 操作速查表

-- 取值
col->'key'                    -- 返回 JSONB
col->>'key'                   -- 返回 TEXT
col->0                        -- 數(shù)組第一個元素（JSONB）
col->>0                       -- 數(shù)組第一個元素（TEXT）
col#>'{a,b,c}'               -- 路徑取值（JSONB）
col#>>'{a,b,c}'              -- 路徑取值（TEXT）
-- 修改
jsonb_set(col, '{key}', '"value"'::jsonb)           -- 設(shè)置字段
jsonb_set(col, '{key}', to_jsonb(variable))         -- 設(shè)置字段（變量）
col || '{"new_key": "value"}'::jsonb                -- 合并
col - 'key'                                          -- 刪除鍵
col - 0                                              -- 刪除數(shù)組第一個元素
-- 數(shù)組操作
jsonb_array_elements(col)                           -- 展開數(shù)組
jsonb_array_elements(col) WITH ORDINALITY           -- 展開并保留順序
jsonb_agg(elem)                                     -- 聚合成數(shù)組
jsonb_agg(elem ORDER BY idx)                        -- 按順序聚合
jsonb_array_length(col)                             -- 數(shù)組長度
-- 判斷
col ? 'key'                   -- 鍵是否存在
col ?& array['a','b']        -- 所有鍵是否存在
col ?| array['a','b']        -- 任意鍵是否存在
col @> '{"key": "val"}'      -- 是否包含
-- 類型
jsonb_typeof(col)            -- 返回類型（object/array/string/number/boolean/null）

八、總結(jié)

本文從基礎(chǔ)到進階，系統(tǒng)講解了 PostgreSQL JSONB 的使用：

基礎(chǔ)操作：-> 和 ->> 的區(qū)別，條件查詢的寫法
索引策略：GIN 索引、jsonb_path_ops、表達式索引的選擇
數(shù)組操作：展開、聚合、保持順序的技巧
批量更新：展開-替換-聚合的通用模式
性能優(yōu)化：索引選擇、批量操作、EXPLAIN 分析

JSONB 是 PostgreSQL 的殺手級特性之一，掌握它可以讓你在很多場景下避免引入額外的中間件。希望這篇文章能幫你在實際工作中少踩坑。

到此這篇關(guān)于PostgreSQL中JSONB的使用與踩坑記錄的文章就介紹到這了,更多相關(guān)PostgreSQL JSONB使用內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线