pg_projection

Overview

SQL-only extension.

Version

Build

You can build the RPM / DEB packages for pg_projection using pig build:

pig build pkg pg_projection         # build RPM / DEB packages

Install

You can install pg_projection directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install pg_projection;          # Install for current active PG version

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:

CREATE EXTENSION pg_projection;

Usage

Sources: pg_projection README, SQL definitions, control file.

pg_projection provides MongoDB-style read projections for PostgreSQL jsonb. The 1.0 SQL file defines two functions: pg_project(jsonb, jsonb) for one JSON document and pg_project_set(text, jsonb) for a query result converted to a JSON array.

Project One JSONB Value

Projection values are numeric flags: 1 includes a field and 0 excludes a field.

CREATE EXTENSION pg_projection;

SELECT pg_project(
  '{"_id": 7, "name": "Ada", "email": "[email protected]", "secret": "x"}'::jsonb,
  '{"name": 1, "email": 1}'::jsonb
);
-- {"_id": 7, "name": "Ada", "email": "[email protected]"}

In inclusion mode, _id is included by default when present. Exclude it explicitly when the caller wants only the selected fields:

SELECT pg_project(
  '{"_id": 7, "name": "Ada", "email": "[email protected]"}'::jsonb,
  '{"_id": 0, "name": 1}'::jsonb
);
-- {"name": "Ada"}

Exclude Fields

When the projection uses 0, the function starts from the original document and removes matching top-level keys:

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

Project A Query Result

pg_project_set(query_text, projection_json) executes the supplied SQL text, converts each row with to_jsonb(t), applies pg_project, and returns a JSON array:

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

Because query_text is dynamic SQL, pass only trusted query strings assembled by application or migration code you control. Do not concatenate untrusted user input into this argument.

Caveats

• The SQL implementation projects top-level keys; it does not implement nested MongoDB path projection.
• Projection values are cast to integers internally, so use numeric 0 and 1 flags.
• pg_project(jsonb, jsonb) is declared IMMUTABLE STRICT; pg_project_set(text, jsonb) is declared STABLE.