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":"[email protected]"}',
  '{"age":31,"phone":"13800138000"}'
) AS merged_result;

Output:

{"name":"Zhang San","age":31,"email":"[email protected]","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": "[email protected]"}
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.

Eight. Monetization: Turning This Into a Product

Mastering json_merge_patch opens up several paid product opportunities:

1. Data Alignment SaaS

Enterprises have data scattered across ERP, CRM, OA, and other systems. Every report requires manual alignment. Package DuckDB + json_merge_patch as an online tool where users upload multiple CSV/JSON files and get auto-merged output. Subscription model: 99~499 CNY/month.

2. Automated ETL Pipeline Service

Many small and medium businesses lack data engineers but have data integration needs. Build automated ETL pipelines with DuckDB for them — daily scheduled pulls from multiple sources, merged with json_merge_patch, written to target databases. Project-based pricing: 3,000~10,000 CNY per client.

3. Data Quality Report Service

Combined with v2.0’s json_merge_patch_diff, you can offer data change-tracking services. Tell clients “A and B systems have 3 inconsistent fields for user data” — this diagnostic service itself is billable.

4. Log Standardization Middleware

API gateway logs arrive in varying formats. Use DuckDB for real-time standardization and output uniform format to downstream analytics systems. This is a classic middleware scenario — package as a microservice for sale.

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