> ## Documentation Index
> Fetch the complete documentation index at: https://docs.startree.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Merge JSON fields during partial upsert

> Retain and update JSON attributes during realtime ingestion with MERGE_JSON, including append and override modes, configuration examples, and operational guidance.

<Note>
  `MERGE_JSON` will be available starting with the **StarTree Cloud 0.17.0 release**.
  Contact StarTree support to confirm availability in your environment before enabling it. The configuration on
  this page requires StarTree's implementation and does not apply to an unmodified Apache Pinot deployment.
</Note>

`MERGE_JSON` combines an incoming JSON object with the stored object for the same primary key during ingestion.
Use it when events update individual attributes and should retain attributes omitted from later events.
Queries and JSON indexes see the resulting merged value.

Configure `MERGE_JSON` per column in a realtime partial-upsert table and choose one object-merge mode for the table:

| Mode               | Behavior                                                                                         |
| ------------------ | ------------------------------------------------------------------------------------------------ |
| `append` (default) | Merge nested objects recursively and retain missing keys at every level.                         |
| `override`         | Retain missing top-level keys, but replace each supplied value, including entire nested objects. |

Both modes **replace arrays**; `append` does not concatenate them. This is an ingestion configuration, not a SQL
function. Use the same `MERGE_JSON` function name for both modes.

## Example: update a customer profile

Suppose the stored `attributes` column contains:

```json theme={null}
{
  "account": "business",
  "settings": {"theme": "dark", "language": "en"},
  "tags": ["trial"],
  "contact": "old@example.com"
}
```

A later event for the same primary key supplies:

```json theme={null}
{
  "settings": {"language": "fr"},
  "tags": ["paid", "active"],
  "contact": null
}
```

With default `append`, the resulting column is:

```json theme={null}
{
  "account": "business",
  "settings": {"theme": "dark", "language": "fr"},
  "tags": ["paid", "active"],
  "contact": null
}
```

With `override`, the resulting column is:

```json theme={null}
{
  "account": "business",
  "settings": {"language": "fr"},
  "tags": ["paid", "active"],
  "contact": null
}
```

In both cases, `account` survives, `tags` is replaced, and `contact` remains a key with a null value.
Only `append` retains `settings.theme`.

## Configure a table

### Prerequisites

* Confirm with StarTree support that the feature is available on every server that can host the table.
* Start with a correctly configured [realtime partial-upsert table](/recipes/upserts-partial): primary keys, stream
  records partitioned by primary key, a comparison column, null handling, and appropriate routing and assignment.
  The custom-merger settings below replace the recipe's `partialUpsertStrategies` map.
* Include the primary key and comparison value in every update. Choose an ordering value that represents the
  intended update order for each key.
* Use a single-value `JSON` column for variable-shape JSON objects. Serialized JSON-object `STRING` columns and
  native `MAP` values are also supported. MAP keys and values must remain compatible with the column's complex schema.

This feature uses realtime partial-upsert semantics. It does not add partial-upsert support to
[OFFLINE tables](/corecapabilities/ingestdata/adv-concepts/batch/offline-upserts). Keep the existing
[off-heap upsert](/corecapabilities/manage-data/offheap-upsert) metadata and recovery settings for your table.

<Warning>
  Existing partial-upsert restrictions still apply. Do not enable `SegmentRefreshTask` on this table. Coordinate
  maintenance, rebalancing, and recovery with StarTree support to preserve per-key update order.
</Warning>

### Schema example

This schema defines the columns used below. `attributes` contains the object to merge; `score` demonstrates a
built-in numeric merge function on the same row.

```json theme={null}
{
  "schemaName": "profile_events",
  "primaryKeyColumns": ["customerId"],
  "dimensionFieldSpecs": [
    {"name": "customerId", "dataType": "STRING"},
    {"name": "attributes", "dataType": "JSON"},
    {"name": "name", "dataType": "STRING"}
  ],
  "metricFieldSpecs": [
    {"name": "score", "dataType": "LONG"}
  ],
  "dateTimeFieldSpecs": [
    {"name": "eventTime", "dataType": "LONG", "format": "1:MILLISECONDS:EPOCH", "granularity": "1:MILLISECONDS"}
  ]
}
```

### Table configuration

Merge these sections into your existing `profile_events` realtime table config. This is a **fragment**, not a
complete table definition: retain the stream, time-column, retention, tenant, routing, and partition-assignment
settings. Preserve existing `metadataManagerClass` and other `metadataManagerConfigs` entries.

