DuckDB v2.0将新增四个JSON函数，解决数据管道对账难题

客座博客文章由Mustafa Khan (Atlan) 撰写。

DuckDB 的 JSON 扩展已经涵盖了读取 JSON、提取路径、应用 RFC 7396 合并补丁以及常用的标量访问器。即将发布的 v2.0 版本将新增四个标量函数：json_merge_patch_diff 计算 RFC 7396 合并补丁的逆操作，json_deep_merge 应用补丁时采用“跳过”null 语义，json_normalize 对键顺序进行规范化，json_strip_nulls 递归删除值为null 的键。您现在就可以通过安装DuckDB v2.0-dev 预览版 来试用这些原语。

这些原语解决了数据管道中在系统之间协调状态时反复出现的若干问题。本文中的示例来自Atlan。Atlan 是 AI 的上下文层：一个从公司数据系统收集元数据、血缘和语义的平台，以便数据团队和 AI 代理能够找到并理解数据。保持该层最新意味着不断协调来自多个上游源的实体文档。两个上游服务可能以不同的键顺序描述同一实体。部分更新可能在字段中携带null 以表示删除或数据缺失。大多数事件只改变数十个字段中的一两个。在 SQL 中处理这些情况以前需要离开数据库。这四个新函数允许您在单个查询中完成工作。

在本文的其余部分，我们将逐个介绍每个函数，然后将它们串联成一个端到端的对账示例。最后，我们展示一个简单基准测试的结果，将这四个函数与等效的 Python 实现在 500,000 个合成变更数据捕获 (CDC) 事件上进行比较。

json_merge_patch_diff：json_merge_patch

的逆操作json_merge_patch_diff(orig, modified)json_merge_patch(orig, patch) = modified。json_merge_patch 已存在于 DuckDB 中。它将 RFC 7396 补丁应用于文档：补丁中的null 值删除键，嵌套对象递归合并，任何其他值覆盖原始值。被删除的键在补丁中显示为null，更改和添加的键显示其新值，而未更改的键则完全省略。

SELECTjson_merge_patch_diff('{"a":1,"b":2,"c":3}','{"a":1,"b":99,"d":4}');

{"c":null,"b":99,"d":4}

a 未更改，因此被省略。b 已更改。c 被移除，因此变为null。d 是新增的。

该函数递归进入嵌套对象，生成仅触及已更改路径的补丁：

SELECTjson_merge_patch_diff('{"user":{"name":"Alice","age":30}}','{"user":{"name":"Alice","age":31}}');

{"user":{"age":31}}

与json_merge_patch 的往返通过构造保持：

SELECTjson_merge_patch('{"a":1,"b":2,"c":3}',json_merge_patch_diff('{"a":1,"b":2,"c":3}','{"a":1,"b":99,"d":4}'))='{"a":1,"b":99,"d":4}'ASround_trips;

true

子树之间的相等性使用yyjson_equals 计算，它比较两个yyjson 值，在结构上比较而不序列化任何一侧。比较相等的子树对输出没有贡献，并短路递归：

SELECTjson_merge_patch_diff('{"a":{"b":{"c":{"d":1}}}}','{"a":{"b":{"c":{"d":1}}}}');

{}

这个函数对于 CDC 管道特别有用，其中元数据目录上的大多数事件只触及数十个字段中的一两个。将json_merge_patch_diff(prev_state, new_state) 的输出发送到下游，而不是完整的新状态，将变更负载削减到之前大小的一小部分。diff 本身就是变更，因此消费者端无需额外过滤，并且另一端的json_merge_patch 从prev_state 和补丁重建新状态。

json_deep_merge：递归合并，其中null 表示“跳过”

上一节的补丁使用json_merge_patch 应用，它严格遵循 RFC 7396：补丁中的null 删除键。这是与json_merge_patch_diff 往返的正确规则，但当你协调来自多个上游的片段时，这些片段发出null 表示“我没有此消息中此字段的值”，这就是错误的规则。json_deep_merge 涵盖了这种情况。

json_deep_merge 遵循与json_merge_patch 相同的递归合并结构，但有一个区别：补丁中的null 值表示“保留原始值”而不是“删除键”。其他一切，包括非 null 值的处理、嵌套对象以及可变参数形状，都与json_merge_patch 匹配。在以下示例中，我们应用一个补丁，其中键b 设置为null，分别使用json_merge_patch 和json_deep_merge，导致两个不同的结果：

SELECTjson_merge_patch('{"a":1,"b":2}','{"b":null}')ASrfc_7396;

{"a":1}

SELECTjson_deep_merge('{"a":1,"b":2}','{"b":null}')ASdeep_merge;

{"a":1,"b":2}

