Four JSON functions that move work back into SQL
DuckDB v2.0 adds RFC 7396 patching, canonical ordering and null stripping — with a measured 123× on one of them against Python.
2 minBig Data & Vector DBs
Reconciling JSON from several sources has been awkward inside SQL: key orders differ, a null may mean deletion or absence, minimal diffs need computing, and partial updates need applying. The usual answer has been to leave the database and do it in Python.
DuckDB v2.0 adds four functions to close that gap. `json_merge_patch_diff` computes a minimal RFC 7396 patch between two documents. `json_deep_merge` applies a patch treating null as skip rather than delete. `json_normalize` sorts keys recursively for a canonical form, which makes deduplication possible. `json_strip_nulls` removes null-valued keys recursively.
The measurements
Over 500,000 change-data-capture events, against Python: `json_strip_nulls` 123.4×, `json_normalize` 46.8×, `json_merge_patch_diff` 35.1×, `json_deep_merge` 18.0×. The full chain came out at 10.8×.
The chain figure is the honest one to quote. Individual functions look spectacular because each replaces a tight Python loop; end to end, the gain settles at roughly an order of magnitude, and that is what a pipeline would actually see.
The null distinction is the detail that will save someone an incident. Treating null as delete versus skip is the difference between a patch that clears a field and one that leaves it alone, and the two functions here make the choice explicit rather than implicit in whichever library was imported.
Available in the v2.0-dev preview; the release itself is due in autumn.
Retold from DuckDB. This is a summary in our own words; follow the link for the original reporting.