> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-vortex-format.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Aggregates JSON values using last-write-wins JSON Merge Patch semantics.

# mergedJSONPatch

<h2 id="mergedJSONPatch">
  mergedJSONPatch
</h2>

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**

```sql theme={null}
mergedJSONPatch(json, sort_key)
```

**Arguments**

* `json` — JSON column to aggregate. [`JSON`](/reference/data-types/newjson)
* `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`](/reference/data-types/newjson)

**Examples**

**Basic usage with sort key**

```sql title=Query theme={null}
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
);
```

```response title=Response theme={null}
┌─mergedJSONPatch(json, sort_key)─┐
│ {"a":3,"b":2,"c":4}             │
└─────────────────────────────────┘
```