Why Do You Need JSON Patch Functions?

Imagine you’re building a data platform. System A produces 100,000 user change records per minute, and System B syncs user profiles. You need to compare the two systems, identify differences, and apply changes.

Before DuckDB v2.0, such operations had to be done outside SQL—writing complex JSON processing logic in Python or Java. Now, DuckDB v2.0 introduces four JSON patch functions that let you complete the entire data reconciliation workflow directly in SQL.

DuckDB v2.0 Four JSON Patch Functions Explained

1. json_merge_patch_diff: Computing the Minimal Incremental Patch

json_merge_patch_diff(orig, modified) returns an RFC 7396 formatted patch such that json_merge_patch(orig, patch) = modified.

-- Basic usage
SELECT json_merge_patch_diff(
    '{"a":1,"b":2,"c":3}',
    '{"a":1,"b":99,"d":4}'
) AS patch;
-- Result: {"c":null,"b":99,"d":4}
-- a is unchanged (omitted), b changed, c deleted (marked null), d is new

Recursive handling of nested objects:

SELECT json_merge_patch_diff(
    '{"user":{"name":"Alice","age":30}}',
    '{"user":{"name":"Alice","age":31}}'
) AS patch;
-- Result: {"user":{"age":31}}
-- Only the changed path appears in the patch

This is particularly useful in CDC (Change Data Capture) pipelines—most events change only one or two fields out of dozens. Sending the patch instead of the full state can compress the change payload to a tiny fraction of its original size.

2. json_deep_merge: Recursive Merge with “Skip on Null” Semantics

Unlike json_merge_patch (where RFC 7396 dictates that null deletes the key), json_deep_merge treats null values as “keep the original value.”

-- json_merge_patch: null deletes the key
SELECT json_merge_patch('{"a":1,"b":2}', '{"b":null}');
-- Result: {"a":1}

-- json_deep_merge: null preserves the original value
SELECT json_deep_merge('{"a":1,"b":2}', '{"b":null}');
-- Result: {"a":1,"b":2}

Typical multi-source data fusion scenario:

-- Two upstream systems each update only partial fields
SELECT json_deep_merge(
    '{"columnName":"user_id","parentColumn":null}',
    '{"columnName":null,"parentColumn":"accounts.id"}'
) AS merged;
-- Result: {"columnName":"user_id","parentColumn":"accounts.id"}
-- Fields unknown to each system are null, and deep_merge preserves original values

Supports variadic arguments—multiple patches apply left to right:

SELECT json_deep_merge(
    '{"a":1}',
    '{"a":null}',    -- skip
    '{"a":2}'        -- overwrite
);
-- Result: {"a":2}

3. json_normalize: Canonical Key Ordering

When two services emit the same JSON object with different key orders, how do you determine if they’re the same? json_normalize recursively sorts all object keys while preserving array element order.

SELECT json_normalize('{"z":1,"a":2,"m":3}');
-- Result: {"a":2,"m":3,"z":1}

SELECT json_normalize('{"c":{"b":{"z":1,"a":2},"a":3},"a":4}');
-- Result: {"a":4,"c":{"a":3,"b":{"a":2,"z":1}}}

-- Deduplication: same content but different key order produces the same hash
SELECT md5(json_normalize('{"z":1,"a":2}')) = 
       md5(json_normalize('{"a":2,"z":1}')) AS same_hash;
-- Result: true

4. json_strip_nulls: Recursively Remove Null Values

SELECT json_strip_nulls('{"a":1,"b":null,"c":null,"d":2}');
-- Result: {"a":1,"d":2}

SELECT json_strip_nulls('{"a":{"x":1,"y":null},"b":2}');
-- Result: {"a":{"x":1},"b":2}

-- Array elements are unaffected (null is a valid array element)
SELECT json_strip_nulls('{"a":[{"x":1,"y":null},{"z":null}]}');
-- Result: {"a":[{"x":1,"y":null},{}]}

End-to-End CDC Reconciliation in Practice

Assume your upstream system marks “unknown fields” as null in each change event. The goal is: clean the event, compute the minimal patch, and store a normalized hash.

-- Create tables
CREATE TABLE catalog (id INTEGER, state JSON);
CREATE TABLE incoming_events (id INTEGER, event JSON);

-- Insert current state
INSERT INTO catalog VALUES (
    1,
    '{"typeName":"Column","name":"user_id","description":"primary key",
      "dataType":"BIGINT","ownerEmail":"[email protected]"}'
);

