PostgreSQL JSONB 指南：查询、索引与全文搜索

PostgreSQL JSONB（二进制 JSON）是现代 PostgreSQL 中最强大的功能之一。与以文本形式存储的 JSON 类型不同，JSONB 存储解析后的二进制数据——支持快速查询、索引和高效存储。本指南涵盖从基本操作到高级索引策略和性能优化的所有内容。

JSON vs JSONB：使用哪个？

PostgreSQL 有两种 JSON 类型：json 和 jsonb。对于几乎所有用例，请使用 jsonb。

创建表并插入 JSONB 数据

JSONB 列存储任何有效的 JSON 值。键在存储时会被排序，重复键会被删除。

-- Create a table with JSONB column
CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    metadata    JSONB,
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- Insert JSONB data
INSERT INTO products (name, metadata) VALUES
    ('Widget A', '{"price": 9.99, "category": "widgets", "tags": ["sale", "new"], "specs": {"weight": 0.5, "color": "red"}}'),
    ('Gadget B', '{"price": 24.99, "category": "gadgets", "tags": ["popular"], "specs": {"weight": 1.2, "color": "blue"}, "stock": 150}'),
    ('Doohickey C', '{"price": 4.99, "category": "widgets", "tags": ["sale"], "specs": {"weight": 0.1, "color": "green"}, "rating": 4.5}');

查询 JSONB：运算符参考

PostgreSQL 提供了丰富的运算符来访问和过滤 JSONB 数据。

-- Access a top-level key (returns JSONB)
SELECT metadata -> 'price' FROM products;
-- Returns: 9.99, 24.99, 4.99 (as JSONB)

-- Access a key as text (use ->>)
SELECT metadata ->> 'category' FROM products;
-- Returns: 'widgets', 'gadgets', 'widgets' (as text)

-- Nested access
SELECT metadata -> 'specs' -> 'color' FROM products;
SELECT metadata #> '{specs,color}' FROM products;        -- path operator
SELECT metadata #>> '{specs,color}' FROM products;       -- path as text

-- Filter rows by JSONB value
SELECT name FROM products WHERE metadata ->> 'category' = 'widgets';

-- Filter by nested value
SELECT name FROM products WHERE (metadata -> 'specs' ->> 'weight')::float > 0.5;

-- Check if key exists
SELECT name FROM products WHERE metadata ? 'stock';

-- Check if ANY key in array exists
SELECT name FROM products WHERE metadata ?| ARRAY['stock', 'rating'];

-- Check if ALL keys exist
SELECT name FROM products WHERE metadata ?& ARRAY['price', 'category'];

-- Containment: does JSONB contain this sub-object?
SELECT name FROM products WHERE metadata @> '{"category": "widgets"}';

-- Is JSONB contained by?
SELECT name FROM products WHERE '{"category": "widgets"}' <@ metadata;

JSONB 运算符快速参考

修改 JSONB 数据

在不替换整个值的情况下更新、添加和删除 JSONB 列中的字段。

-- Update a specific key (PostgreSQL 14+: subscript syntax)
UPDATE products
SET metadata['price'] = '19.99'
WHERE name = 'Widget A';

-- jsonb_set: update nested key (older syntax, all versions)
UPDATE products
SET metadata = jsonb_set(metadata, '{price}', '19.99')
WHERE name = 'Widget A';

-- Update nested path
UPDATE products
SET metadata = jsonb_set(metadata, '{specs,color}', '"black"')
WHERE name = 'Widget A';

-- Add a new key
UPDATE products
SET metadata = metadata || '{"discount": 0.1}'::jsonb
WHERE name = 'Widget A';

-- Remove a key
UPDATE products
SET metadata = metadata - 'stock'
WHERE name = 'Gadget B';

-- Remove multiple keys
UPDATE products
SET metadata = metadata - ARRAY['stock', 'rating'];

-- Remove nested key
UPDATE products
SET metadata = metadata #- '{specs,weight}';

JSONB 索引以提升性能

正确的索引策略可以使 JSONB 查询与常规列上的查询一样快。GIN 索引是 JSONB 的主力。

-- GIN index: most useful for JSONB (supports ?, ?|, ?&, @>, <@)
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

-- GIN index on a specific key (for equality queries on that key)
CREATE INDEX idx_products_category ON products
    USING GIN ((metadata -> 'category'));

-- B-tree index on extracted value (for range queries)
CREATE INDEX idx_products_price ON products
    USING BTREE ((( metadata ->> 'price' )::numeric));

-- Expression index for a nested text field
CREATE INDEX idx_products_color ON products
    USING BTREE ((metadata #>> '{specs,color}'));

-- Check index usage
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM products WHERE metadata @> '{"category": "widgets"}';

聚合和 JSONB 函数

PostgreSQL 提供了强大的函数来聚合、展开和构造 JSONB 值。

-- jsonb_agg: aggregate rows into a JSON array
SELECT
    metadata ->> 'category' AS category,
    jsonb_agg(name ORDER BY name) AS product_names,
    COUNT(*) AS count,
    AVG((metadata ->> 'price')::numeric) AS avg_price
FROM products
GROUP BY metadata ->> 'category';

-- jsonb_object_agg: build a JSON object from key-value pairs
SELECT jsonb_object_agg(name, metadata ->> 'price') AS price_map
FROM products;

-- jsonb_array_elements: expand a JSONB array into rows
SELECT
    name,
    jsonb_array_elements_text(metadata -> 'tags') AS tag
FROM products;

-- jsonb_each: expand a JSONB object into key-value rows
SELECT
    name,
    key,
    value
FROM products,
     jsonb_each(metadata -> 'specs');

-- jsonb_build_object: construct JSONB from expressions
SELECT jsonb_build_object(
    'id', id,
    'name', name,
    'price', (metadata ->> 'price')::numeric,
    'in_stock', metadata ? 'stock'
) AS product_summary
FROM products;

性能技巧

• 始终在频繁查询的 JSONB 列上创建 GIN 索引。没有索引，JSONB 查询需要顺序扫描。
• 使用生成列将频繁查询的字段提取为常规列：GENERATED ALWAYS AS (metadata ->> 'category') STORED。
• 使用 jsonb 而非 json——读取更快（二进制格式，无需重新解析）且支持索引。
• 避免深层嵌套。扁平的 JSONB 结构更易于索引和查询。考虑对非常深的结构进行规范化。
• 使用 @>（包含）过滤已知子对象——它使用 GIN 索引。避免使用 ->> 和 LIKE 进行文本搜索；改用全文搜索。

常见问题