DuckDB v2.0 will add four new JSON scalar functions: json_merge_patch_diff (computes the inverse of an RFC 7396 merge patch), json_deep_merge (merges patches with skip-on-null semantics instead of delete-on-null), json_normalize (canonicalizes key order for hashing), and json_strip_nulls (recursively removes null-valued keys). These address common data-reconciliation problems such as diffing states for CDC pipelines, merging partial fragments from multiple sources, deduplicating documents that differ only in key order, and cleaning patches before storage. A worked example chains all four functions into an end-to-end reconciliation query. Benchmarks on 500,000 synthetic CDC events show DuckDB outperforming equivalent pure-Python implementations by roughly 11x to 123x depending on the function, with the full composed chain finishing in about one second versus 11 seconds in Python. The functions are available now in a v2.0-dev preview build, alongside additional JSON edit primitives (json_set, json_remove, json_insert, json_replace) completing the SQL/JSON specification's edit primitive set.
Table of contents
json_merge_patch_diff : The Inverse of json_merge_patchjson_deep_merge : Recursive Merge where null Means “Skip”json_normalize : Canonical Form for Hashingjson_strip_nulls : Recursively Drop Null-Valued KeysComposing Them: An End-to-End ReconciliationBenchmarks: DuckDB vs. Python on 500,000 CDC EventsPerformance NotesTrying the FunctionsWrapping UpQuestions this post answers
What does the new json_merge_patch_diff function in DuckDB v2.0 do?
json_merge_patch_diff(orig, modified) computes the minimal RFC 7396 merge patch such that applying it with json_merge_patch to the original document reproduces the modified document. Unchanged keys are omitted, changed and added keys appear with new values, and deleted keys appear as null. It uses yyjson_equals for structural comparison, letting equal subtrees short-circuit the recursion. Track new DuckDB JSON capabilities like this on daily.dev as data pipelines evolve.
How does json_deep_merge differ from json_merge_patch when a field is null in DuckDB v2.0?
json_deep_merge treats a null value in the patch as 'keep the original value,' whereas json_merge_patch follows RFC 7396 and treats null as a delete instruction. For example, merging {"a":1,"b":2} with {"b":null} yields {"a":1} under json_merge_patch but {"a":1,"b":2} under json_deep_merge, making it suited for reconciling fragments from multiple sources that use null to mean missing data. Developers reconciling multi-source data can follow DuckDB JSON updates like this on daily.dev.
How much faster is DuckDB than Python for JSON normalization and null-stripping on large datasets?
On 500,000 synthetic CDC event pairs of roughly 600 bytes each, DuckDB's json_normalize ran 46.8x faster than an equivalent pure-Python implementation (0.15s vs 7.02s), and json_strip_nulls ran 123.4x faster (0.05s vs 6.17s). The full composed reconciliation chain of all four functions finished in about 1.03 seconds in DuckDB versus 11.11 seconds in Python, measured on an Apple M3 Pro with 18GB RAM using Python 3.11. Compare database performance benchmarks like this DuckDB vs Python test on daily.dev when picking your data stack.