```json theme={null}
{
  "tableIndexConfig": {
    "nullHandlingEnabled": true
  },
  "upsertConfig": {
    "mode": "PARTIAL",
    "comparisonColumns": ["eventTime"],
    "partialUpsertMergerClass": "ai.startree.pinot.upsert.merger.ConfigurablePartialUpsertMerger",
    "defaultPartialUpsertStrategy": "OVERWRITE",
    "metadataManagerConfigs": {
      "startree.merger.columns.attributes": "MERGE_JSON",
      "startree.merger.columns.score": "INCREMENT"
    }
  }
}
```

This configuration recursively merges `attributes`, adds each incoming `score` to the previous score, and uses
`OVERWRITE` for other non-key columns such as `name`. Primary-key and comparison columns are never merged.
With no previous value to merge, the incoming value becomes the initial value.

The example enables table-level null handling. Schema-level `enableColumnBasedNullHandling` is also supported;
when using it, keep the fields that can be omitted or null nullable rather than marking them `notNull`.

Do not also configure `partialUpsertStrategies`: it is mutually exclusive with `partialUpsertMergerClass`.
When migrating an existing table, move its column strategies into `startree.merger.columns.<column>` entries.

To select `override`, add this entry to `upsertConfig.metadataManagerConfigs`:

```json theme={null}
{
  "startree.merger.json.mode": "override"
}
```

The mode applies to **every `MERGE_JSON` column in the table**. It cannot be selected separately per column.
It does not change built-in strategies. Built-in `OVERWRITE` replaces an entire non-null column value;
JSON `override` still preserves absent top-level keys inside the column.

### Configuration reference

| Setting                                                   | Default            | Meaning                                                                                                                            |
| --------------------------------------------------------- | ------------------ | ---------------------------------------------------------------------------------------------------------------------------------- |
| `partialUpsertMergerClass`                                | Not enabled        | Set to the fully qualified class name above.                                                                                       |
| `metadataManagerConfigs.startree.merger.columns.<column>` | No custom function | `MERGE_JSON` or a Pinot built-in strategy, such as `INCREMENT`, `OVERWRITE`, or `FORCE_OVERWRITE`. At least one entry is required. |
| `metadataManagerConfigs.startree.merger.json.mode`        | `append`           | `append` or `override`; applies to all JSON merge columns.                                                                         |
| `defaultPartialUpsertStrategy`                            | `OVERWRITE`        | Built-in strategy for non-key columns without a custom function.                                                                   |

Function names and modes ignore case and surrounding whitespace. Column names are trimmed; duplicate names
after trimming are rejected. Null, blank, or unknown functions/modes, primary-key or
comparison-column targets, and an empty function map fail merger construction.

<Warning>
  Merger construction does not validate column existence or type compatibility against the schema. A nonexistent
  column or an incompatible data type can therefore pass table creation. Check the configured column names and types
  before enabling the feature.
</Warning>

## Merge rules and invalid input

| Input at a supplied key                       | `append`                         | `override`                        |
| --------------------------------------------- | -------------------------------- | --------------------------------- |
| Object replacing an object                    | Recursively merge.               | Replace with the incoming object. |
| Array, scalar, or a change of value type      | Replace with the incoming value. | Replace with the incoming value.  |
| Explicit JSON null                            | Set the key to null.             | Set the key to null.              |
| Key absent from the incoming top-level object | Keep the previous value.         | Keep the previous value.          |
| Empty incoming top-level object `{}`          | Keep all previous keys.          | Keep all previous keys.           |

An incoming empty nested object such as `{"settings":{}}` retains the previous nested keys in `append` mode,
and clears that nested object's contents in `override` mode. Neither mode provides a key-deletion operator;
omitted top-level keys survive. Use whole-column replacement if that is the intended operation.

**SQL null and JSON null differ.** An absent or SQL-null incoming `attributes` column preserves its previous value
under `MERGE_JSON`. A JSON object such as `{"contact":null}` sets a key to null. The string `"null"` as the entire
serialized document is a non-object input. A built-in `FORCE_OVERWRITE` column can accept SQL null to clear the
whole column; that column then follows replacement semantics instead of JSON merging.

Object merging requires both serialized strings to parse as single JSON objects. Otherwise, the merger uses
the fallbacks below:

| Previous column value            | Incoming column value                                                                   | Result                                                         |
| -------------------------------- | --------------------------------------------------------------------------------------- | -------------------------------------------------------------- |
| Valid object                     | Malformed JSON, trailing garbage, concatenated documents, array, scalar, or JSON `null` | Keep the previous object.                                      |
| Malformed JSON or any non-object | Any incoming string                                                                     | Keep the incoming string verbatim, even if it is also invalid. |
| Valid object                     | Valid object                                                                            | Merge using the configured mode.                               |

