DATABASES Signal 355
Reconciling JSON in DuckDB, One Patch at a Time
↗
DuckDB v2.0 will ship four new JSON scalar functions, json_merge_patch_diff, json_deep_merge, json_normalize, and json_strip_nulls, that handle common reconciliation tasks like computing diffs, merging with skip-on-null semantics, canonicalizing key order, and removing null keys within SQL queries.
These functions let data engineers perform JSON reconciliation work that previously required leaving the database, keeping the logic in a single SQL query. They address recurring pipeline problems like computing minimal CDC payloads, handling null semantics that differ between upstream sources, and normalizing key order for comparison.
Written by elseif from the cluster below · every claim links back to a sourceThe three things worth knowing
json_merge_patch_diff computes the minimal RFC 7396 patch between two documents, enabling compact CDC payloads that only carry changed fields.
json_deep_merge applies patches where null means 'keep original' rather than 'delete,' covering reconciliation of fragments from multiple upstreams that emit null to indicate absence of data.
json_normalize and json_strip_nulls canonicalize key order and remove null-valued keys respectively, making structural equality comparisons reliable across sources with different conventions.
THE CLUSTER