pg_projection

A lightweight PostgreSQL extension that brings MongoDB-style Projections to the JSONB data type. Optimized for read-only operations and performance, it allows you to filter JSON documents using a familiar syntax.

---

🚀 Installation

From Source

make
sudo make install

In PostgreSQL

Activate the extension in your database:

CREATE EXTENSION pg_projection;

---

🛠️ Usage

The extension provides functions to project fields based on a projection object where 1 means include and 0 means exclude.

1. Inclusion Mode

Returns only the specified fields. Note that _id is included by default unless explicitly set to 0.

SELECT pg_project(data, '{"name": 1, "email": 1}') 
FROM agents;
-- Result: {"_id": "...", "name": "...", "email": "..."}

2. Exclusion Mode

Returns all fields except those marked with 0.

SELECT pg_project(data, '{"internal_id": 0, "secret_key": 0}') 
FROM contacts;
-- Result: All fields except internal_id and secret_key

3. Query Set Projection

Transform the result of an entire query into a projected array of objects:

SELECT pg_project_set('SELECT * FROM users', '{"password": 0}');
-- Result: [{"id": 1, "username": "admin"}, ...]

---

💡 Why pg_projection?

• Familiar Syntax: If you know MongoDB, you already know how to use this.
• Native JSONB Performance: Leverages PostgreSQL's internal JSONB operators for speed.
• Clean API: Avoid complex jsonb_build_object chains for simple filtering.
• Dynamic Projections: Easily pass projections from your application layer directly to SQL.

---

📄 License

This project is licensed under the MIT License.