搜索数据怎么建模？从「一坨 JSON」到可查询的数据模型

我最早存搜索结果的方式特别粗暴——整个 JSON 响应塞进一个 TEXT 字段，用的时候再解析。前两周很爽，第三周开始痛：查「某个关键词排第几」要写 JSON 函数，查「某站点的排名趋势」要全表扫。后来花了一天把数据模型重做，查询从「写不出来」变成「一条 SQL」。这篇记录搜索数据建模的思路。

为什么不能「JSON 直接存」

JSON 直接存不是错，但不能只存 JSON：

• 查询难：排名、趋势、域名对比这些高频操作，全都要在 JSON 里挖
• 索引难：索引建在列上，JSON 里的字段用不上
• 语义模糊：字段什么含义靠脑子记，新人接手全靠猜

正确姿势是双轨：原始 JSON 当证据，结构化模型供查询。两个都留。

第一步：拆实体

先别看 JSON，看业务要回答什么问题：

• 这个关键词现在排第几？
• 排名最近怎么变的？
• SERP 上有哪些模块（精选摘要、相关搜索）？
• 竞品出现在哪些词里？

问题反推实体，四个就够：

1. 采集请求：keyword、市场（hl/gl）、时间、request_id、credits
2. 结果条目：rank、title、link、domain、snippet
3. SERP 模块：featured_snippet、people_also_ask、related_searches…
4. 原始响应：完整 JSON（证据层，重放和排查用）

第二步：建表（以搜索端点为例）

CREATE TABLE requests (
  request_id      TEXT PRIMARY KEY,
  keyword         TEXT NOT NULL,
  hl              TEXT NOT NULL DEFAULT 'en',
  gl              TEXT NOT NULL DEFAULT 'us',
  page            INTEGER NOT NULL DEFAULT 1,
  collected_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  credits_charged INTEGER
);

CREATE TABLE result_items (
  id         BIGSERIAL PRIMARY KEY,
  request_id TEXT NOT NULL REFERENCES requests(request_id),
  rank       INTEGER NOT NULL,
  title      TEXT,
  link       TEXT NOT NULL,
  domain     TEXT,
  snippet    TEXT,
  UNIQUE (request_id, rank)
);

CREATE TABLE serp_modules (
  id          BIGSERIAL PRIMARY KEY,
  request_id  TEXT NOT NULL REFERENCES requests(request_id),
  module_type TEXT NOT NULL,          -- featured_snippet / people_also_ask / ...
  payload     JSONB NOT NULL
);

CREATE TABLE raw_responses (
  request_id TEXT PRIMARY KEY,
  body       JSONB NOT NULL
);

几个设计决定值得说明：

• rank 设 NOT NULL（结果必有排名），title/snippet 可空——可空性照着接口文档的 optional 标注来
• 模块统一用「类型 + JSONB」存，模块结构经常变，硬拆成多张表不划算
• 原始响应单独一张表，日常查询不会扫到它

第三步：写数据字典

表建完，字段含义要落到纸面：

字段	类型	可空	含义	来源
rank	int	否	本次响应内的 1-based 排名	organic[].rank
domain	text	是	link 解析出的域名	从 link 派生
snippet	text	是	摘要，谷歌展示了才有	organic[].snippet

字典的收益在细节里：新同事看字典就懂；校验逻辑照着字典写；rank 和 position 这种易混字段在字典里写清楚关系。

踩坑记录

坑 1：只存 JSON。 最简单的「查排名」都要写 JSON 函数，性能还差。原始 + 模型双存解决。

坑 2：忘了时间维度。 结果表没存 collected_at，多天数据混在一起，趋势分析要靠连表。每条数据都带时间和市场维度。

坑 3：可空字段设成 NOT NULL。 把 snippet 设成 NOT NULL，某次谷歌没渲染摘要，插入直接失败。可空性严格照文档。

坑 4：rank 和 position 混用。 文档里 position 是 rank 的别名，且不是每次都出现。入库统一用 rank。

坑 5：字典比代码旧。 加了字段不更新字典，半年后没人敢信字典。改表必须改字典，写进 code review 清单。

工程清单

1. 原始 JSON 和可查询模型双存
2. 拆实体：请求 / 结果 / 模块 / 原始响应
3. 可空性照接口文档的 optional 标注
4. 每条数据带时间 + 市场维度
5. 字段含义写数据字典，改表必须改字典

建模的本质，是把「以后要查什么」提前想清楚。JSON 是证据，模型是姿势——两个都在，数据才「存得下、查得出、说得清」。

接口里哪些字段 optional、position 和 rank 什么关系，SerpBase 官方文档 每个端点都标得很细，建模前对着字段表先列一遍。你现在怎么存搜索数据，JSON 一把梭还是建了模型？评论区聊聊。