Do you ever face this scenario:
Your company has 5 systems—CRM, ERP, Finance, E-commerce, Customer Service—each storing a copy of user information. Today the boss wants a complete user profile report, and you need to merge all 5 JSON sources into one clean dataset.
Writing this in Python means loops, null handling, field conflict resolution, nested structure management… a dozen lines of code just for one merge logic. And when the data scale grows, performance tanks.
DuckDB provides a function called json_merge_patch—based on RFC 7396—that handles all merge logic in a single SQL line.
1. What is json_merge_patch?
json_merge_patch(base, patch) is DuckDB’s built-in JSON merge function, based on the RFC 7396 standard. The working principle is straightforward:
- Apply the fields from the second argument (patch) onto the first argument (base)
- Same fields: patch value overwrites base value
- Different fields: automatically complementary, nothing gets lost
- Nested objects: recursive merge, not simple replacement
SELECT json_merge_patch(
'{"name":"Alice","age":30,"email":"[email protected]"}',
'{"age":31,"phone":"13800138000"}'
) AS merged_result;
Result:
{"name":"Alice","age":31,"email":"[email protected]","phone":"13800138000"}
Key observations:
nameandemailcome from base, since patch doesn’t have themageis overwritten from30to31by the patchphoneis a new field from patch, automatically appended
2. Three Core Behaviors You Must Know
Behavior 1: Recursive Merge for Nested Objects
This is the most valuable feature of json_merge_patch. Ordinary JSON merge often replaces the entire nested object, but DuckDB merges recursively:
SELECT json_merge_patch(
'{"user":{"name":"Alice","settings":{"theme":"dark","lang":"en"}}}',
'{"user":{"settings":{"lang":"zh"}}}'
) AS result;
Result:
{"user":{"name":"Alice","settings":{"theme":"dark","lang":"zh"}}}
theme didn’t disappear—it only existed in base, and patch didn’t touch it. Only lang was overwritten.
Comparison with Python:
import json
base = {"user": {"name": "Alice", "settings": {"theme": "dark", "lang": "en"}}}
patch = {"user": {"settings": {"lang": "zh"}}}
# Direct update loses theme
base["user"].update(patch["user"]) # ❌ settings replaced entirely, theme lost!
# Need a recursive merge function
def deep_merge(a, b):
for k, v in b.items():
if k in a and isinstance(a[k], dict) and isinstance(v, dict):
deep_merge(a[k], v)
else:
a[k] = v
deep_merge(base, patch) # ✅ Requires 10+ lines of code
Behavior 2: null Values Mean “Delete Field”
This is the RFC 7396 standard behavior, and also a common pitfall:
SELECT json_merge_patch(
'{"a":1,"b":2,"c":3}',
'{"b":null}'
) AS result;
Result:
{"a":1,"c":3}
Field b is deleted. If you want to preserve a null value in the patch, that’s not possible—null in RFC 7396 always means “delete operation.”
💡 Practical scenario: This behavior is especially useful for “incremental updates.” For example, when a user uploads a new avatar, you only need to pass
{"avatar": "new_url.jpg"}—no need to send the entire user object.
Behavior 3: Arrays Are Replaced Entirely, Not Merged
SELECT json_merge_patch(
'{"tags":["a","b","c"]}',
'{"tags":["d","e"]}'
) AS result;
Result:
{"tags":["d","e"]}
The array is completely replaced, not appended. If you need array merging, use other approaches like array_concat.
3. Practical Scenarios: From API to Production
Scenario 1: Multi-Source User Profile Merge
Assume your company has 3 data sources, each storing partial user information:
import duckdb
con = duckdb.connect(':memory:')
# Source A: HR system
con.execute("""
CREATE TABLE hr_data AS
SELECT 'U001' AS user_id,
'{"name":"Li Si","department":"Engineering","level":"P6","hire_date":"2020-03-15"}'::VARCHAR AS profile
""")
# Source B: Attendance system
con.execute("""
CREATE TABLE attendance_data AS
SELECT 'U001' AS user_id,
'{"attendance_days":22,"overtime_hours":15,"status":"active"}'::VARCHAR AS extra
""")
# Source C: Membership system
con.execute("""
CREATE TABLE vip_data AS
SELECT 'U001' AS user_id,
'{"membership":"gold","points":15000,"expiry":"2027-01-01"}'::VARCHAR AS vip_info
""")
# Merge all three sources
result = con.execute("""
SELECT
h.user_id,
json_merge_patch(
json_merge_patch(h.profile, a.extra),
c.vip_info
) AS full_profile
FROM hr_data h
JOIN attendance_data a ON h.user_id = a.user_id
JOIN vip_data c ON h.user_id = c.user_id
""").fetchone()
print(result[1])
# {"name":"Li Si","department":"Engineering","level":"P6","hire_date":"2020-03-15",
# "attendance_days":22,"overtime_hours":15,"status":"active",
# "membership":"gold","points":15000,"expiry":"2027-01-01"}
Fields from all three systems auto-align. Same fields get overwritten by later sources, different fields complement each other. No Python loops needed.
Scenario 2: Batch Table Merge
When your data is already in tables, just JOIN and merge:
CREATE TABLE user_basic AS
SELECT 'U001' AS user_id, '{"name":"Wang Wu","age":28}' AS profile_json;
CREATE TABLE user_extra AS
SELECT 'U001' AS user_id, '{"city":"Beijing","membership":"gold"}' AS extra_json;
SELECT
a.user_id,
json_merge_patch(a.profile_json, b.extra_json) AS full_profile
FROM user_basic a
JOIN user_extra b ON a.user_id = b.user_id;
Scenario 3: Extract from Logs/API Responses
log_json = '{"timestamp":"2026-09-10T10:00:00Z","level":"ERROR","message":"Connection timeout","service":"api-gateway"}'
# Extract key fields and build structured records
con = duckdb.connect(':memory:')
result = con.execute(f"""
SELECT
json_extract_scalar('{log_json}', '$.timestamp') AS ts,
json_extract_scalar('{log_json}', '$.level') AS level,
json_extract_scalar('{log_json}', '$.service') AS service,
json_extract_scalar('{log_json}', '$.message') AS message
""").fetchone()
print(f'[{result[0]}] {result[1]} in {result[2]}: {result[3]}')
# [2026-09-10T10:00:00Z] ERROR in api-gateway: Connection timeout
Scenario 4: ATTACH Multi-Database Joint Query
This is the most powerful usage. You have multiple DuckDB files, each storing data of different dimensions:
import duckdb
# Attach multiple data sources
con = duckdb.connect(':memory:')
con.execute("ATTACH 'crm.duckdb' AS crm")
con.execute("ATTACH 'erp.duckdb' AS erp")
con.execute("ATTACH 'finance.duckdb' AS finance")
# Cross-database join + JSON merge
con.execute("""
CREATE TABLE unified_customer AS
SELECT
c.customer_id,
json_merge_patch(
json_merge_patch(c.crm_profile, e.erp_profile),
f.finance_profile
) AS full_profile
FROM crm.main.customers c
JOIN erp.main.customers e ON c.customer_id = e.customer_id
JOIN finance.main.customers f ON c.customer_id = f.customer_id
""")
# Query integrated data directly
result = con.execute("""
SELECT customer_id, full_profile
FROM unified_customer
WHERE json_extract_scalar(full_profile, '$.crm_profile.level') = 'VIP'
""").fetchall()
This means you can store data in separate files (isolated by business domain) and ATTACH them when needed for joint queries—without any ETL data migration.
4. Performance Benchmark: DuckDB vs Python
Test with 10,000 records:
import duckdb, time, json
con = duckdb.connect(':memory:')
# Prepare 10k test records
profiles = [(f'U{i}', json.dumps({'name': f'User{i}', 'age': i % 100, 'email': f'u{i}@test.com'})) for i in range(10000)]
extras = [(f'U{i}', json.dumps({'city': ['Beijing','Shanghai','Guangzhou','Shenzhen'][i%4], 'membership': ['gold','silver','bronze'][i%3]})) for i in range(10000)]
con.execute('CREATE TABLE profiles (user_id VARCHAR, profile VARCHAR)')
con.execute('CREATE TABLE extras (user_id VARCHAR, extra VARCHAR)')
for p in profiles:
con.execute('INSERT INTO profiles VALUES (?, ?)', p)
for e in extras:
con.execute('INSERT INTO extras VALUES (?, ?)', e)
con.commit()
# DuckDB approach
start = time.time()
result = con.execute("""
SELECT a.user_id, json_merge_patch(a.profile, b.extra) AS merged
FROM profiles a JOIN extras b ON a.user_id = b.user_id
""").fetchall()
duckdb_time = time.time() - start
# Python approach
start = time.time()
merged_py = []
for a, b in zip(profiles, extras):
merged = json.loads(a[1])
extra = json.loads(b[1])
merged.update(extra)
merged_py.append((a[0], json.dumps(merged)))
python_time = time.time() - start
print(f'DuckDB: {duckdb_time:.4f}s')
print(f'Python: {python_time:.4f}s')
print(f'Speedup: {python_time/duckdb_time:.1f}x')
Results:
| Approach | 10,000 rows | Per-row |
|---|---|---|
| DuckDB json_merge_patch | 0.052s | 0.005ms |
| Python json.loads + update | 0.104s | 0.010ms |
| Speedup | — | 2.0x |
The larger the data, the more DuckDB pulls ahead. Reasons:
- Columnar storage—JSON fields read on-demand
- Vectorized execution—processes entire columns at once, not row by row
- More efficient memory management—no frequent GC pauses like Python
5. Common Pitfalls and Solutions
Pitfall 1: Nested Arrays Don’t Merge Recursively
-- ❌ Expected: tags merged to ["a","b","c","d"]
SELECT json_merge_patch(
'{"tags":["a","b"]}',
'{"tags":["c","d"]}'
);
-- ✅ Actual result: {"tags":["c","d"]} — array replaced entirely
Solution: Use json_transform for manual array merging:
SELECT json_transform(
'{"tags":["a","b"]}',
'$.tags'::JSONPATH,
'array_concat($, $.tags)'
)
Or merge arrays in Python before passing to DuckDB.
Pitfall 2: null Deletes Fields
If you want null in the patch to mean “don’t update” instead of “delete”, use json_deep_merge (new in v2.0):
-- v2.0: null values are skipped, fields not deleted
SELECT json_deep_merge(
'{"a":1,"b":2}',
'{"b":null,"c":3}'
);
-- Result: {"a":1,"b":2,"c":3} — b retains original value
Pitfall 3: Key Order Differences Cause Comparison Failures
DuckDB outputs JSON with deterministic key order (insertion order), while Python’s json.dumps sorts alphabetically by default. String comparison will fail:
# DuckDB output: {"name":"User0","age":0,"email":"[email protected]","city":"Beijing"}
# Python output: {"age":0,"city":"Beijing","email":"[email protected]","name":"User0"}
# Strings differ, but semantics are identical!
Solution: Parse and compare as dictionaries, not strings.
Pitfall 4: Invalid JSON Strings Throw Errors
-- ❌ Will error
SELECT json_merge_patch('not json', '{"a":1}');
-- ✅ Check validity first
SELECT CASE
WHEN json_valid('{bad json') THEN json_merge_patch('{bad json', '{}')
ELSE NULL
END;
6. Combining with Other JSON Functions
json_merge_patch is rarely used alone—typically combined with other functions:
-- 1. Merge + filter null fields
SELECT json_strip_nulls(
json_merge_patch(base_json, patch_json)
) FROM ...
-- 2. Merge + normalize key order (for dedup/hash)
SELECT json_normalize(
json_merge_patch(a.json, b.json)
) FROM ...
-- 3. Conditional merge (only merge non-null fields)
SELECT json_merge_patch(
base_json,
CASE WHEN patch_json IS NOT NULL THEN patch_json ELSE '{}' END
) FROM ...
-- 4. Extract specific field after merge
SELECT json_extract(
json_merge_patch(a.json, b.json),
'$.contact.email'
) FROM ...
7. Monetization Strategies: What Can json_merge_patch Earn You?
1. Multi-Source Data Integration Service
- Pain point: SMEs have data scattered across CRM, ERP, Excel—integration costs are high
- Solution: Use DuckDB ATTACH + json_merge_patch to merge multiple sources in one SQL layer, no ETL pipeline needed
- Pricing: Project-based $500-1,500 per engagement, or subscription $70-300/month per data source
2. API Data Cleaning Pipeline
- Pain point: Third-party API JSON formats are inconsistent, requiring cleanup and normalization
- Solution: Build an automated cleaning pipeline with json_merge_patch + json_strip_nulls
- Pricing: SaaS model at $15-70/month per user
3. User Profiling Tool
- Pain point: Marketing teams need to integrate multi-channel user data for 360° profiles
- Solution: DuckDB as local data lake, ATTACH channel data, json_merge_patch for real-time merging
- Pricing: Embedded in consulting reports, $70-300 per report
4. Data Quality Monitoring as a Service
- Pain point: Clients complain about field inconsistencies across data sources
- Solution: Use json_merge_patch for difference detection + automatic reconciliation with alerting
- Pricing: $30-150/month based on monitored sources
Summary
The core value of json_merge_patch: turning multi-source JSON merge from “write loops to process” into “one SQL line.”
Three key takeaways:
- Nested objects merge recursively—parent-level non-overlapping fields don’t interfere
- null means delete—best partner for incremental updates
- Arrays are replaced entirely—combine with
array_concatwhen you need array merging
Combined with DuckDB’s ATTACH capability, you can merge JSON across database files without moving data—especially valuable for data-sensitive scenarios where centralization isn’t an option.
📖 More detailed json_merge_patch practical cases and complete code repository at duckdblab.org
💡 Want to systematically learn DuckDB data processing techniques? duckdblab.org has a complete tutorial series from beginner to advanced, covering JSON, Parquet, time series and other high-frequency scenarios.