Merge JSON fields during partial upsert

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.

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:

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

A later event for the same primary key supplies:

{
  "settings": {"language": "fr"},
  "tags": ["paid", "active"],
  "contact": null
}

With default append, the resulting column is:

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

With override, the resulting column is:

{
  "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: 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. Keep the existing off-heap upsert metadata and recovery settings for your table.

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.

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.

{
  "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.

{
  "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:

{
  "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.

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.

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:

Confirm availability with StarTree support and coordinate any required deployment update.
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.
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.
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:

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

Off-heap upserts: metadata storage, snapshot, preload, and TTL settings.
Partial upserts: the base partial-upsert ingestion model and built-in strategies.
JSON index: filter the merged JSON document efficiently.

Get Started

Ingestion

External Tables

Query Data

Manage Data

Observability

Visualize Data

Cluster Operations

Manage Security

Release Notes

Reference

Merge JSON fields during partial upsert

Example: update a customer profile

Configure a table

Prerequisites

Schema example

Table configuration

Configuration reference

Merge rules and invalid input

Ordering, recovery, and operations

Troubleshooting

​Example: update a customer profile

​Configure a table

​Prerequisites

​Schema example

​Table configuration

​Configuration reference

​Merge rules and invalid input

​Ordering, recovery, and operations

​Troubleshooting

​Related documentation

Example: update a customer profile

Configure a table

Prerequisites

Schema example

Table configuration

Configuration reference

Merge rules and invalid input

Ordering, recovery, and operations

Troubleshooting

Related documentation