Modifying JSONB — Building, Updating, and Merging Documents

JSONB is not read-only. PostgreSQL provides a rich set of functions and operators for constructing new documents, updating existing ones, merging documents together, and removing keys — all without leaving SQL.

JSONB modification functions

Example 1: Building JSONB from query results

jsonb_build_object is the primary tool for constructing JSONB from dynamic query results — useful for building API responses, creating audit records, or populating JSONB columns from existing data:

-- Build a JSONB summary document per client
SELECT
    c.company_name,
    jsonb_build_object(
        'client_id',      c.client_id,
        'industry',       c.industry,
        'country',        c.country,
        'account_manager', c.account_manager,
        'invoice_count',  COUNT(i.invoice_id),
        'total_billed',   ROUND(SUM(ii.quantity * ii.unit_price), 2),
        'paid_pct',       ROUND(100.0 * SUM(CASE WHEN i.status='paid'
                               THEN ii.quantity * ii.unit_price ELSE 0 END)
                               / NULLIF(SUM(ii.quantity * ii.unit_price), 0), 1),
        'statuses',       jsonb_agg(DISTINCT to_jsonb(i.status)),
        'last_invoice',   MAX(i.issue_date)
    ) AS client_summary
FROM clients c
JOIN invoices i ON i.client_id = c.client_id
JOIN invoice_items ii ON ii.invoice_id = i.invoice_id
GROUP BY c.client_id, c.company_name, c.industry, c.country, c.account_manager
ORDER BY c.company_name;

jsonb_agg is the JSONB-specific version of ARRAY_AGG — it aggregates values into a JSONB array rather than a PostgreSQL array. Use it when you need JSONB output; use ARRAY_AGG when you need a PostgreSQL array.

Example 2: jsonb_set — updating a nested field

jsonb_set(target, path, new_value, create_missing) sets a specific path within an existing document. The path is an array literal (e.g. '{key}' or '{nested,key}'). The fourth parameter controls whether to create the path if it doesn't exist.

-- Add a 'last_updated' timestamp to all product attributes
UPDATE product_catalog
SET attributes = jsonb_set(
    attributes,
    '{last_updated}',
    to_jsonb(NOW()::TEXT),
    true  -- create if missing
)
WHERE category = 'Electronics';

-- Update a nested value: increase ram_gb by 8 for all eligible products
UPDATE product_catalog
SET attributes = jsonb_set(
    attributes,
    '{ram_gb}',
    to_jsonb((attributes ->> 'ram_gb')::INT + 8)
)
WHERE category = 'Electronics'
  AND (attributes ->> 'ram_gb')::INT < 32;

-- Verify the update
SELECT name, attributes ->> 'ram_gb' AS ram_gb, attributes ->> 'last_updated' AS updated
FROM product_catalog WHERE category = 'Electronics';

Example 3: The || merge operator

The || operator performs a shallow merge of two JSONB objects. Keys from the right document overwrite keys from the left:

-- Merge additional metadata into existing attributes
WITH enrichment AS (
    SELECT
        product_id,
        jsonb_build_object(
            'in_stock',     TRUE,
            'warehouse',    'London-1',
            'last_checked', CURRENT_DATE::TEXT
        ) AS extra_data
    FROM product_catalog
    WHERE category = 'Electronics'
)
UPDATE product_catalog pc
SET attributes = pc.attributes || e.extra_data
FROM enrichment e
WHERE e.product_id = pc.product_id;

-- The || operator does a SHALLOW merge — if both documents have 'specs' object,
-- the right-side 'specs' completely replaces the left-side 'specs'.
-- For deep merge, use jsonb_set or a custom function.

-- Demonstrate: remove staging keys before production export
SELECT
    name,
    attributes - ARRAY['last_updated', 'warehouse', 'last_checked'] AS clean_attributes
FROM product_catalog
WHERE category = 'Electronics';

Example 4: Transforming relational data to JSONB — the aggregation pattern

A common data pipeline requirement: take normalised relational data and aggregate it into JSONB documents for export to an API or document store. This example builds a complete order document with embedded line items:

SELECT
    o.order_id,
    jsonb_build_object(
        'order_id',    o.order_id,
        'order_date',  o.created_at::DATE,
        'status',      o.status,
        'customer',    jsonb_build_object(
            'id',       c.customer_id,
            'country',  c.country,
            'channel',  c.acquisition_channel
        ),
        'items', jsonb_agg(
            jsonb_build_object(
                'product_id',   p.product_id,
                'name',         p.name,
                'category',     p.category,
                'quantity',     oi.quantity,
                'unit_price',   oi.unit_price,
                'line_total',   ROUND(oi.quantity * oi.unit_price, 2)
            )
            ORDER BY p.category, p.name
        ),
        'totals', jsonb_build_object(
            'subtotal',       o.total_amount,
            'discount',       o.discount_amount,
            'item_count',     SUM(oi.quantity),
            'unique_products', COUNT(DISTINCT oi.product_id)
        )
    ) AS order_document
FROM ec_orders o
JOIN ec_customers c ON c.customer_id = o.customer_id
JOIN ec_order_items oi ON oi.order_id = o.order_id
JOIN ec_products p ON p.product_id = oi.product_id
WHERE o.status = 'completed'
GROUP BY o.order_id, o.created_at::DATE, o.status, o.total_amount, o.discount_amount,
         c.customer_id, c.country, c.acquisition_channel
ORDER BY o.created_at::DATE DESC
LIMIT 5;

Key Takeaway

JSONB modification follows three patterns: jsonb_build_object for construction, jsonb_set for surgical updates (one path at a time), and || for shallow merges. The - key operator removes keys cleanly. For transforming relational data into documents, jsonb_build_object + jsonb_agg is the standard approach. Use to_jsonb() to convert any PostgreSQL value (timestamp, integer, boolean) into a JSONB-compatible value.