pgbson

Overview

PGXN distribution name is bson, CREATE EXTENSION name is pgbson, source archive and RPM root are postgresbson, and the control default_version is 2.1 while the package release is 2.1.0.

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:

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:

• pgbson 2.1.0 README
• pgbson 2.1 control file
• pgbson 2.1 SQL API

pgbson adds a BSON data type, typed dot-path accessors, JSON-style navigation, casts, comparison operators, and btree/hash indexing. The PGXN distribution release is 2.1.0, while the SQL extension version is 2.1. Use BSON when binary round-trip fidelity or BSON-specific scalar types matter; use jsonb when PostgreSQL-native JSON indexing is the primary requirement.

Install and Store BSON

CREATE EXTENSION pgbson;
SELECT pgbson_version();

CREATE TABLE events (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  payload bson NOT NULL
);

INSERT INTO events (payload)
VALUES ('{"user":{"name":"Ada"},"attempt":3}'::jsonb::bson);

The native module depends on libbson. The implicit bytea-to-bson cast validates BSON input, while the reverse cast preserves the binary representation.

Extract Values

Typed accessors avoid materializing each intermediate document:

SELECT bson_get_string(payload, 'user.name'),
       bson_get_int32(payload, 'attempt')
FROM events;

Other typed getters cover 64-bit integers, doubles, decimals, datetimes, binary values, booleans, embedded BSON documents, and JSONB arrays. A missing path or a type mismatch returns NULL, so validate the expected BSON schema at ingestion when those cases must be distinguished.

Version 2.1 adds a type-agnostic terminal extractor:

SELECT bson_get_value(payload, 'user.name')
FROM events;
-- { "_" : "Ada" }

bson_get_value always wraps the selected scalar, array, or document under the key _. Remove exactly that one wrapper in the caller. It intentionally has no chainable -> equivalent.

Navigate, Compare, and Index

SELECT payload->'user'->>'name'
FROM events;

CREATE INDEX events_user_name_idx
ON events (bson_get_string(payload, 'user.name'));

CREATE INDEX events_payload_btree_idx ON events (payload);
CREATE INDEX events_payload_hash_idx ON events USING hash (payload);

Version 2.1 provides logical comparison operators =, <>, <, <=, >, and >=; == and <<>> perform binary equality and inequality. The default btree operator class uses logical BSON comparison, while the hash operator class uses binary equality. Choose intentionally when field order or byte identity matters.

Upgrade and Caveats

ALTER EXTENSION pgbson UPDATE TO '2.1';

• Installing a 2.1 shared library does not update an existing 2.0 extension’s SQL objects; run the extension update after installing the files.
• The 2.1 shared library fixes a backend crash when bson_get_bson() or -> resolves to a scalar endpoint. Earlier binaries should be replaced even when an application does not yet use the new 2.1 SQL function.
• BSON-to-JSON/JSONB casts use Extended JSON. BSON and JSONB have different type, equality, and ordering semantics, so conversion is not lossless for every workflow.
• In 2.1, ->> on a BSON datetime includes the trailing Z; bson_get_datetime() is unchanged. Check clients that compare the old text form.
• BSON top-level values are documents, not bare arrays or scalars. bson_get_value uses its _ wrapper to return any nested shape within that restriction.