Introduction
In data synchronization, inventory management, and configuration versioning scenarios, you often need to merge two JSON data sources and detect differences. The traditional approach requires complex ETL scripts, but DuckDB’s built-in json_merge_patch, json_remove, and JSON type operations let you accomplish all of this directly with SQL.
This article uses a real-world e-commerce inventory sync scenario to demonstrate JSON merging, field-level diffing, and incremental updates in DuckDB.
Scenario: E-commerce Product Data Sync
You maintain two data sources:
- Original product catalog (
products_original): current online inventory - Updated product catalog (
products_updated): new data from procurement system
Your goals:
- Merge product specs (keep old fields + add new ones)
- Detect price changes
- Identify new and delisted products
Environment Setup
SELECT version();
DuckDB v1.5.2 fully supports all JSON functions — no additional extensions required.
Step 1: Create Test Data
-- Original product table (online inventory)
CREATE TABLE products_original AS
SELECT * FROM (VALUES
('P001', 'Mechanical Keyboard', 299.90, '{"color":"black","size":"full-size","weight":800}'::JSON),
('P002', 'Wireless Mouse', 149.90, '{"color":"white","dpi":3200,"weight":90}'::JSON),
('P003', 'Monitor Stand', 199.90, '{"material":"aluminum","max_weight":15000,"adjustable":true}'::JSON),
('P004', 'USB Hub', 89.90, '{"ports":7,"power_supply":false,"color":"silver"}'::JSON),
('P005', 'Noise-Cancelling Headphones', 899.90, '{"type":"over-ear","battery_life":30,"active_noise_cancellation":true}'::JSON)
) AS t(id, name, price, specs);
-- Updated product table (procurement system push)
CREATE TABLE products_updated AS
SELECT * FROM (VALUES
('P001', 'Mechanical Keyboard', 329.90, '{"color":"black","size":"full-size","weight":820,"rgb":true}'::JSON),
('P002', 'Wireless Mouse', 149.90, '{"color":"black","dpi":4000,"weight":95,"wireless_charging":true}'::JSON),
('P003', 'Monitor Stand', 179.90, '{"material":"aluminum","max_weight":20000,"adjustable":true,"tilt_range":15}'::JSON),
('P004', 'USB Hub', 79.90, '{"ports":7,"power_supply":true,"color":"black"}'::JSON),
('P006', 'Webcam Pro', 459.90, '{"resolution":"4K","fps":60,"auto_focus":true}'::JSON)
) AS t(id, name, price, specs);
SELECT * FROM products_original ORDER BY id;
Figure: E-commerce inventory sync flow — merging and comparing original and updated data
Step 2: JSON Merge with json_merge_patch
json_merge_patch implements the RFC 7396 JSON Merge Patch algorithm. It recursively merges two JSON objects:
- Fields present in the new object override old values
- Fields absent in the new object are preserved
- A
nullvalue indicates deletion
SELECT
p.id,
p.name,
json_merge_patch(p.specs, u.specs) AS merged_specs
FROM products_original p
JOIN products_updated u ON p.id = u.id;
Results:
| id | name | merged_specs |
|---|---|---|
| P001 | Mechanical Keyboard | {“color”:“black”,“size”:“full-size”,“weight”:820,“rgb”:true} |
| P002 | Wireless Mouse | {“color”:“black”,“dpi”:4000,“weight”:95,“wireless_charging”:true} |
| P003 | Monitor Stand | {“material”:“aluminum”,“max_weight”:20000,“adjustable”:true,“tilt_range”:15} |
| P004 | USB Hub | {“ports”:7,“power_supply”:true,“color”:“black”} |
Figure: Product specs after json_merge_patch merge
Key observations:
weightupdated from 800 to 820 (price adjustment)rgb,wireless_charging,tilt_range— new fields correctly mergedcolorchanged from"white"to"black"
Step 3: Field-Level Difference Detection
Extract and compare individual fields using json_extract:
SELECT
o.id,
o.name,
o.price AS original_price,
u.price AS new_price,
u.price - o.price AS price_change,
CASE WHEN o.specs IS DISTINCT FROM u.specs THEN 'specs_changed' ELSE 'specs_unchanged' END AS specs_status,
json_extract_string(u.specs, '$.rgb') AS new_rgb_feature
FROM products_original o
JOIN products_updated u ON o.id = u.id;
Results:
| id | name | original_price | new_price | price_change | specs_status | new_rgb_feature |
|---|---|---|---|---|---|---|
| P001 | Mechanical Keyboard | 299.90 | 329.90 | 30.00 | specs_changed | true |
| P002 | Wireless Mouse | 149.90 | 149.90 | 0.00 | specs_changed | null |
| P003 | Monitor Stand | 199.90 | 179.90 | -20.00 | specs_changed | null |
| P004 | USB Hub | 89.90 | 79.90 | -10.00 | specs_changed | null |
Key observations:
- P001 (Keyboard): price increased $30, new RGB lighting feature added
- P002 (Mouse): price unchanged, color changed from white to black, wireless charging added
- P003-P004: both have price reductions — good candidates for promotions
Step 4: Detect New and Delisted Products
Use EXCEPT or NOT IN to quickly identify data changes:
SELECT 'New Product' AS change_type, id, name, price FROM products_updated
WHERE id NOT IN (SELECT id FROM products_original)
UNION ALL
SELECT 'Delisted Product' AS change_type, id, name, price FROM products_original
WHERE id NOT IN (SELECT id FROM products_updated);
Results:
| change_type | id | name | price |
|---|---|---|---|
| New Product | P006 | Webcam Pro | 459.90 |
| Delisted Product | P005 | Noise-Cancelling Headphones | 899.90 |
Step 5: Complete Sync Pipeline
Combine all steps into a complete synchronization script:
-- Create target table if not exists
CREATE TABLE IF NOT EXISTS products_sync (
id VARCHAR PRIMARY KEY,
name VARCHAR,
price DOUBLE,
specs JSON,
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Execute UPSERT sync
INSERT INTO products_sync (id, name, price, specs)
SELECT
COALESCE(o.id, u.id),
COALESCE(o.name, u.name),
COALESCE(u.price, o.price),
json_merge_patch(
COALESCE(o.specs, '{}'::JSON),
COALESCE(u.specs, '{}'::JSON)
) AS specs
FROM products_original o
FULL OUTER JOIN products_updated u ON o.id = u.id
ON CONFLICT (id) DO UPDATE SET
price = EXCLUDED.price,
specs = EXCLUDED.specs,
last_updated = CURRENT_TIMESTAMP;
-- Verify sync results
SELECT id, name, price, specs FROM products_sync ORDER BY id;
Sync Results:
| id | name | price | specs |
|---|---|---|---|
| P001 | Mechanical Keyboard | 329.90 | {“color”:“black”,“size”:“full-size”,“weight”:820,“rgb”:true} |
| P002 | Wireless Mouse | 149.90 | {“color”:“black”,“dpi”:4000,“weight”:95,“wireless_charging”:true} |
| P003 | Monitor Stand | 179.90 | {“material”:“aluminum”,“max_weight”:20000,“adjustable”:true,“tilt_range”:15} |
| P004 | USB Hub | 79.90 | {“ports”:7,“power_supply”:true,“color”:“black”} |
| P005 | Noise-Cancelling Headphones | 899.90 | {“type”:“over-ear”,“battery_life”:30,“active_noise_cancellation”:true} |
| P006 | Webcam Pro | 459.90 | {“resolution”:“4K”,“fps”:60,“auto_focus”:true} |
Figure: Complete synced product database
Advanced: Merging Nested Arrays
When JSON contains array fields, json_merge_patch replaces the entire array by default. To merge arrays (append instead of replace), use this technique:
-- Merge tag arrays instead of replacing
SELECT
p.id,
p.name,
json_group_array(DISTINCT json_array_element_text(
json_merge_patch(
'{"tags":["electronics","peripheral"]}'::JSON,
'{"tags":["new","2026-model"]}'::JSON
)
)) AS merged_tags
FROM products_original p
WHERE p.id = 'P001';
Output:
| id | name | merged_tags |
|---|---|---|
| P001 | Mechanical Keyboard | [“electronics”, “peripheral”, “new”, “2026-model”] |
Summary
This article demonstrated core JSON data merging and difference detection skills in DuckDB through an e-commerce inventory sync scenario:
| Function/Technique | Purpose |
|---|---|
json_merge_patch(a, b) | Recursively merge two JSON objects |
json_extract(json, '$.field') | Extract single fields for comparison |
IS DISTINCT FROM | NULL-safe comparison |
FULL OUTER JOIN | Detect both new and delisted items |
INSERT ... ON CONFLICT | UPSERT pattern for sync |
These techniques can be directly applied to:
- Configuration management: Merge configs from multiple sources
- API response processing: Merge partially updated resources
- Data versioning: Track JSON document change history
- Real-time reporting: Update aggregates based on incremental data
更多 DuckDB 实战技巧，请关注 DuckDB Lab（duckdblab.org）