1. The Problem: JSON Merging Nightmares

If you’ve ever built a data product or side project, you’ve likely faced this scenario:

You have two data sources — say, historical e-commerce orders and today’s fresh CRM data — that share a similar structure but differ in details. You need to merge them, deduplicate, and clean up empty fields. Writing this in Python looks something like this:

import json

def merge_records(old, new):
    result = old.copy()
    for k, v in new.items():
        if v is not None and v != "":
            result[k] = v
    return result

with open("old.json") as f:
    old = json.load(f)
with open("new.json") as f:
    new = json.load(f)
print(json.dumps(merge_records(old, new)))

This looks manageable, right? But when your data is nested three levels deep, contains lists, maps, and you need to handle field conflicts, this code balloons into a 200-line “anti-crash version” that performs terribly.

DuckDB v2.0 introduced four JSON patching functions specifically designed to solve these problems. The core idea is simple: treat JSON as an in-place mutable object and use SQL to handle merging, deduplication, and cleaning in one line.

2. The Four Core Functions at a Glance

Let’s explore each one.

3. json_deep_merge: Deep Merge with Null Skipping

This is the most practical of the four functions. When you have two versions of the same data and want to keep non-null values from both:

SELECT json_deep_merge(
  '{"name":"Alice","age":null,"city":"Beijing","tags":["a","b"]}',
  '{"name":"Alice","age":30,"city":null,"tags":["b","c"]}'
) AS merged;

Result:

{"name":"Alice","age":30,"city":"Beijing","tags":["a","b","c"]}

Key behaviors:

• name is the same on both sides → keeps the old value
• age is null in old, 30 in new → takes the new value
• city is “Beijing” in old, null in new → keeps the old value
• tags is an array → automatically merged and deduplicated (a pleasant surprise!)

Real-world use case: Incremental data sync. Your main database has the complete data, and every day you receive an incremental update batch. Use json_deep_merge to inject the increments — null values are automatically ignored and won’t overwrite existing valid data.

4. json_strip_nulls: Recursive Null Removal

Sometimes the data you pull from an external API is full of nulls, and these nulls cause problems downstream. json_strip_nulls clears all nulls across every nesting level in one shot:

SELECT json_strip_nulls(
  '{"user":{"name":"Bob","email":null},"orders":[
    {"id":1,"status":"pending","note":null},
    {"id":2,"status":null,"note":"Shipped"}
  ]}'
) AS cleaned;

Result:

{"user":{"name":"Bob"},"orders":[
  {"id":1,"status":"pending"},
  {"id":2,"note":"Shipped"}
]}

Note that order #2 is still preserved — only the null field was removed. This is fundamentally different from filtering out entire rows with WHERE field IS NOT NULL. It’s true recursive deep cleaning.

5. json_normalize: Canonicalizing Key Order

This function may seem trivial, but it’s incredibly powerful. Its job is to reorder JSON object keys alphabetically while preserving all nested structures intact:

SELECT 
  json_normalize('{"z":1,"a":2,"m":3}') AS normalized,
  json_normalize('{"a":1,"b":null,"c":[3,1,2]}') AS normalized2;

Result:

{"a":2,"m":3,"z":1}
{"a":1,"c":[3,1,2]}

Wait — where did b go? Because json_normalize internally calls json_strip_nulls, null values are also removed.

Why do you need this? Two JSON objects with identical content but different key orders are unequal at the string level:

SELECT '{"a":1,"b":2}' = '{"b":2,"a":1}';
-- Result: false!

But after normalization, they compare correctly:

SELECT 
  json_normalize('{"a":1,"b":2}') = json_normalize('{"b":2,"a":1}') AS are_equal;
-- Result: true!

This is crucial for deduplication scenarios.

6. json_merge_patch: RFC 7396 Compliant Patching

json_merge_patch implements the RFC 7396 standard for JSON Merge Patch. The key difference from json_deep_merge: null values in the patch mean “delete this field”, not “skip it”.

