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

Sources: README, META.json 2.0.2, pgbson.control

pgbson adds a BSON data type plus BSON-aware accessors and operators. Upstream documents the package release as 2.0.2, while the extension control file still exposes SQL default version 2.0; this matches the packaging note that the dist version is ahead of the extension SQL version.

CREATE EXTENSION pgbson;

Core Access Patterns

Typed dotpath accessors walk the BSON structure directly and are the upstream-recommended fast path:

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;
SELECT bson_get_string(bson_column, 'data.payload.product.definition.id') FROM my_table;

JSON-style operators are also supported:

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

Main Functions and Operators

• Typed getters such as bson_get_string, bson_get_int32, bson_get_int64, bson_get_double, bson_get_decimal, bson_get_datetime, bson_get_binary, and bson_get_boolean.
• bson_get_bson to return a BSON subdocument.
• bson_get_jsonb_array when a path resolves to an array and you want native jsonb array operators afterward.
• Arrow operators -> and ->> similar to PostgreSQL JSON types.
• Casts to json/jsonb using Extended JSON so type fidelity is preserved.

Interop and Indexing

Cast BSON to jsonb when you want PostgreSQL JSON operators:

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

Build expression indexes on extracted paths:

CREATE INDEX ON data_collection (bson_get_string(data, 'd.recordId'));

The README also notes BSON values can round-trip byte-for-byte through bytea casts.

Caveats

• Dotpath accessors are usually faster and more memory-efficient than long -> chains because they avoid materializing intermediate substructures.
• bson_get_bson() returns NULL for scalar endpoints because simple scalars are not BSON documents.
• Upstream explicitly calls out array handling and wrong-type accessor behavior as rough edges that still need better ergonomics.