Introduction

Have you ever faced this scenario at work: a user’s complete profile is scattered across multiple systems — HR has name and department, attendance system has work days, CRM has purchase history, and the data warehouse has behavioral tags. The fields don’t overlap consistently, some have the same names but different meanings, and some systems even store the entire profile as a single JSON string.

The traditional approach is writing a bunch of Python loops to extract fields one by one, resolve conflicts, and assemble a new object. The code is verbose, hard to maintain, and breaks whenever upstream schemas change.

DuckDB’s json_merge_patch function solves this elegantly. Following RFC 7396, its core logic is simple: later values override earlier ones, missing fields get filled in. One line of SQL replaces what would otherwise be dozens of lines of Python.

One. Basic Usage of json_merge_patch

The simplest merge

SELECT json_merge_patch(
 '{"name":"Zhang San","age":30,"email":"zhangsan@example.com"}',
 '{"age":31,"phone":"13800138000"}'
) AS merged_result;

Output:

{"name":"Zhang San","age":31,"email":"zhangsan@example.com","phone":"13800138000"}

The second record only provided age and phone, but all original data is preserved after merging. age gets overwritten (30 → 31), phone is added new, and name/email remain unchanged.

Understanding the merge logic

json_merge_patch(a, b) works as follows:

• If a key exists in both a and b, b’s value overrides a’s value
• If a key exists only in a, a’s value is kept
• If a key exists only in b, the new value from b is used
• If the value is a nested object, merge recursively instead of overwriting entirely

Two. Real-World Scenario 1: Customer Profile Consolidation

Imagine you’re a data engineer at a SaaS company, and you need to merge data from three systems into a unified customer profile.

Data Sources

DuckDB Merge Query

import duckdb

con = duckdb.connect(':memory:')

# Three data sources
crm_json = '{"customer_id":"C001","name":"Li Si","source":"sales_team"}'
biz_json = '{"customer_id":"C001","total_orders":15,"total_spend":28400}'
tag_json = '{"customer_id":"C001","segment":"vip","churn_risk":"low"}'

# Core merge: pairwise merge, recursive handling
result = con.execute(f"""
 SELECT json_merge_patch(
 json_merge_patch('{crm_json}', '{biz_json}'),
 '{tag_json}'
 ) AS full_profile
""").fetchone()

print(result[0])
# {"customer_id":"C001","name":"Li Si","source":"sales_team",
# "total_orders":15,"total_spend":28400,
# "segment":"vip","churn_risk":"low"}

Key points:

• customer_id exists in all tables — the later overrides the earlier, but since values are identical, no conflict
• Unique fields from each table (name, total_orders, segment) are all preserved
• Three levels of nesting also work: if a table’s value is a nested object, it merges recursively

Three. Real-World Scenario 2: Merging from Table Data

In practice, your data usually already lives in tables. Suppose you have two tables storing basic and extended info for the same users:

-- Create test data
CREATE TABLE user_basic (
 user_id VARCHAR,
 profile_json JSON
);

CREATE TABLE user_extra (
 user_id VARCHAR,
 extra_json JSON
);

-- Insert data
INSERT INTO user_basic VALUES
('U001', '{"name":"Wang Wu","age":28,"city":"Beijing"}'),
('U002', '{"name":"Zhao Liu","age":35,"city":"Shanghai"}'),
('U003', '{"name":"Qian Qi","age":22,"city":"Guangzhou"}');

INSERT INTO user_extra VALUES
('U001', '{"membership":"gold","points":15000,"join_date":"2023-01-15"}'),
('U002', '{"membership":"silver","points":8000,"join_date":"2022-06-20"}'),
('U003', '{"membership":"bronze","points":2000,"join_date":"2024-03-01"}');

The merge query is beautifully simple:

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;

Result:

Four. Real-World Scenario 3: Log Extraction + Standardization

When processing API logs or system events, you often need to extract key fields from raw JSON and merge them into a standardized format.

import duckdb

con = duckdb.connect(':memory:')

# Simulate API logs
logs = [
 '{"timestamp":"2026-09-10T10:00:00Z","level":"ERROR","message":"Connection timeout","service":"api-gateway"}',
 '{"timestamp":"2026-09-10T10:05:00Z","level":"WARN","message":"High memory usage","service":"data-pipeline","memory_pct":87}',
 '{"timestamp":"2026-09-10T10:10:00Z","level":"INFO","message":"Deployment complete","service":"deploy-service","version":"2.3.1"}',
]

# Extract key fields and merge into standardized format
result = con.execute(f"""
 SELECT 
 json_extract('{logs[0]}', '$.level') AS level,
 json_extract('{logs[0]}', '$.message') AS message,
 json_extract('{logs[0]}', '$.service') AS service,
 json_merge_patch(
 '{{"type":"log"}}',
 '{logs[0]}'
 ) AS standardized
""").fetchone()

print(result[0:3]) # ('ERROR', 'Connection timeout', 'api-gateway')
print(result[3]) # Fully standardized JSON

This technique is especially valuable for data standardization pipelines — logs from different sources have different formats, but with json_merge_patch plus a default template, you can unify everything into a规范 format.

Five. Advanced: Recursive Merge of Nested Objects

The most powerful feature of json_merge_patch is recursive merging. When two JSON objects contain nested objects, it doesn’t overwrite the entire nested object — it dives one level deeper and merges there.

SELECT json_merge_patch(
 '{"user":{"name":"Zhang San","preferences":{"theme":"dark","lang":"zh"}}}',
 '{"user":{"preferences":{"notifications":true}}}'
) AS merged;

Result:

{
 "user": {
 "name": "Zhang San",
 "preferences": {
 "theme": "dark",
 "lang": "zh",
 "notifications": true
 }
 }
}

Note that preferences didn’t lose theme and lang — notifications was simply added. This is the real value of recursive merging: you can update only part of a configuration without rewriting the entire object.

Six. Performance Comparison: DuckDB vs Python

Many people’s first instinct is to use Python for this kind of task. Let’s compare:

# Python approach
import json

profile_a = {"name": "Zhang San", "age": 30, "email": "zhangsan@example.com"}
profile_b = {"age": 31, "phone": "13800138000"}
profile_c = {"segment": "vip", "churn_risk": "low"}

# Manual merge — need to handle conflicts and missing keys
def deep_merge(base, update):
 result = base.copy()
 for key, value in update.items():
 if key in result and isinstance(result[key], dict) and isinstance(value, dict):
 result[key] = deep_merge(result[key], value)
 else:
 result[key] = value
 return result

merged = deep_merge(deep_merge(profile_a, profile_b), profile_c)

Comparison:

The key difference is batch processing: when facing millions of user records, Python loops will be painfully slow, while DuckDB’s vectorized execution makes the merge operation virtually free.

Seven. DuckDB v2.0 JSON Upgrade Preview

DuckDB v2.0 (codename Cyanoptera) is coming this autumn with 4 brand-new JSON functions:

json_deep_merge is particularly noteworthy — with standard json_merge_patch, if the second argument’s value is null, it will override the first argument’s value. But sometimes you want null to mean “don’t update” rather than “clear”. json_deep_merge is designed exactly for this scenario.

Summary

json_merge_patch is DuckDB’s core weapon for handling multi-source JSON data. It replaces extensive Python stitching logic with a single SQL call, supports recursive merging of nested objects, and integrates seamlessly into ETL pipelines.

Next time you face user data scattered across multiple systems, ask yourself: could json_merge_patch handle this?

💡 More DuckDB JSON实战技巧 → duckdblab.org

本文信息

如发现错误，欢迎通过 GitHub Issue 或邮件 contact@duckdblab.org 反馈。