PostgreSQL 与 JSONB：兼具关系型强大和文档型灵活性的数据库

当 PostgreSQL 获得了对 JSON 文档的强大支持时，“SQL 还是 NoSQL？“这个问题就失去了相关性。通过 JSONB，你可以在同一个数据库中获得所需的严格模式结构和所需的文档灵活性。

目录

JSON vs JSONB：始终使用 JSONB

-- JSON: 按原样存储纯文本
-- JSONB: 以处理的二进制格式存储

-- JSONB 的优势:
-- ✓ 支持 GIN 索引（超快速查询）
-- ✓ 消除冗余空格和重复键
-- ✓ 包含操作符: @>, <@
-- ✗ 写入略慢（需要解析）
-- ✗ 不保留键的顺序和空格

CREATE TABLE eventos (
  id         BIGSERIAL PRIMARY KEY,
  tipo       TEXT NOT NULL,
  timestamp  TIMESTAMPTZ DEFAULT NOW(),
  payload    JSONB NOT NULL,             
  metadata   JSONB DEFAULT '{}'::JSONB
);

基本插入和查询

-- 插入带有灵活 payload 的事件
INSERT INTO eventos (tipo, payload) VALUES
  ('usuario.registro', '{"nombre": "Ana García", "plan": "pro", "pais": "MX"}'),
  ('pago.completado',  '{"monto": 99.99, "moneda": "USD", "metodo": "card"}'),
  ('error.api',        '{"codigo": 429, "endpoint": "/api/v2/items", "ip": "10.0.0.1"}');

-- 字段提取: ->> 操作符
SELECT payload->>'nombre' AS nombre
FROM eventos
WHERE tipo = 'usuario.registro';

-- 嵌套提取
SELECT payload->'direccion'->>'ciudad' AS ciudad
FROM eventos
WHERE tipo = 'usuario.registro';

-- 按 JSON 内部值过滤
SELECT * FROM eventos
WHERE tipo = 'pago.completado'
  AND (payload->>'monto')::NUMERIC > 50;

GIN 索引：SQL 速度查询 JSON

-- 在整个 JSONB 列上创建 GIN 索引
CREATE INDEX idx_eventos_payload ON eventos USING GIN (payload);  

-- 在特定键上创建索引（更高效）
CREATE INDEX idx_eventos_tipo_pago ON eventos
  USING GIN ((payload->'metodo'));

-- 现在这些查询使用索引:
SELECT * FROM eventos
WHERE payload @> '{"plan": "pro"}';      -- 包含此对象

SELECT * FROM eventos
WHERE payload ? 'codigo';                -- 包含此键

包含操作符

-- @>  "包含"
SELECT * FROM eventos
WHERE payload @> '{"moneda": "USD", "metodo": "card"}';

-- <@  "被包含于"
SELECT '{"a": 1}'::JSONB <@ '{"a": 1, "b": 2}'::JSONB;  -- true

-- ?   "拥有该键"
SELECT * FROM eventos WHERE payload ? 'codigo';

-- ?|  "拥有任一这些键"
SELECT * FROM eventos WHERE payload ?| ARRAY['nombre', 'email'];

-- ?&  "拥有所有这些键"
SELECT * FROM eventos WHERE payload ?& ARRAY['monto', 'moneda'];

jsonb_set 和部分更新

相对于纯文档的巨大优势：更新一个字段无需重写整个文档。

-- 更新 JSONB 内的字段
UPDATE eventos
SET payload = jsonb_set(payload, '{plan}', '"enterprise"')  
WHERE tipo = 'usuario.registro'
  AND payload->>'nombre' = 'Ana García';

-- 删除一个键
UPDATE eventos
SET payload = payload - 'ip'
WHERE tipo = 'error.api';

-- 在 JSONB 内的数组添加条目
UPDATE eventos
SET payload = jsonb_insert(payload, '{tags, -1}', '"urgente"')
WHERE tipo = 'error.api';

聚合函数：jsonb_agg 和 jsonb_object_agg

-- 按货币将支付分组为 JSON 数组
SELECT
  payload->>'moneda' AS moneda,
  COUNT(*)           AS total_pagos,
  jsonb_agg(payload) AS detalle          
FROM eventos
WHERE tipo = 'pago.completado'
GROUP BY moneda;

-- 从行构建对象
SELECT jsonb_object_agg(tipo, COUNT(*))  
FROM eventos
GROUP BY 1;

混合模式：兼得两者之长

CREATE TABLE productos (
  id          BIGSERIAL PRIMARY KEY,
  sku         TEXT UNIQUE NOT NULL,
  nombre      TEXT NOT NULL,
  precio      NUMERIC(10,2) NOT NULL,
  categoria   TEXT NOT NULL,
  -- 结构化字段 ↑ 用于 JOIN、B-tree 索引、约束
  atributos   JSONB DEFAULT '{}',
  -- 灵活属性 ↓ 根据产品类别
  CHECK (precio > 0)
);

-- 电子产品: { "voltaje": 220, "garantia_meses": 24 }
-- 服装:      { "tallas": ["S","M","L"], "material": "algodón" }
-- 图书:      { "isbn": "...", "paginas": 320 }

-- 利用两列的查询
SELECT nombre, atributos->>'garantia_meses' AS garantia
FROM productos
WHERE categoria = 'electronica'
  AND (atributos->>'garantia_meses')::INT >= 12
  AND precio < 500;

JSONB 不能替代关键字段的强类型列。规则是：如果你要频繁进行 JOIN、WHERE 或 ORDER BY——就用列。如果它是可变的元数据或很少查询——就放在 JSONB 中。