当补丁包含非 null 值时，两个函数都用补丁值替换原始值，并且当原始值和补丁都是对象时，两者都递归进入嵌套对象。null 的处理是唯一的区别：

SELECTjson_deep_merge('{"a":{"x":1,"y":2}}','{"a":{"y":null,"z":3}}');

{"a":{"x":1,"y":2,"z":3}}

嵌套的y 被保留，因为补丁包含null。z 被添加，因为补丁有一个真实值。相同的补丁通过json_merge_patch 会删除y：

SELECTjson_merge_patch('{"a":{"x":1,"y":2}}','{"a":{"y":null,"z":3}}');

{"a":{"x":1,"z":3}}

该函数是可变参数的：传入任意数量的补丁，它们从左到右应用。

SELECTjson_deep_merge('{"a":1}','{"a":null}','{"a":2}');

{"a":2}

第一个补丁跳过（保留a=1），第二个覆盖（a 变为2）。

motivating skip-on-null 规则的用例是协调来自多个来源的片段。一个来源知道列名（columnName）但不知道其父表（parentColumn）。另一个知道父表但不知道列名。每个都发出一个片段，未知字段为null：

SELECTjson_deep_merge('{"columnName":"user_id","parentColumn":null}','{"columnName":null,"parentColumn":"accounts.id"}');

{"columnName":"user_id","parentColumn":"accounts.id"}

注意，将 SQLNULL 作为参数和 JSONnull 值会导致不同行为。SQLNULL 补丁使结果为NULL，SQLNULL 原始值被忽略，与json_merge_patch 的行为匹配。JSONnull 在补丁中表示“保留此键的原始值”。

json_normalize：用于哈希的规范形式

第三个原语回答了本文开头提出的第一个问题：当两个服务发出具有不同键顺序的相同 JSON 对象时，它们是否是相同的文档？词法上它们不是，这意味着比较原始字符串的内容哈希将得出它们不同的结论。json_normalize 通过递归排序每个对象的键来修复这个问题，包括嵌套在数组中的对象。数组元素保持其顺序，因为元素顺序在 JSON 中有意义，而键顺序没有。标量值原样返回。

SELECTjson_normalize('{"z":1,"a":2,"m":3}');

{"a":2,"m":3,"z":1}

递归下降到嵌套对象中，以及下降到数组中的嵌套对象中：

SELECTjson_normalize('{"c":{"b":{"z":1,"a":2},"a":3},"a":4}');

{"a":4,"c":{"a":3,"b":{"a":2,"z":1}}}

SELECTjson_normalize('[{"z":1,"a":2},{"y":3,"b":4}]');

[{"a":2,"z":1},{"b":4,"y":3}]

一旦两个语义上等价的文档规范化为相同的字节，对它们进行哈希就可以轻易地消除重复：

SELECTmd5(json_normalize('{"z":1,"a":2}'))=md5(json_normalize('{"a":2,"z":1}'))ASsame_hash;

true

A GROUP BY md5(json_normalize(payload)) 将重复的发射折叠为单一行，对仅因键顺序不同而不同的实体进行去重。这对于元数据目录很有用，因为同一个实体可能由两个不同的上游源描述，键顺序略有不同。

内部上，该函数遍历可变的文档，使用 std::sort 对每个对象的键值对进行排序，并按顺序重建对象。

json_strip_nulls：递归删除空值键

json_strip_nulls 是一个清理辅助函数。它递归地删除每个值为 JSON null 的键。数组元素不会被修改（因为 null 是合法的数组元素），标量值原样返回。

SELECTjson_strip_nulls('{"a":1,"b":null,"c":null,"d":2}');

{"a":1,"d":2}

它会深入到嵌套对象中：

SELECTjson_strip_nulls('{"a":{"x":1,"y":null},"b":2}');

{"a":{"x":1},"b":2}

并进入数组内部的对象，同时保持数组结构不变：

SELECTjson_strip_nulls('{"a":[{"x":1,"y":null},{"z":null}]}');

{"a":[{"x":1},{}]}

其动机场景是在存储之前清理补丁。计算 json_merge_patch_diff 后，补丁中包含被删除键的 null 条目，这在 RFC 7396 下是正确的。如果你只想发送新增和更新，json_strip_nulls(json_merge_patch_diff(orig, modified)) 会从补丁中移除这些 null 条目。生成的补丁可以添加和更新字段，但绝不能删除。同样的想法适用于在哈希或存储之前修剪冗长的 API 响应。

引擎盖下，这使用了 yyjson_mut_obj_iter_remove，它会在迭代期间删除键而不使迭代器失效。在一个已经干净的文档上调用 json_strip_nulls 会简化为一次解析、一次遍历和一次序列化。

组合应用：端到端的对账