-- Insert incoming event (partial fields are null)
INSERT INTO incoming_events VALUES (
    1,
    '{"name":"user_id","description":"primary key for the users table",
      "dataType":"BIGINT","ownerEmail":null,"team":null}'
);

-- Complete reconciliation workflow (single SQL)
WITH cleaned AS (
    -- Step 1: Clean null placeholders
    SELECT json_strip_nulls(event) AS clean_event
    FROM incoming_events
    WHERE id = 1
),
merged AS (
    -- Step 2: Deep merge (missing fields keep original values)
    SELECT json_deep_merge(c.state, cl.clean_event) AS merged_state
    FROM catalog c, cleaned cl
),
patched AS (
    -- Step 3: Compute minimal patch
    SELECT json_merge_patch_diff(c.state, m.merged_state) AS patch
    FROM catalog c, merged m
)
-- Step 4: Apply patch and compute normalized hash
SELECT 
    patch,
    md5(json_normalize(json_merge_patch(c.state, p.patch))) AS content_hash
FROM catalog c, patched p
WHERE c.id = 1;

Result Analysis:

dataType is unchanged so it doesn’t appear in the patch. ownerEmail and team are cleaned and also don’t appear. The final patch contains only genuinely changed fields.

Performance Comparison: DuckDB vs Python

The DuckDB official blog benchmarked on 500,000 CDC events:

Sources of Performance Advantage:

1. DuckDB operates directly on yyjson trees in place, avoiding Python’s json.loads/json.dumps overhead
2. Vectorized execution mode, processing 2048 values per row
3. Multi-threaded parallel processing

Comparison with Traditional Tools

How to Try It Early with v1.5.x

Although v2.0 official release is not yet available, you can test through the following methods:

# Install DuckDB v2.0-dev preview
pip install duckdb --pre

import duckdb
con = duckdb.connect(":memory:")
con.execute("LOAD json;")

# Test json_normalize
result = con.execute("SELECT json_normalize('{\"z\":1,\"a\":2}');").fetchone()
print(result)  # ('{"a":2,"z":1}',)

Monetization: How to Make Money with JSON Reconciliation Skills

1. Data Reconciliation SaaS Service

Target Customers: Enterprises needing multi-system data synchronization (e-commerce, finance, SaaS)

Business Model:

• Per-record pricing: $0.001/record
• Monthly subscription: $299-$999/month
• Enterprise custom: $5,000+/month

Key Selling Points:

• Real-time reconciliation, second-level discrepancy detection
• Automatic minimal patch computation, reducing downstream sync volume
• Native SQL interface, no coding required

Technical Architecture:

Upstream System A → CDC → DuckDB (json_deep_merge + patch) → Downstream System B
Upstream System C → CDC → DuckDB (json_merge_patch_diff) → Differential Reports

2. Data Quality Monitoring Service

Target Customers: Data platform teams, data engineers

Product Forms:

• Self-service data quality dashboard
• Automated reconciliation report generation
• Real-time anomaly change alerts

Pricing:

• Basic: $99/month (100K records/month)
• Professional: $499/month (1M records/month)
• Enterprise: Custom pricing

3. Data Migration Tool

Scenario: Enterprise migration from MongoDB/document databases to relational databases

Process:

1. Use json_normalize to unify document formats from different sources
2. Use json_strip_nulls to clean invalid data
3. Use json_deep_merge to merge multi-version documents
4. Batch write to target tables

Pricing: Per-data-volume pricing, $0.01/MB

4. Technical Consulting & Training

Services:

• Enterprise CDC pipeline architecture design
• DuckDB JSON function performance optimization
• Data reconciliation best practices training

Pricing:

• Consulting: $200-$500/hour
• Enterprise training: $5,000-$20,000/day

Summary

DuckDB v2.0’s four JSON patch functions solve long-standing pain points in the data reconciliation field. Through the combination of json_merge_patch_diff, json_deep_merge, json_normalize, and json_strip_nulls, you can:

1. Complete operations that previously required Python code, directly in SQL
2. Achieve 10-123x performance improvement
3. Build real-time data reconciliation SaaS products

These functions are not limited to CDC pipelines—they can also be used for API response normalization, configuration management, version control, and many other scenarios. Start experiencing them in v2.0-dev today!