pg_projection

pg_projection : MongoDB-like read projections for JSONB in PostgreSQL

Overview

Packages

Source

pig build pkg pg_projection;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

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

Install this extension with pig:

pig install pg_projection;		# install via package name, for the active PG version

pig install pg_projection -v 18;   # install for PG 18
pig install pg_projection -v 17;   # install for PG 17
pig install pg_projection -v 16;   # install for PG 16
pig install pg_projection -v 15;   # install for PG 15
pig install pg_projection -v 14;   # install for PG 14

Create this extension with:

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.