pg_projection

概览

SQL-only extension.

版本

构建

您可以使用 pig build 命令构建 pg_projection 扩展的 RPM / DEB 包：

pig build pkg pg_projection         # 构建 RPM / DEB 包

安装

您可以直接安装 pg_projection 扩展包的预置二进制包，首先确保 PGDG 和 PIGSTY 仓库已经添加并启用：

pig repo add pgsql -u          # 添加仓库并更新缓存

使用 pig 或者是 apt/yum/dnf 安装扩展：

• 安装
• pig
• dnf
• apt

pig install pg_projection;          # 当前活跃 PG 版本安装

pig ext install -y pg_projection -v 18  # PG 18
pig ext install -y pg_projection -v 17  # PG 17
pig ext install -y pg_projection -v 16  # PG 16
pig ext install -y pg_projection -v 15  # PG 15
pig ext install -y pg_projection -v 14  # PG 14

dnf install -y pg_projection_18       # PG 18
dnf install -y pg_projection_17       # PG 17
dnf install -y pg_projection_16       # PG 16
dnf install -y pg_projection_15       # PG 15
dnf install -y pg_projection_14       # PG 14

apt install -y postgresql-18-pg-projection   # PG 18
apt install -y postgresql-17-pg-projection   # PG 17
apt install -y postgresql-16-pg-projection   # PG 16
apt install -y postgresql-15-pg-projection   # PG 15
apt install -y postgresql-14-pg-projection   # PG 14

创建扩展：

CREATE EXTENSION pg_projection;

用法

• 来源：pg_projection README，SQL definitions，control file

pg_projection 为 PostgreSQL jsonb 提供类似 MongoDB 的读取投影能力。1.0 SQL 文件定义了两个函数：pg_project(jsonb, jsonb) 用于单个 JSON 文档，pg_project_set(text, jsonb) 用于把查询结果转换为 JSON 数组后再做投影。

投影单个 JSONB 值

投影值使用数字标志：1 表示包含字段，0 表示排除字段。

CREATE EXTENSION pg_projection;

SELECT pg_project(
  '{"_id": 7, "name": "Ada", "email": "ada@example.test", "secret": "x"}'::jsonb,
  '{"name": 1, "email": 1}'::jsonb
);
-- {"_id": 7, "name": "Ada", "email": "ada@example.test"}

在包含模式下，如果 _id 存在，默认会被保留。调用方只想返回选中字段时，需要显式排除它：

SELECT pg_project(
  '{"_id": 7, "name": "Ada", "email": "ada@example.test"}'::jsonb,
  '{"_id": 0, "name": 1}'::jsonb
);
-- {"name": "Ada"}

排除字段

当投影使用 0 时，函数会从原始文档出发，移除匹配的顶层 key：

SELECT pg_project(
  '{"name": "Ada", "internal_id": "a-1", "secret_key": "k"}'::jsonb,
  '{"internal_id": 0, "secret_key": 0}'::jsonb
);
-- {"name": "Ada"}

投影查询结果

pg_project_set(query_text, projection_json) 会执行传入的 SQL 文本，用 to_jsonb(t) 转换每一行，应用 pg_project，并返回 JSON 数组：

SELECT pg_project_set(
  'SELECT id, username, password_hash FROM users WHERE active',
  '{"password_hash": 0}'::jsonb
);

由于 query_text 是动态 SQL，只应传入由你控制的应用代码或迁移代码组装出的可信查询字符串。不要把不可信的用户输入拼接进这个参数。

注意事项

• SQL 实现只投影顶层 key；它没有实现 MongoDB 的嵌套路径投影。
• 投影值会在内部转换为整数，因此应使用数字 0 和 1 标志。
• pg_project(jsonb, jsonb) 声明为 IMMUTABLE STRICT；pg_project_set(text, jsonb) 声明为 STABLE。