关系数据库中的 JSON 列

概念定义

形状随生产方变化或持续演进的文档装不进固定列。JSON 类型把解析后的文档存进行里：可按路径查询、可做包含比较、可经表达式或 GIN 索引。jsonb 归一化文档，文本型 json 不可索引且无法原样往返。

工作机制

操作符负责提取与判断：-> 与 ->> 取值，@> 测包含，jsonb_path_query 执行 SQL/JSON 路径语言。GIN 加速文档包含查询；表达式索引高速服务单一路径。文档内容的统计信息弱于列统计，JSON 谓词的规划器估算常常失真。

使用场景

归档原始外部载荷、按租户扩展属性、稳定核心列加可变明细的事件信封、形状未定时的原型。访问模式一旦稳定，把热字段抽出成真正的列。

常见误区

“像文档库一样无模式”只是列级灵活：表仍是关系的，约束只能靠 CHECK 与校验代码。把频繁过滤与连接的业务键压进 JSON，等于放弃索引与约束；把金额和时间戳存成 JSON 字符串，会陷入类型转换与排序规则的泥潭。读取宽文档时按路径选取，别整块取回。

代码示例

package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
)

type Profile struct {
	ID       int64          `json:"id"`
	Nickname sql.NullString `json:"-"`
	Bio      *string        `json:"bio"`
}

type profileJSON struct {
	ID       int64   `json:"id"`
	Nickname *string `json:"nickname"`
	Bio      *string `json:"bio"`
}

func nullString(value sql.NullString) *string {
	if !value.Valid {
		return nil
	}
	return &value.String
}

func (p Profile) MarshalJSON() ([]byte, error) {
	return json.Marshal(profileJSON{
		ID:       p.ID,
		Nickname: nullString(p.Nickname),
		Bio:      p.Bio,
	})
}

func scanProfile(row *sql.Row) (Profile, error) {
	var profile Profile
	err := row.Scan(&profile.ID, &profile.Nickname, &profile.Bio)
	return profile, err
}

func main() {
	encoded, _ := json.Marshal(Profile{ID: 7})
	fmt.Println(string(encoded))
	encoded, _ = json.Marshal(Profile{
		ID:       8,
		Nickname: sql.NullString{String: "ada", Valid: true},
	})
	fmt.Println(string(encoded))
}

-- 每个邮箱保留 created_at 最新的一行，标记其余为重复
WITH ranked AS (
  SELECT
    id,
    email,
    created_at,
    ROW_NUMBER() OVER (
      PARTITION BY email
      ORDER BY created_at DESC, id DESC
    ) AS rn
  FROM subscribers
)
SELECT id, email, created_at, (rn = 1) AS is_primary
FROM ranked
ORDER BY email, rn;

-- 一次性清扫：删除全部非首行
WITH ranked AS (
  SELECT id,
         ROW_NUMBER() OVER (
           PARTITION BY email
           ORDER BY created_at DESC, id DESC
         ) AS rn
  FROM subscribers
)
DELETE FROM subscribers s
USING ranked r
WHERE s.id = r.id
  AND r.rn > 1;

-- 上游堵漏：唯一索引阻止重复再次写入
CREATE UNIQUE INDEX CONCURRENTLY idx_subscribers_email
  ON subscribers (email);