1. JSONB 数据类型与操作
1.1 JSONB 简介
JSONB 是 PostgreSQL 中的一种数据类型，用于存储 JSON 格式的数据，并提供高效的查询和索引功能。
1.1.1 创建 JSONB 列
CREATE TABLE json_data ( id SERIAL PRIMARY KEY, data JSONB );1.2 JSONB 查询与索引
1.2.1 JSONB 查询操作
-- 查询包含特定键值对的行
SELECT * FROM json_data WHERE data @> '{"key": "value"}';
-- 查询包含特定键的行
SELECT * FROM json_data WHERE data ? 'key';
-- 查询数组元素
SELECT * FROM json_data WHERE data -> 'array_key' @> '["element1", "element2"]';
-- 展开 JSONB 对象
SELECT id, (data -> 'nested_key1' ->> 'nested_key2') AS nested_value FROM json_data;
1.2.2 创建 JSONB 索引
CREATE INDEX idx_jsonb_key ON json_data USING GIN (data jsonb_path_ops);1.3 JSONB 函数
1.3.1 JSONB 函数示例
-- 更新 JSONB 字段
UPDATE json_data SET data = jsonb_set(data, '{key}', '"new_value"', true);
-- 删除 JSONB 字段
UPDATE json_data SET data = data - 'key';
-- 合并 JSONB 对象
SELECT jsonb_concat(data1, data2) AS merged_data FROM json_data;
2. 全文搜索与搜索引擎集成
2.1 全文搜索功能
PostgreSQL 提供了全文搜索功能，允许在文本数据上执行复杂的搜索操作。
2.1.1 配置全文搜索
CREATE TEXT SEARCH DICTIONARY english_ispell (
TEMPLATE = ispell,
DictFile = english,
AffFile = english,
StopWords = english
);
ALTER TEXT SEARCH CONFIGURATION english ALTER MAPPING FOR asciiword WITH english_ispell;
2.2 全文搜索操作
2.2.1 全文搜索查询
SELECT * FROM documents WHERE to_tsvector('english', content) @@ to_tsquery('english', 'search_query');
2.3 全文搜索索引
2.3.1 创建全文搜索索引
CREATE INDEX idx_full_text_search ON documents USING GIN (to_tsvector('english', content));
2.4 全文搜索与外部搜索引擎集成
2.4.1 使用 pg_search 扩展
CREATE EXTENSION pg_trgm;
CREATE EXTENSION pgcrypto;
-- Query:
SELECT query, total_time
FROM pg_stat_statements
ORDER BY total_time