这四个函数组合成一个小的对账工作流。假设上游系统在每次更改时发出部分 JSON 文档。某些字段存在且具有新值，某些字段缺席因为上游未包含它们，还有一些字段明确为 null 因为上游在本次事件中没有数据。目标是清理事件，针对先前状态计算最小补丁，发送该补丁，并存储结果文档的规范化哈希，以便相同实体在去重时折叠。

让我们通过一个具体示例来看这个工作流：

CREATETABLEcatalog(idINTEGER,stateJSON);INSERTINTOcatalogVALUES(1,'{"typeName":"Column","name":"user_id","description":"primary key","dataType":"BIGINT","ownerEmail":"[email protected]"}');CREATETABLEincoming_events(idINTEGER,eventJSON);INSERTINTOincoming_eventsVALUES(1,'{"name":"user_id","description":"primary key for the users table","dataType":"BIGINT","ownerEmail":null,"team":null}');

步骤 1：从传入事件中剥离 null 占位符，以免它们被视为删除：

SELECTjson_strip_nulls(event)ASclean_eventFROMincoming_eventsWHEREid=1;

{"name":"user_id","description":"primary key for the users table","dataType":"BIGINT"}

步骤 2：针对当前状态生成最小补丁。将清理后的事件深度合并到原始文档上（因此缺失的字段保留其先前的值），然后与原始文档进行差异比较：

SELECTjson_merge_patch_diff(c.state,json_deep_merge(c.state,json_strip_nulls(e.event)))ASpatchFROMcatalogcJOINincoming_eventseUSING(id);

{"description":"primary key for the users table"}

只有更改的字段出现。dataType 未更改，ownerEmail 和 team 是 null 并被剥离，因此两者都不会返回。补丁就是你要往下游发送的内容。

步骤 3：应用补丁并哈希规范化结果：

SELECTmd5(json_normalize(json_merge_patch(c.state,'{"description":"primary key for the users table"}')))AScontent_hashFROMcatalogcWHEREid=1;

21ceaced24d8958d71d537f6fcebe8a2

每个函数执行单一转换，整个链在单个查询中运行。

基准测试：DuckDB 与 Python 在 500,000 个 CDC 事件上的对比

下面的基准测试将每个函数和完整的组合链与等价的 Python 实现进行比较，处理 500,000 个合成目录事件对（每个文档约 600 字节）。Python 实现使用 json.loads 解析，用纯 Python 应用等价的转换（Python 输出在 100 个示例文档上与 DuckDB 完全匹配），并使用 json.dumps 序列化。这是与 DuckDB 在 JSON 列上端到端所做的最接近的同类比较。

基准测试方法如下。每个测量是五次迭代的中位数。Python 计时包括每行的解析、转换和序列化，数据预加载到内存中以排除磁盘 I/O。对于 DuckDB，我们从命令行计时整个运行，并减去一个无操作 SELECT json 运行，因此只测量转换。硬件是 Apple M3 Pro（arm64），18 GB RAM，Python 3.11。DuckDB 默认多线程运行，而 Python 脚本是单线程。基准测试代码和合成数据生成器可在 GitHub 上获取。

加速来自两个来源：DuckDB 对 yyjson 树进行原地操作，而 Python 等价实现大部分周期花费在 json.loads 和 json.dumps 上。结果是大约 1-2 个数量级的加速，取决于函数执行了多少转换工作。json_strip_nulls 是极端情况，因为解析后的工作简化为带有原地删除的单次迭代遍历。在组合链上，每函数差距缩小，因为两侧的绝对工作量都增加，但 DuckDB 仍在大约一秒内完成 500,000 个事件对的完整对账。

性能说明

有几个值得指出的特点：

• 所有四个函数直接操作 yyjson 可变值。
• json_merge_patch_diff 使用 yyjson_equals 进行深度相等比较，且比较相等的子树会停止递归。
• json_normalize 的排序是每个对象级别的，而非全局。成本随每个对象的大小而非文档大小扩展。
• 所有四个都是普通的标量函数，一次处理一行向量，因此适合 DuckDB 的向量化执行模型。

试用这些函数

这四个函数将在 DuckDB v2.0 中提供。要立即试用，请安装 v2.0-dev 预览版，然后加载 JSON 扩展：

LOAD json;SELECTjson_normalize('{"z":1,"a":2}');

总结

这四个函数扩展了 DuckDB JSON 扩展，提供了用于差异、合并、规范化和剥离 null 值的原语。它们组合成对账管道，以前需要 SQL 之外的转换。

这还不止。函数 json_set(json, path, value)、json_remove、json_insert 和 json_replace 也可在 DuckDB v2.0 预览版中使用（#23786），完成了 SQL/JSON 规范中定义的编辑原语集。对于实现，请参阅 extension/json 中的 JSON 扩展源码。

欢迎在 Discord 或 GitHub 上提供反馈和错误报告。