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.

本文信息

如发现错误，欢迎通过 GitHub Issue 或邮件 [email protected] 反馈。