JSON 与非结构化数据

PostgreSQL 提供 json 和 jsonb 类型，用于存储结构灵活的数据。CloudBase PostgreSQL 中建议优先使用 jsonb，它支持更高效的查询、索引和字段操作。

JSON 字段适合扩展配置、第三方回调原文、表单草稿、埋点上下文等结构可能变化的数据。核心业务字段仍建议拆成普通列，便于约束、索引和权限控制。

json 与 jsonb

类型	特点	建议
json	保留原始文本格式，写入开销较低	很少查询内部字段时使用
jsonb	二进制存储，支持索引和高效查询	默认优先选择

创建 JSON 字段

create table public.orders (
  id bigint generated by default as identity primary key,
  user_id varchar(64) not null default auth.uid(),
  status text not null default 'pending',
  metadata jsonb not null default '{}',
  created_at timestamptz not null default now()
);

写入 JSON 数据时，可以直接传入 JSON 对象。

insert into public.orders (status, metadata)
values (
  'paid',
  '{
    "channel": "wechat",
    "coupon": "new-user",
    "client": { "platform": "mp" }
  }'::jsonb
);

查询 JSON 字段

常用操作符如下：

操作符	说明
->	取 JSON 对象或数组
->>	取文本值
@>	判断是否包含指定 JSON
?	判断是否存在顶层 key

select id, metadata ->> 'channel' as channel
from public.orders
where metadata ->> 'channel' = 'wechat';

包含查询适合判断某个 JSON 片段是否存在。

select id
from public.orders
where metadata @> '{"coupon": "new-user"}'::jsonb;

更新 JSON 字段

可以使用 jsonb_set 更新指定路径。

update public.orders
set metadata = jsonb_set(metadata, '{client,version}', '"1.2.0"', true)
where id = 1;

删除字段可以使用 - 操作符。

update public.orders
set metadata = metadata - 'coupon'
where id = 1;

HTTP API 查询

通过 HTTP API 查询 JSON 字段时，可以使用 PostgREST 支持的 JSON 路径过滤能力。具体语法以 HTTP API 参考为准。

curl -X GET 'https://<envId>.api.tcloudbasegateway.com/v1/rdb/rest/orders?metadata->>channel=eq.wechat' \
  -H 'Authorization: Bearer <access_token>'

索引优化

如果经常使用 @> 做包含查询，可以创建 GIN 索引。

create index orders_metadata_gin_idx
on public.orders
using gin (metadata);

如果经常按某个 JSON 子字段过滤，可以创建表达式索引。

create index orders_channel_idx
on public.orders ((metadata ->> 'channel'));

建模建议

• 用普通列保存查询频率高、需要排序、需要唯一约束或参与权限判断的字段。
• 用 jsonb 保存变化快、字段稀疏、只做展示或审计的扩展信息。
• 不要把完整业务模型都放进一个 JSON 字段，否则后续查询、权限和迁移成本会很高。