Skip to content
ClickHouse Docs
49.6kGet Started

mergedJSONPatch

View as MarkdownEdit this pageReport an issueSet up docs MCP

Autogenerated from ClickHouse system tables

Introduced in: v26.8.0

Aggregates JSON values by merging them with last-write-wins semantics, implementing the core merge behavior of RFC 7396 JSON Merge Patch at the path level.

The aggregate function stores state as triplets (key, value, sorting_key) where each key (JSON path) only keeps the latest effective record according to the sorting_key. Object writes are flattened into descendant paths for paths that the JSON type stores as dynamic scalar leaves. Ancestor non-object writes shadow conflicting descendants.

Every explicitly typed path (declared with JSON(a Map(...)), JSON(a Tuple(...)), JSON(a Variant(...)), JSON(a Dynamic), etc.) is treated as an atomic value: the whole path value is replaced by the newer patch rather than deep-merged. Only untyped (dynamic) scalar paths follow RFC 7396 deep-merge semantics.

The sort_key determines which value wins for each JSON path. The row with the largest sort_key is retained. If two conflicting patches have equal sort keys, the result is order-dependent: the patch processed later wins the tie. Users should not rely on ORDER BY to break ties deterministically.

LIMITATIONS (inherited from ColumnObject):

  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.

Syntax

mergedJSONPatch(json, sort_key)

Arguments

  • 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.

Returned value

Returns a single JSON object that is the result of merging all input JSON objects. JSON

Examples

Basic usage with sort key

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}             │
└─────────────────────────────────┘
Navigation