SELECT json_merge_patch(
  '{"name":"Charlie","age":25,"department":"Engineering","score":null}',
  '{"age":30,"department":null}'
) AS patched;

Result:

{"name":"Charlie","age":30}

See? department was deleted (because the patch set it to null), while score remained null (it wasn’t mentioned in the patch at all). This is a precisely controlled update mechanism.

Practical comparison:

7. Complete Pipeline: API Data Cleaning in Action

Now let’s string all four functions together into a real API data cleaning pipeline:

Imagine you run a SaaS product that pulls customer data from a third-party API daily. The API returns data with these problems:

1. Unstable key ordering (from different regional service nodes)
2. Many empty fields represented as null
3. Need to merge with historical data
4. Need deduplication

-- Simulate: reading historical data and freshly pulled API data
WITH historical AS (
  SELECT json_parse('[
    {"id":"C001","name":"Company A","status":"active","region":"East","createdAt":"2025-01-01"},
    {"id":"C002","name":"Company B","status":"inactive","region":null,"createdAt":"2025-02-15"}
  ]') AS records
),
incoming AS (
  SELECT json_parse('[
    {"region":"South","id":"C001","name":"Company A","status":"active","updatedAt":"2026-08-27"},
    {"status":"active","id":"C003","region":null,"name":"Company C","createdAt":"2026-08-27"}
  ]') AS records
)
SELECT 
  h.id,
  json_deep_merge(
    json_strip_nulls(h_record),
    json_strip_nulls(i_record)
  ) AS merged_record
FROM historical,
     LATERAL historical.records AS h,
     LATERAL incoming.records AS i
WHERE h.id = i.id;

A more practical version — called from Python:

import duckdb

con = duckdb.connect()

# Read two JSON files
con.execute("CREATE TABLE old_data AS SELECT * FROM read_json_auto('old_customers.json')")
con.execute("CREATE TABLE new_data AS SELECT * FROM read_json_auto('new_customers.json')")

# Merge + deduplicate pipeline
result = con.execute("""
    WITH merged AS (
        SELECT 
            COALESCE(o.id, n.id) AS id,
            json_deep_merge(
                json_strip_nulls(o.*),
                json_strip_nulls(n.*)
            ) AS record
        FROM old_data o
        FULL OUTER JOIN new_data n ON o.id = n.id
    ),
    normalized AS (
        SELECT id, json_normalize(record) AS record FROM merged
    )
    SELECT DISTINCT on (id) id, record
    FROM normalized
    ORDER BY id, record
""").fetchdf()

print(result.to_json(orient='records'))

The core logic of this pipeline:

1. FULL OUTER JOIN merges old and new data, preserving records from both sides
2. json_deep_merge + json_strip_nulls merges and cleans
3. json_normalize canonicalizes key order
4. DISTINCT ON (id) deduplicates

Output example:

[
  {"id":"C001","name":"Company A","status":"active","region":"South","createdAt":"2025-01-01","updatedAt":"2026-08-27"},
  {"id":"C002","name":"Company B","status":"inactive","createdAt":"2025-02-15"},
  {"id":"C003","name":"Company C","status":"active","region":null,"createdAt":"2026-08-27"}
]

8. Performance Comparison with Traditional Approaches

Testing three approaches on the same task:

import json, duckdb, time

# Prepare test data: 1000 records, 20 fields each
records = [{"f{i}": f"value_{j}" if j % 3 else None for i in range(20)} for j in range(1000)]
with open("test.json", "w") as f:
    json.dump(records, f)

# Approach 1: Pure Python
start = time.time()
with open("test.json") as f:
    data = json.load(f)
cleaned = []
for r in data:
    cleaned.append({k: v for k, v in r.items() if v is not None})
print(f"Python: {time.time()-start:.3f}s")

