Zh JSON

DuckDB实战：JSON合并与数据对比——json_merge_patch与差异检测

深入讲解DuckDB的json_merge_patch合并策略、JSON字段差异检测、以及结合UNNEST处理嵌套数组的完整实战教程，附带电商库存同步场景。

English

引言

在数据同步、库存管理和配置版本控制等场景中，经常需要合并两个 JSON 数据源并检测差异。传统做法是编写复杂的 ETL 脚本，但 DuckDB 内置的 json_merge_patch、json_remove 和 JSON 类型运算让你可以直接用 SQL 完成这些操作。

本文将通过一个电商库存同步的真实业务场景，演示如何使用 DuckDB 进行 JSON 合并、字段差异检测和增量更新。

场景：电商产品数据同步

假设你维护着两个数据源：

  • 原始产品库 (products_original)：当前线上库存
  • 更新产品库 (products_updated)：采购系统推送的新数据

你需要：

  1. 合并产品规格（保留旧字段 + 新增字段）
  2. 检测价格变动
  3. 找出新增和已下架的商品

环境准备

SELECT version();

DuckDB v1.5.2 完全支持所有 JSON 函数，无需安装额外扩展。

第一步：创建测试数据

-- 原始产品表（线上库存）
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);

-- 更新产品表（采购系统推送）
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;

数据架构

图：电商库存同步流程——原始数据与更新数据的合并对比

第二步：JSON 合并 —— json_merge_patch

json_merge_patch 是 RFC 7396 标准定义的 JSON Merge Patch 算法实现。它会递归合并两个 JSON 对象：

  • 新对象中存在的字段会覆盖旧字段
  • 新对象中不存在的字段保留原值
  • null 值表示删除该字段
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;

运行结果：

idnamemerged_specs
P001Mechanical Keyboard{“color”:“black”,“size”:“full-size”,“weight”:820,“rgb”:true}
P002Wireless Mouse{“color”:“black”,“dpi”:4000,“weight”:95,“wireless_charging”:true}
P003Monitor Stand{“material”:“aluminum”,“max_weight”:20000,“adjustable”:true,“tilt_range”:15}
P004USB Hub{“ports”:7,“power_supply”:true,“color”:“black”}

合并结果

图：json_merge_patch 合并后的产品规格

关键观察：

  • weight 从 800 更新为 820（价格调整）
  • rgb、wireless_charging、tilt_range 等新增字段被正确合并
  • color 从 "white" 更新为 "black"

第三步：字段差异检测

使用 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;

运行结果：

idnameoriginal_pricenew_priceprice_changespecs_statusnew_rgb_feature
P001Mechanical Keyboard299.90329.9030.00specs_changedtrue
P002Wireless Mouse149.90149.900.00specs_changednull
P003Monitor Stand199.90179.90-20.00specs_changednull
P004USB Hub89.9079.90-10.00specs_changednull

关键观察：

  • P001（机械键盘）价格上涨 30 元，且新增 RGB 灯效
  • P002（无线鼠标）价格不变，但配色从白色改为黑色，新增无线充电功能
  • P003-P004 均有价格下调，适合做促销活动

第四步：新增与下架商品检测

使用 EXCEPT 或 NOT IN 快速识别数据变更：

SELECT '新增商品' AS change_type, id, name, price FROM products_updated
WHERE id NOT IN (SELECT id FROM products_original)
UNION ALL
SELECT '下架商品' AS change_type, id, name, price FROM products_original
WHERE id NOT IN (SELECT id FROM products_updated);

运行结果：

change_typeidnameprice
新增商品P006Webcam Pro459.90
下架商品P005Noise-Cancelling Headphones899.90

第五步：完整同步流程

将上述步骤组合为一个完整的同步 SQL 脚本：

