mergedJSONPatch

​

mergedJSONPatch

1. Null deletion: a patch {"key": null} does not remove the key. ColumnObject drops null-valued members on insertion, so the function cannot distinguish “key absent” from “key is null”.
2. Empty-object replacement: a patch {"a": {}} cannot displace an older scalar or array at path a. ColumnObject silently drops paths whose value is an empty object {}, so the newer patch contributes nothing and the old value survives.
3. Non-Nullable typed-path absence: when a JSON column declares a typed path with a non-nullable type (e.g., JSON(a UInt32)), a row that omits a is stored with the type default value (e.g., 0). The aggregate cannot tell “absent” from “explicitly written as the default”, so a newer patch that omits a silently erases an older non-zero value. To avoid this, declare typed paths as Nullable (e.g., JSON(a Nullable(UInt32))). A null in a nullable typed path is treated as “path absent” and is correctly skipped.
4. All typed paths are atomic: every typed path (Map(K,V), JSON, Dynamic, Tuple(…), Variant(…), Array(…), or any other declared type) is stored as a single value. The aggregate replaces the entire value atomically rather than deep-merging its contents. Only dynamic (untyped, scalar) paths are deep-merged path-by-path.
5. Dot-in-key ambiguity: the JSON type represents {"a":{"b":1}} and {"a.b":1} with the same internal path a.b. A single row can therefore expose both a and a.b as independent peers. When a newer patch writes only a, the ancestor/descendant conflict rule erases a.b; when it writes only a.b, the same rule erases a. To avoid this, set json_type_escape_dots_in_keys = 1. With this setting, literal dots in JSON keys are percent-encoded (e.g. a.b becomes a%2Eb), making them distinct from nested paths and eliminating the false conflict.

mergedJSONPatch(json, sort_key)

• json — JSON column to aggregate. JSON
• sort_key — Comparable column that determines which write wins for each path. The row with the largest sort_key value is retained.

SELECT mergedJSONPatch(json, sort_key) FROM
(
    SELECT '{"a":1}'::JSON AS json, 1 AS sort_key
    UNION ALL
    SELECT '{"b":2}'::JSON, 2
    UNION ALL
    SELECT '{"a":3, "c":4}'::JSON, 3
);

┌─mergedJSONPatch(json, sort_key)─┐
│ {"a":3,"b":2,"c":4}             │
└─────────────────────────────────┘