The Ingestion Bottleneck of Wide JSON

In modern telemetry, clickstreams, and AI agent observability platforms, semi-structured JSON is the standard data exchange format. These datasets present unique challenges as they scale:

• Rapid Schema Evolution: Schema shapes change constantly. Rigidly declaring thousands of typed columns upfront and performing continuous migrations is practically impossible.
• Sparsity at Scale: Although the global union of JSON keys across the dataset can exceed $10,000+$ unique paths, individual records may only populate a small fraction (e.g., 50 to 100 keys) of those fields.
• Eager Columnar Shredding: To maintain analytical performance, traditional OLAP databases attempt to parse and materialize every distinct JSON path into its own columnar structure during the write phase.

When a database encounters wide JSON with over 10,000 unique paths, this eager shredding approach breaks down. Writing thousands of physical column fragments per batch results in massive write amplification, background merge backlogs (compaction pressure), and severe metadata bloat inside file headers. SREs and database administrators are often forced to choose between slow, row-oriented JSON parsing at query time or system-stalling compaction queues at write time.

Apache Doris 4.1: The Hybrid Paradigm

Apache Doris 4.1 introduces a unified storage and retrieval paradigm to resolve this trade-off. Rather than forcing a static architectural decision between row-oriented document storage and eager columnar serialization at ingestion, the engine defers physical organization.

Two primary core mechanisms underpin this approach:

1. DOC Mode (Deferred JSON Shredding): High-throughput ingestion lands JSON payloads on disk in a structured document format. Columnar shredding is deferred, occurring asynchronously during background compaction cycles and targeting only the most frequently queried paths.
2. Storage Format V3 (On-Demand Metadata Loading): Segment-level metadata is decoupled from the file footer and distributed into discrete columnar structures. Instead of loading the metadata for all 10,000 columns into memory when a file is opened, the engine only reads metadata on-demand for the specific columns participating in the active query.

Core Concepts and Implementation

1. Defining the Wide-JSON Schema with DOC Mode

To support wide-scale datasets, define your dynamic fields using the VARIANT column type. You can restrict known stable paths to specific types while leaving the rest of the payload dynamic. Enable DOC mode and metadata decoupling by setting the appropriate properties during table creation.

The following Data Definition Language (DDL) statement shows how to define a high-throughput, semi-structured log table optimized with Storage Format V3 and DOC mode:

-- DDL for ultra-wide JSON log indexing
CREATE TABLE log_analytics_wide (
    `ts` DATETIME NOT NULL COMMENT "Log event timestamp",
    `log_id` VARCHAR(128) NOT NULL COMMENT "Unique log tracer ID",
    `payload` VARIANT COMMENT "Semi-structured dynamic payload",
    INDEX idx_payload(payload) USING INVERTED PROPERTIES("parser" = "english")
) ENGINE=OLAP
DUPLICATE KEY(`ts`, `log_id`)
DISTRIBUTED BY HASH(`log_id`) BUCKETS 16
PROPERTIES (
    "replication_num" = "1",
    "storage_format" = "V3",
    "variant_enable_doc_mode" = "true"
);

2. Querying and Manipulating Deeply Nested Fields

Once ingested, the nested attributes of the VARIANT column can be queried directly via bracket notation. For aggregations, filtering, and ordering operations, cast the path values explicitly to concrete types to bypass on-the-fly serialization overhead.

This query demonstrates how to filter, parse, and aggregate paths inside the wide JSON without scanning irrelevant document blocks:

-- Compute error trends across dynamic payloads
SELECT 
    CAST(payload['service_name'] AS VARCHAR) AS service_name,
    COUNT(*) AS total_failures
FROM log_analytics_wide
WHERE ts >= '2026-07-01 00:00:00'
  AND CAST(payload['status_code'] AS INT) >= 500
  AND payload['environment'] = 'production'
GROUP BY service_name
ORDER BY total_failures DESC
LIMIT 10;

Technical Performance Dynamics

When comparing Apache Doris 4.1 utilizing DOC mode and Storage Format V3 to typical alternative database patterns on a 100-million-row dataset containing ~10,000 distinct JSON keys, several performance advantages materialize:

• Ingestion Throughput: Write speeds match document databases (such as PostgreSQL with JSONB) because complex column-creation tasks are skipped during initial data insertion.
• Memory Consumption: Storage Format V3 limits segment-opening memory overhead. On wide tables, metadata memory footprint is reduced by up to $60\times$ compared to legacy storage engines.
• Storage Efficiency: True columnar compression under ZSTD yields high compaction rates, making the storage footprint substantially more compact than JSON string fields.

SRE and Database Best Practices

• Expose Common Keys in Schema Templates: For fields that are consistently queried, define them explicitly inside the VARIANT template declaration to ensure they bypass the evaluation stage during load.
• Tune Subcolumn Threshold Limits: Utilize variant_max_subcolumns_count to control when less frequent paths fall back to sparse storage, preventing file descriptor exhaustion.
• Implement Tiered Storage Policies: Set up auto-partitioning alongside cloud object storage backends to migrate cold historical data away from local SSDs to save infrastructure costs.
• Enforce Low Compaction Intervals: Because DOC mode shifts shredding tasks to the background, monitor compaction queues to ensure thread limits are matched to local CPU resources.

Getting Started

To test these wide-JSON performance optimizations in your local environment, install the latest release, configure your table parameters, and load dynamic payloads:

# Step 1: Start Apache Doris container or local instance
docker run -p 9030:9030 -p 8030:8030 -d apache/doris:4.1.0-be

# Step 2: Access the MySQL protocol interface
mysql -h 127.0.0.1 -P 9030 -u root

# Step 3: Run the DDL DDL script to create the table with DOC mode
# [Paste the DDL schema code block from Section 1 here]

# Step 4: Stream load a highly sparse JSON dataset
curl --location-trusted -u root: -T wide_logs.json \
  -H "read_json_by_line:true" -H "format:json" \
  http://127.0.0.1:8030/api/test_db/log_analytics_wide/_stream_load

By leveraging this decoupled architecture, your ingestion paths remain fast and reliable, while your deep-analytical dashboards continue to benefit from columnar execution speeds at petabyte scale.