For example, `{"a":1}` plus `[2]` keeps `{"a":1}`, while `42` plus `{"a":2}` becomes `{"a":2}`.
These are merge-stage fallbacks, not record-validation guarantees: decoding, transforms, and schema conversion
can reject or normalize data before it reaches the merger. A first record has no previous object to preserve.

Two native MAP values merge as maps. A mixed representation such as MAP plus string, or unsupported runtime
values such as numbers, keeps the incoming value. Use a consistent representation for each column.
The merger preserves numeric precision when parsing JSON strings; it does not preserve original whitespace or
key order, or restore precision already lost during decoding or conversion.

## Ordering, recovery, and operations

`MERGE_JSON` follows Pinot's existing partial-upsert ordering and previous-row selection. An older event excluded
by that ordering cannot contribute missing keys later. Preserve per-key ordering upstream; merge operations are
not generally commutative. Equal comparison values are not a deduplication mechanism: an accepted replay can
reapply functions such as `INCREMENT`.

Deletion, metadata expiry, retention, and reinsertion keep their existing Pinot behavior. Updates after deletion
start from the incoming fields rather than inheriting the deleted object's attributes, subject to normal record
ordering. Once previous-key metadata expires, the next record also starts fresh. TTL applies to Pinot's record
and metadata lifecycle, not to individual keys inside a JSON object. The plugin does not implement key deletion,
scripted updates, or the complete Elasticsearch Update API.

For rollout and configuration changes:

1. Confirm availability with StarTree support and coordinate any required deployment update.
2. For a new table, apply the configuration and verify construction succeeds. For an existing table, pause table
   consumption before changing merger settings, then coordinate the configuration update and server restart with
   StarTree support. Resume consumption only after all replicas have recreated their partition mergers with the
   same settings. A rolling mode change
   during consumption can produce different accumulated documents across replicas; a segment reload alone does
   not recreate the partition merger.
3. Ingest two ordered updates for a test key and query the stored result. Check a retained key and a replaced key;
   if using a JSON index, also check that a predicate on the old value no longer matches.
4. Coordinate rollback with StarTree support. Before using a deployment without this feature, remove
   `partialUpsertMergerClass` and its merger-specific metadata settings, and configure the intended built-in upsert
   behavior. Already merged data is not undone.

For the schema above, query the test key after its updates have been consumed:

```sql theme={null}
SELECT customerId, attributes, score
FROM profile_events
WHERE customerId = 'customer-1';
```

The `attributes` result should match the chosen mode in the customer-profile example. If the table has a
[JSON index](/corecapabilities/manage-data/indexes/json-index), test its predicates against the merged document too.

Settings are captured at construction. A mode change is not retroactive, and hot configuration migration is not
provided. Each partition owns its merger and failure state.

Malformed JSON and unsupported types produce rate-limited fallback warnings. A built-in function that throws
keeps the incoming value; after 100 consecutive exceptions for a column, that column switches to `OVERWRITE`
until its merger is recreated. A successful merge resets that exception streak but not the per-column warning
timer; a later failure can still have its warning suppressed within the 10-second interval. The documented JSON
fallbacks return normally and do not trigger this exception latch. Warnings are not a per-record error counter,
and this plugin does not export a dedicated degraded-merge metric.

Keys accumulate over time. For serialized JSON values, a successful object merge parses both strings and serializes
the result. Native MAP values instead incur map allocation and copying, including recursive merging in `append`
mode. Check representative document sizes, nesting, update rates, and ingestion lag before production adoption.

## Troubleshooting

| Symptom                                              | Check                                                                                                                           |
| ---------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------- |
| Server cannot load `ConfigurablePartialUpsertMerger` | Verify the class name and confirm feature availability with StarTree support.                                                   |
| Table creation rejects the custom merger             | Remove conflicting `partialUpsertStrategies` and check normal partial-upsert prerequisites.                                     |
| Partition initialization rejects configuration       | Check function/mode spelling, null values, empty/duplicate column names, and protected-column targets.                          |
| A configured column is not merged                    | Match its schema name and runtime type; inspect the missing-column warning emitted on the first merge.                          |
| A previous JSON document remains unchanged           | Check whether the update was out of order, SQL null, malformed, or a top-level non-object.                                      |
| Nested keys disappear                                | Check whether the table uses `override`, or the incoming value changes type.                                                    |
| A column starts replacing values                     | Inspect warnings for repeated built-in merge failures and the 100-exception latch; fix the cause before coordinating a restart. |

## Related documentation

* [Off-heap upserts](/corecapabilities/manage-data/offheap-upsert): metadata storage, snapshot, preload, and TTL settings.
* [Partial upserts](/recipes/upserts-partial): the base partial-upsert ingestion model and built-in strategies.
* [JSON index](/corecapabilities/manage-data/indexes/json-index): filter the merged JSON document efficiently.