pg_json_diff

Overview

Source requires PostgreSQL 12+ and C++17. Packaging removes the unsupported control-file author field; no extension dependency.

Version

Build

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

pig build pkg pg_json_diff         # build RPM / DEB packages

Install

You can install pg_json_diff 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 pg_json_diff;          # Install for current active PG version

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

dnf install -y pg_json_diff_18       # PG 18
dnf install -y pg_json_diff_17       # PG 17
dnf install -y pg_json_diff_16       # PG 16
dnf install -y pg_json_diff_15       # PG 15
dnf install -y pg_json_diff_14       # PG 14

apt install -y postgresql-18-pg-json-diff   # PG 18
apt install -y postgresql-17-pg-json-diff   # PG 17
apt install -y postgresql-16-pg-json-diff   # PG 16
apt install -y postgresql-15-pg-json-diff   # PG 15
apt install -y postgresql-14-pg-json-diff   # PG 14

Create Extension:

CREATE EXTENSION pg_json_diff;

Usage

Sources:

• Official README
• Extension SQL
• C++ implementation

pg_json_diff 1.0 provides three immutable JSONB transformation functions backed by nlohmann/json: generate an RFC 6902 JSON Patch, apply such a patch, or apply an RFC 7386 merge patch. Use it for explicit document transformations, not as automatic conflict resolution or a stable audit-diff format.

Core Workflow

Generate a patch, store or inspect it, and verify that applying it produces the expected target:

CREATE EXTENSION pg_json_diff;

WITH docs AS (
  SELECT '{"name":"Ada","age":30}'::jsonb AS old_doc,
         '{"name":"Ada","age":31,"city":"London"}'::jsonb AS new_doc
), patch AS (
  SELECT old_doc, new_doc, generate_json_diff(old_doc, new_doc) AS operations
  FROM docs
)
SELECT operations, apply_patch(old_doc, operations) = new_doc AS verified
FROM patch;

Validate externally supplied patches before applying them and run the transformation in a transaction with application-level authorization.

Function Index

• generate_json_diff(source, target) returns an RFC 6902 operation array.
• apply_patch(document, patch) supports add, remove, replace, move, copy, and test operations.
• merge_patch(document, patch) recursively merges objects; a null member removes a key and arrays are replaced rather than merged.

All three functions accept and return jsonb.

Operational Notes

Each call serializes PostgreSQL JSONB to text, parses it into a C++ JSON value, performs the operation, serializes again, and reparses the result as JSONB. Large or deeply nested documents therefore incur copying and parser cost. nlohmann/json and PostgreSQL JSONB have different numeric models, so test very large integers and high-precision decimals for fidelity. Patch errors are surfaced as general internal errors, and generated operation order or paths should not be treated as a canonical human audit record.