PostgreSQL is already well-known for its rich JSON/JSONB support, making it one of the most flexible relational databases for handling semi-structured data. With functions like jsonb_set, jsonb_insert, jsonb_delete_path, and operators such as || for concatenation, developers have many tools at hand.
But there’s one big limitation: atomic JSON transformations are often verbose and scattered. Updating multiple keys, renaming, or appending values requires chaining several functions, each with its own quirks. This can quickly turn otherwise simple JSON operations into long, hard-to-maintain SQL queries.
That’s where the json_transform extension comes in. Inspired by Oracle’s json_transform, this PostgreSQL extension provides a single, unified function to perform a variety of JSONB modifications in one atomic step.
Why json_transform?
- Atomic operations: Multiple modifications can be applied in a single call, avoiding repetitive nesting of jsonb_set.
- Cleaner SQL: Instead of juggling several JSON functions, a simple declarative syntax defines what you want.
- Upserts made simple: Automatically insert or replace values without complex condition checks.
- Consistency: Brings PostgreSQL closer to feature parity with Oracle, which already supports JSON transformations out of the box.
Supported Operations
The extension currently supports the following commands, designed to cover the most common transformation scenarios:
1. SET
Overwrites the value if it exists; otherwise, creates it.
SET $.path.to.key = value
2. INSERT
Adds a new key/value only if it does not already exist.
INSERT $.path.to.key = value
3. APPEND
Appends a value to an existing array.
APPEND $.array_key = value
4. REMOVE
Removes a key or array element.
REMOVE $.path.to.key
5. REPLACE
Replaces the value only if the key exists (no-op if missing).
REPLACE $.path.to.key = value
6. RENAME
RENAME $.old_key TO new_key
Renaming a key inside a JSON document in PostgreSQL usually takes multiple steps. Without json_transform, you’d need to remove the old key and then rebuild the JSON with the new key name, which makes queries long and harder to read. With json_transform, the same task can be done in a single, simple statement. Here’s how you can do it:
Example with json_transform:
UPDATE profiles
SET profile_data = json_transform(
profile_data,
'RENAME $.prefs TO settings'
)
WHERE profile_data->>'user' = 'u1';
Equivalent in PostgreSQL:
UPDATE profiles
SET profile_data = (profile_data - 'prefs')
|| jsonb_build_object('settings', profile_data->'prefs')
WHERE profile_data->>'user' = 'u1';
Combining Multiple Operations
The real power of json_transform is combining operations in one call.
UPDATE profiles
SET profile_data = json_transform(
profile_data,
'REMOVE $.flags.beta',
'APPEND $.emails = "test@x.com"',
'SET $.flags.active = false',
'RENAME $.prefs TO settings'
)
WHERE profile_data->>'user' = 'u1';
Equivalent in PostgreSQL:
Would require multiple jsonb_set, #-, and concatenation calls — much harder to read and maintain.
Pros and Cons
Like any extension, json_transform has its strengths and trade-offs. Here’s a quick look at the advantages and limitations to help you decide when it’s the right tool for your use case.
Pros
- Single, atomic function for multiple operations
- Easier migration from Oracle JSON features
- Cleaner and more readable SQL queries
- Avoids boilerplate with conditional insert/replace logic
Cons
- Requires installing an extension (not built-in yet)
- Slight learning curve for the mini-syntax (SET, INSERT, etc.)
- Currently limited to explicit commands (does not support advanced jsonpath features like wildcards or recursion)
The json_transform extension brings a long-missing feature to PostgreSQL’s JSON ecosystem. By simplifying JSON modifications into a unified, declarative function, it reduces query complexity, improves readability, and aligns PostgreSQL closer to Oracle’s JSON capabilities.