跳到主要内容

PostgreSQL JSON 与 JSONB 教程

PostgreSQL 支持 JSON（文本存储）和 JSONB（二进制存储）。JSONB 支持索引和高效操作符，是绝大多数场景的选择。

本篇内容​

  • json vs jsonb 的差异
  • 取值操作符：-> 、->> 、#> 、#>>
  • 包含与存在判断：@> 、? 、?| 、?&
  • GIN 索引与查询优化

json 与 jsonb​

CREATE TABLE events (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  -- jsonb：解析后二进制存储，键顺序不保留，支持索引
  payload JSONB NOT NULL
);

INSERT INTO events (payload) VALUES
  ('{"type":"signup","user":{"id":42,"name":"Ada"},"tags":["db","sql"]}');

取值操作符​

-- -> 返回 JSON 值；->> 返回文本
SELECT payload -> 'user' ->> 'name' FROM events;

-- 路径访问 #>（返回 JSON）与 #>>（返回文本）
SELECT payload #>> '{user,name}' FROM events;

-- 数组下标
SELECT payload -> 'tags' -> 0 FROM events;

包含与键存在​

-- 包含：payload 中包含指定结构
SELECT * FROM events WHERE payload @> '{"type":"signup"}';

-- 键是否存在（顶层 / 路径 / 任一 / 全部）
SELECT * FROM events WHERE payload ? 'type';
SELECT * FROM events WHERE payload ?| ARRAY['type','login'];
SELECT * FROM events WHERE payload ?& ARRAY['type','user'];

索引​

-- GIN 索引加速 @> 与 ? 类查询
CREATE INDEX events_payload_gin ON events USING GIN (payload);

-- 常用路径可用表达式索引
CREATE INDEX events_type_idx ON events ((payload ->> 'type'));
待补充（TODO）
  • 补充：json 与 jsonb 的写入/存储差异与选型
  • 补充：jsonb 的修改操作（jsonb_set / || 合并 / - 删除键）
  • 补充：JSON 完整性校验（CHECK (payload @> …) 或 JSON Schema 扩展）
  • 补充：JSONB 与关系列混用的设计建议（何时该拆列）