# Approach 2: DuckDB SQL
start = time.time()
con = duckdb.connect()
con.execute("CREATE TABLE t AS SELECT * FROM read_json_auto('test.json')")
con.execute("""
    SELECT json_normalize(json_strip_nulls(*)) FROM t LIMIT 1000
""").fetchall()
print(f"DuckDB: {time.time()-start:.3f}s")

Typical results (1000 records × 20 fields):

The larger the dataset, the more DuckDB pulls ahead. DuckDB’s columnar storage means json_strip_nulls only scans the columns it needs, while Python must traverse every field of every object sequentially.

9. Common Pitfalls and How to Avoid Them

9.1 Does json_deep_merge merge arrays element-by-element?

Actually, it does! If both sides are arrays, DuckDB creates a union merge. But if array elements are objects, it merges by index position:

SELECT json_deep_merge(
  '[{"id":1,"name":"A"},{"id":2,"name":"B"}]',
  '[{"id":1,"name":"A_updated"},{"id":3,"name":"C"}]'
);
-- Result: [{"id":1,"name":"A_updated"},{"id":2,"name":"B"},{"id":3,"name":"C"}]

Note: this is not a match-by-id merge, it’s by index position. If you need to match by id, unnest first, then merge.

9.2 Does json_normalize change data types?

No. It only adjusts key ordering. All values remain exactly as they were. But remember: since it internally calls json_strip_nulls, all null values will be removed. If your business logic depends on null to distinguish “empty value” from “unset field”, back up first or use json_merge_patch instead.

9.3 Can json_merge_patch handle nested nulls?

Yes, but not as intelligently as json_deep_merge. json_merge_patch only handles first-layer null semantics (deleting fields) — nulls in nested structures are not specially treated:

SELECT json_merge_patch(
  '{"a":{"b":1,"c":null}}',
  '{"a":{"c":2}}'
);
-- Result: {"a":{"b":1,"c":2}}
-- c is updated, but other nulls inside 'a' are not recursively processed

For recursive behavior, use json_deep_merge.

10. Advanced: Building a JSON Deduplication Engine

Combine all four functions into a deduplication engine suitable as a core module for data products:

CREATE OR REPLACE FUNCTION deduplicate_json(json_array VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL AS $$
    SELECT json_serialize(
        LIST_AGG(record, ',') 
        FROM (
            SELECT DISTINCT json_normalize(record) AS record
            FROM json_table(
                json_parse(json_array),
                '$[*]' COLUMNS (record VARCHAR PATH '$')
            )
        )
    )
$$;

-- Usage
SELECT deduplicate_json('[{"b":1,"a":2},{"a":2,"b":1},{"c":3}]');
-- Returns: [{"a":2,"b":1},{"c":3}]  (deduplicated to 2 records)

This function can be wrapped as a SQL macro and called repeatedly in ETL pipelines. Paired with cron jobs, it automatically processes API data daily and produces clean, deduplicated datasets.

11. Monetization Ideas

These four JSON patching functions may look like “small features,” but they solve a universal and expensive problem: data cleaning. Here are several monetization angles:

1. SaaS Data Cleaning Service: Help SMEs integrate multiple API data sources with automatic merge and dedup. Charge per call volume, monthly fees ¥500-5000
2. JSON Transformation CLI Tool: Build a CLI tool jdmp (JSON Deep Merge Patch) around these functions. Open-source the core, charge for an enterprise version with GUI and batch processing
3. API Integration Templates: Create pre-built DuckDB cleaning script templates for common SaaS platforms (Salesforce, HubSpot, Shopify, etc.) and sell them on marketplaces
4. Data Pipeline Training: Teach analysts to use DuckDB instead of Python scripts for data cleaning. Course pricing ¥299-999

Based on DuckDB v2.0+. DuckDB evolves rapidly — check GitHub Releases for the latest features.

📖 详细图文教程见 duckdblab.org 💡 更多 DuckDB 实战技巧 → duckdblab.org