-- 创建目标表（如果不存在）
CREATE TABLE IF NOT EXISTS products_sync (
  id VARCHAR PRIMARY KEY,
  name VARCHAR,
  price DOUBLE,
  specs JSON,
  last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 执行 UPSERT 同步
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;

-- 验证同步结果
SELECT id, name, price, specs FROM products_sync ORDER BY id;

同步结果：

idnamepricespecs
P001Mechanical Keyboard329.90{“color”:“black”,“size”:“full-size”,“weight”:820,“rgb”:true}
P002Wireless Mouse149.90{“color”:“black”,“dpi”:4000,“weight”:95,“wireless_charging”:true}
P003Monitor Stand179.90{“material”:“aluminum”,“max_weight”:20000,“adjustable”:true,“tilt_range”:15}
P004USB Hub79.90{“ports”:7,“power_supply”:true,“color”:“black”}
P005Noise-Cancelling Headphones899.90{“type”:“over-ear”,“battery_life”:30,“active_noise_cancellation”:true}
P006Webcam Pro459.90{“resolution”:“4K”,“fps”:60,“auto_focus”:true}

同步结果

图：完整同步后的产品数据库

进阶：嵌套数组的合并策略

当 JSON 中包含数组字段时，json_merge_patch 默认会用新数组完全替换旧数组。如果需要合并数组（追加而非替换），可以使用以下技巧：

-- 假设产品有标签数组，需要合并而非替换
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';

输出：

idnamemerged_tags
P001Mechanical Keyboard[“electronics”, “peripheral”, “new”, “2026-model”]

总结

本文通过电商库存同步场景，演示了 DuckDB 中 JSON 数据合并与差异检测的核心技能：

函数/技术用途
json_merge_patch(a, b)递归合并两个 JSON 对象
json_extract(json, '$.field')提取单个字段进行比较
IS DISTINCT FROM安全的 NULL 感知比较
FULL OUTER JOIN同时检测新增和下架
INSERT ... ON CONFLICTUPSERT 模式执行同步

这些技巧可以直接应用于：

  • 配置中心：合并多个来源的配置 JSON
  • API 响应处理：合并部分更新的资源
  • 数据版本控制：追踪 JSON 文档的变更历史
  • 实时报表：基于增量数据更新汇总结果

更多 DuckDB 实战技巧，请关注 DuckDB Lab（duckdblab.org）

🔧 相关教程推荐

DuckDB JSON 补丁全家桶：一行 SQL 搞定数据合并与去重 DuckDB v2.0 推出四个 JSON 补丁函数：json_deep_merge、json_strip_nulls、json_normalize、json_merge_patch。从 API 数据 …
DuckDB MERGE UPSERT + 增量更新实战：每日报表从 2 小时到 30 秒 掌握 DuckDB MERGE UPSERT 实现增量数据更新，让每日报表生成从 2 小时压缩到 30 秒内，附完整 SQL 代码和变现建议。
DuckDB MERGE UPSERT + 增量更新——让每日报表从 2 小时变成 30 秒 用 DuckDB 的 MERGE INTO 语句实现增量更新报表系统，每天只处理新增数据，将日报生成时间从 2 小时压缩到 30 秒内，附完整 Python + SQL 代码和变现路径。
实战：用 DuckDB 构建跨境电商选品分析器（附完整代码）

🦆 DuckDB 完全指南 · 📖 浏览全部教程 · 📘 DuckDB 速查表

← 返回文章列表
上一篇 DuckDB 被 AWS 收购：中国开发者的机遇与行动指南

📺 Watch video tutorials → Olap Studio YouTube

Subscribe for more DuckDB & AI automation tutorials

© 2026 Olap 工作室 - DuckDB 实战教程与数据分析指南
使用 Hugo 构建
主题 Stack 由 Jimmy 设计

⚠️ 本站为独立社区项目，与 DuckDB 基金会及 DuckDB 官方项目无任何从属、背书或赞助关系。

"DuckDB" 是 DuckDB 基金会的注册商标，本站仅以事实描述方式使用该名称。

本站内容仅供教育与社区推广用途，不构成任何商业服务。