pgbson

Overview

Release tag 2.0.2 still ships extension SQL version 2.0; PGXN dist name is bson, CREATE EXTENSION name is pgbson, RPM package root is postgresbson, and the runtime dependency is libbson.

Version

Build

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

pig build pkg pgbson         # build RPM / DEB packages

Install

You can install pgbson 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:

• Install
• pig
• dnf
• apt

pig install pgbson;          # Install for current active PG version

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

dnf install -y postgresbson_18       # PG 18
dnf install -y postgresbson_17       # PG 17
dnf install -y postgresbson_16       # PG 16
dnf install -y postgresbson_15       # PG 15
dnf install -y postgresbson_14       # PG 14

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

Create Extension:

CREATE EXTENSION pgbson;

Usage

Syntax:

CREATE EXTENSION pgbson;
SELECT bson_get_datetime(bson_column, 'msg.header.event.ts') FROM my_table;
SELECT (bson_column->'msg'->'header'->'event'->>'ts')::timestamp FROM my_table;

Source: README

pgbson adds a BSON data type to PostgreSQL together with functions and operators for creating, inspecting, and querying BSON documents. The upstream README positions it as a binary, richly typed alternative to JSON/JSONB with round-trip fidelity and first-class support for datetimes, numeric subtypes, and raw bytes.

Why BSON

The README highlights several BSON advantages over JSON:

• datetimes are first-class values
• numeric types remain distinct (int32, int64, float, decimal)
• raw byte arrays are first-class
• round-tripping preserves exact binary representation
• native SDK support exists across many languages

Access Patterns

The extension exposes two styles of access:

Dotpath Accessors

These are the high-performance typed accessors documented upstream:

SELECT bson_get_datetime(bson_column, 'msg.header.event.ts') FROM my_table;
SELECT bson_get_bson(bson_column, 'msg.header.event') FROM my_table;

The README argues these are more memory-efficient than repeated arrow dereferences because they walk the BSON structure directly and materialize only the terminal value.

Arrow Operators

It also supports JSON-like operators:

SELECT (bson_column->'msg'->'header'->'event'->>'ts')::timestamp
FROM my_table;

JSON Interop

The BSON type can be cast to JSON using Extended JSON (EJSON) so type fidelity is preserved. This allows BSON values to be fed into JSON/JSONB functions and operators when needed:

SELECT (bson_get_bson(bson_column, 'msg.header.event')::jsonb) ?& ARRAY['id','type']
FROM my_table;

Notes

The README includes examples of end-to-end BSON round-tripping across Java, Kafka, Python, and PostgreSQL, emphasizing that the stored BSON payload can be retrieved byte-for-byte unchanged when cast back to bytea.